Stored procedure returns varchar

Question

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

Example in the procedure:

 declare @ErrorMessage varchar(255) if @TestFlag = 0 set @ErrorMessage = 'Test' return @ErrorMessage 

Answer 1

You can use the parameter or resulset to return any type of data.
Return values ​​must always be integers.

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

Taken from here

Answer 2

To do this, you need to create a saved function:

 create function dbo.GetLookupValue(@value INT) returns varchar(100) as begin declare @result varchar(100) select @result = somefield from yourtable where ID = @value; return @result end 

Then you can use this saved function as follows:

 select dbo.GetLookupValue(4) 

Mark

Answer 3

The stored procedure return code is always integer, but you can have OUTPUT parameters, which are any type you want - see http://msdn.microsoft.com/en-us/library/aa174792.aspx .