Asked  10 Months ago    Answers:  5   Viewed   10 times

I would like to know if in SQL is it possible to return a varchar value from a stored procedure, most of the examples I have seen the return value is an int.

Example within a procedure:

declare @ErrorMessage varchar(255)

if @TestFlag = 0
   set @ErrorMessage = 'Test'

return @ErrorMessage 

 Answers

2

You can use out parameter or the resulset to return any data type.
Return values should always be integer

CREATE PROCEDURE GetImmediateManager
   @employeeID INT,
   @managerName VARCHAR OUTPUT
AS
BEGIN
   SELECT @managerName = ManagerName
   FROM HumanResources.Employee 
   WHERE EmployeeID = @employeeID
END

Taken from here

Tuesday, August 10, 2021
3

If your stored procedure does not return anything you have to use SET NOCOUNT ON;

You can read more about it here:

https://msdn.microsoft.com/en-us/library/ms189837.aspx

Saturday, May 29, 2021
 
samrap
 
2

Try adding this line to the beginning of your stored procedure:
SET FMTONLY OFF
You can remove this after you have finished importing.

It's mentioned in this article (kind of a long way down) and it worked for me, although I have ended up going back to ADO.NET for the times when I am relying heavily on Stored Procedures. Bear in mind that if your columns change depending on the dynamic SQL your entity model will break.

Thursday, August 12, 2021
 
2

That´s because email it´s allways = to Email (the column names are not case sensitive) You should change it to something like this:

-- Change Delimiter
DELIMITER //
-- Create Stored Procedure
CREATE DEFINER=`username`@`localhost` PROCEDURE GetUserByEmail( 
    IN My_email VARCHAR(255)
)
BEGIN

SELECT * FROM user WHERE email = My_email;

END//
-- Change Delimiter again
DELIMITER ;
Thursday, September 2, 2021
 
Steve
 
2

Any ideas why this would happen?

Compilation.

The master script likely is compiled batch by batch using the statistics valid at this point.

The SP will be compiled once at start, and if the statistics change during the run - as is typial for a sequence of loads - there you go. Especially if the statistical change is significant during processing. Basically the stats at teh beginning - when things are compiled - are totally off compared to the runtime stats for some tables.

There is a recompile option that you can se tin the individual statements to avoid this.

Wednesday, December 8, 2021
 
Asperi
 
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :  
Share