Stored Procedure returning a string? [duplicate]

Question

Possible Duplicate:
Conversion failed when converting the nvarchar value ‘Internet Explorer 3 original’ to data type int

So I created this following simple stored procedure:

CREATE PROCEDURE test
AS
BEGIN
    RETURN 'works!'
END
GO

I then wanted to execute it by firing the following statement:

EXEC test

Why am I getting the following error all the time?

Conversion failed when converting the varchar value 'works!' to data type int.

Oh and btw, (when) is it necessary to have the GO-statement in the end of a stored procedure? I guess it takes no effect here since it will jump out of the procedure when I am returing my @out String.

Answer 1

Using RETURN only allows you to return an INTEGER code

You either need to SELECT the value

   CREATE PROCEDURE test
   AS
   BEGIN
       SELECT 'works!'
   END

or if you want to assign into a variable in the caller, use an OUTPUT parameter

CREATE PROCEDURE test
    @outputVal VARCHAR(20) OUTPUT
AS
BEGIN
    SET @outputVal = 'works!'
END

DECLARE @val VARCHAR(20)
EXECUTE test @val OUTPUT