SQL - Must declare the scalar variable "@ReturnVal".

tommym121
tommym121 used Ask the Experts™
on
This is the calling code
Use Test;

DECLARE @str nvarchar(max)
Exec sp_GetColummValueByIdx 'TmpUpdateTable', 'IID', 1, @str OUTPUT
Print '@str => ' + @str

This is the stored procedure
USE [Test]
GO
/****** Object:  StoredProcedure [dbo].[sp_GetColummValueByIdx]    Script Date: 05/24/2012 16:01:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_GetColummValueByIdx]
      -- Add the parameters for the stored procedure here
      @TableName varchar(max),      
      @ColumnName varchar(max),
      @idx int,
      @ReturnVal nvarchar(max) OUTPUT
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

    -- Insert statements for procedure here
      --DECLARE @fmtString varchar(max)
      DECLARE @SQLQuery nvarchar(max)
      Set @SQLQuery =
            N'Select @ReturnVal=' +dbo.FormatStringForColumn(@TableName, @ColumnName)+ ' FROM ' + @TableName + ' WHERE idx='+CAST(@idx as varchar(max))
      
      Print '@SQLQuery -' + @SQLQuery
      EXEC (@SQLQuery)
        Print '@ReturnVal =>' + @ReturnVal
END

I got  the following result,  it complains (Must declare the scalar variable "@ReturnVal".)
Can anyone tell me why?


@SQLQuery -Select @ReturnVal= Cast(ISNULL(LTRIM(RTRIM('N'''+Replace([IID],'''','''''')+'''')),'NULL') as nvarchar(max)) FROM TmpUpdateTable WHERE idx=1
 
Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable "@ReturnVal".
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
EXEC does not support external parameters. You need to use sp_executesql instead. See below.

As the side note - I would be very careful with that pattern. It's not safe against SQL injection

ALTER PROCEDURE [dbo].[sp_GetColummValueByIdx]
      -- Add the parameters for the stored procedure here
      @TableName varchar(max),      
      @ColumnName varchar(max),
      @idx int,
      @ReturnVal nvarchar(max) OUTPUT
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

    -- Insert statements for procedure here
      --DECLARE @fmtString varchar(max)
      DECLARE @SQLQuery nvarchar(max)
      Set @SQLQuery =
            N'Select @ReturnVal=' +dbo.FormatStringForColumn(@TableName, @ColumnName)+ ' FROM ' + @TableName + ' WHERE idx=@IDX'
      
      Print '@SQLQuery -' + @SQLQuery
	exec sp_executesql 
		@SQLQuery
		,N'@ReturnVal nvarchar(max) output, @IDX int'
		,@ReturnVal = @ReturnVal output, @idx=@Idx
        Print '@ReturnVal =>' + @ReturnVal
END

Open in new window

Author

Commented:
Thanks.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial