troubleshooting Question

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

Avatar of tommym121
tommym121Flag for Canada asked on
Microsoft SQL ServerMicrosoft SQL Server 2005Microsoft SQL Server 2008
2 Comments1 Solution1640 ViewsLast Modified:
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".
ASKER CERTIFIED SOLUTION
dwkor

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros