stored procedure returns varchar

Question

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 proc

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

Answer 1

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

Answer 2

A stored procedure's return code is always integer, but you can have OUTPUT parameters that are any desired type -- see http://msdn.microsoft.com/en-us/library/aa174792.aspx .

Answer 3

You will need to create a stored function for that:

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

You can then use this stored function like this:

select dbo.GetLookupValue(4)

Marc