exec sp with output parameter

I have the following sp:
ALTER PROCEDURE myspPrepareTextForHTML
@.MessageContent nvarchar(1400),
@.returnval nvarchar(1400) output
AS
BEGIN
SET NOCOUNT ON;
SET @.returnval='something'
RETURN @.returnval
END

When I try this:

EXEC myspPrepareTextForHTML @.MessageContent='dfgsdfgdf', @.returnvalOUTPUT

print @.returnval

I get the error:
Must declare the scalar variable "@.returnval".

How can I get this to work?

You need to set the value of an output parameter. In this case, you jus want to pass in your current variable.

Just like other parameters, the left side of = means the name of the parameter, right side means the value, or return value (return variable).

EXEC myspPrepareTextForHTML @.MessageContent='dfgsdfgdf', @.returnval = @.returnvalOUTPUT

|||now I get the error:

Msg 245, Level 16, State 1, Procedure myspPrepareTextForHTML, Line 17

Conversion failed when converting the nvarchar value 'something' to data type int.

|||

Peter Smith:

now I get the error:

Msg 245, Level 16, State 1, Procedure myspPrepareTextForHTML, Line 17

Conversion failed when converting the nvarchar value 'something' to data type int.

Most likely, the calling proc or SQL statement declaredits@.returnval as an int, rather than an nvarchar of the same size as declared in the myspPrepareTextForHTML proc.

|||This is everything I have so far...I dont see why I still get that int error:

ALTER

PROCEDURE [dbo].[myspPrepareTextForHTML]

@.MessageContent

nvarchar(1400),

@.returnval

nvarchar(1400)output

AS

BEGIN

SETNOCOUNTON;SET @.returnval='something'RETURN @.returnval

END

declare

@.returnvalnvarchar(1400)

EXEC

myspPrepareTextForHTML @.MessageContent='dfgsdfgdf', @.returnval= @.returnvalOUTPUT

print

@.returnval|||Because anything you return with RETURN can only be of INT type. So SQL Server is trying to convert the 'something' to int. If you are returning the value through the OUTPUT parameter you dont need to use the RETURN statement. So remove theRETURN @.returnval statement in your proc and you should be fine.|||

Sorry, I missed the fact that you are trying to return an output value. Any value returned with the RETURN statement must be an int. This is why there are OUTPUT variables, which allow us to essentially 'return' values of any type (like using reference/ByRef variables in .NET).

Change your code to the following, and save the RETURN statement to return something similar to an error code:

ALTER PROCEDURE [dbo].[myspPrepareTextForHTML] @.MessageContentnvarchar(1400), @.returnvalnvarchar(1400)output ASBEGIN SET NOCOUNT ON;SET @.returnval='something'RETURN 0-- no errorEND

|||like a charm!