Problems with Dynamic SQL and Output Variables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Problems with Dynamic SQL and Output Variables

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-11-20 : 09:15:29

Anil writes "I am trying to use Dynamic SQL to provide an output value to the calling procedure. This SELECT statement works as static when created. This block of code is my problem...


CREATE PROCEDURE sp_NextSequence
@strTableName varchar(20),
@strUserName varchar(45),
@intMaxCount int OUTPUT
AS	
	DECLARE @SQL nVarChar(1000)
	SELECT @SQL = 'SELECT @intMaxCount = max(intSeqNum) +1
                      FROM ' 
	SELECT @SQL = @SQL + @strTableName + ' WHERE strUserName
                      = '
	SELECT @SQL = @SQL + CHAR(39)+@strUserName+CHAR(39)
	EXEC(@SQL)
GO

When I execute this procedure, I get a "Server: Msg 137, Level 15, State 1, Line 1
Must declare the variable '@intMaxCount'."

@intMaxCount is defined as an output variable already. Why am I being asked to declare again? Any ideas.

Thanks"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources