Unable to get output parameter to work in SQL Server

I'm trying to have  the results of a stored procedure populate a variable.  I've eventually stripped my query down to the bare-bones query you see below:

DECLARE @SQLString NVARCHAR(500);
DECLARE @ParmDefinition NVARCHAR(500);
DECLARE @Lastlname varchar(30);
SET @SQLString = N'SELECT ''Smith'' as TheLastName';
SET @ParmDefinition = N'@LastlnameOUT varchar(30) OUTPUT';

exec sp_executesql
	@SQLString,
	@ParmDefinition,
	@LastlnameOUT=@Lastlname OUTPUT;

SELECT @Lastlname;

Open in new window


Even this does not work as I intended instead giving the following output:
Results
canuckconsultingAsked:
Who is Participating?
 
HuaMin ChenSystem AnalystCommented:
Try
DECLARE @SQLString NVARCHAR(500);
DECLARE @ParmDefinition NVARCHAR(500);
DECLARE @Lastlname varchar(30);
SET @SQLString = N'SELECT @Lastlname=''Smith''';
SET @ParmDefinition = N'@Lastlname varchar(30) OUTPUT';

exec sp_executesql
	@SQLString,
	@ParmDefinition,
	@Lastlname=@Lastlname OUTPUT;

SELECT @Lastlname;

Open in new window

0
 
HuaMin ChenSystem AnalystCommented:
Try
DECLARE @SQLString NVARCHAR(500);
DECLARE @ParmDefinition NVARCHAR(500);
DECLARE @Lastlname varchar(30);
SET @SQLString = N'SELECT @Lastlnameout=''Smith''';
SET @ParmDefinition = N'@Lastlnameout varchar(30) OUTPUT';

exec sp_executesql
	@SQLString,
	@ParmDefinition,
	@Lastlname=@LastlnameOUT OUTPUT;

SELECT @Lastlname;

Open in new window

0
 
canuckconsultingAuthor Commented:
No luck....I get the message:
Msg 137, Level 15, State 2, Line 10
Must declare the scalar variable "@LastlnameOUT".
0
 
canuckconsultingAuthor Commented:
Thank youi!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.