dynamic sql - SQL Server Forums

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

Transact-SQL (2000)

dynamic sql

Author

Topic

gotafly
Yak Posting Veteran

54 Posts

Posted - 2005-06-14 : 11:39:09

Not sure what I am doing wrong?? I need to pass the field name to the SP "@progReplaceVal" Then I want to put that field data into the @progReplaceValTemp var.

I get the following error...

Must declare the variable '@progReplaceValTemp'. SELECT @progReplaceValTemp=programAppropriateness FROM tblACRDIMProgramPending WHERE (ProgramPendingID = 30)

CREATE PROCEDURE sp_getprogCopy
(
@ProgramPendingID int,
@progReplaceVal nvarchar(50),
@progReplaceValTemp nvarchar(4000) OUTPUT
)
AS

DECLARE @SQLString varchar(200)
SET @SQLString =
'SELECT @progReplaceValTemp=' + @progReplaceVal + '
FROM
tblACRDIMProgramPending
WHERE
(ProgramPendingID = ' + Cast(@ProgramPendingID as VarChar(10)) +')'

print @SQLString
EXEC (@SQLString)

GO

If I hard cord the field in like so, it works:

CREATE PROCEDURE sp_getprogCopy
(
@ProgramPendingID int,
@progReplaceVal nvarchar(50),
@progReplaceValTemp nvarchar(4000) OUTPUT
)
AS
SELECT @progReplaceValTemp = programAppropriateness
FROM
tblACRDIMProgramPending
WHERE
(ProgramPendingID = @ProgramPendingID)
GO

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-06-14 : 11:55:34

To do this the way you want it, it looks to me like you will have to use sp_executesql, something like this (note that I changed the datatype of @SQLString to nvarchar):

DECLARE @SQLString nvarchar(200)
SET @SQLString =
'SELECT @progReplaceValTemp=' + @progReplaceVal + '
FROM
tblACRDIMProgramPending
WHERE
(ProgramPendingID = ' + Cast(@ProgramPendingID as VarChar(10)) +')'

EXECUTE sp_executesql @SQLString, N'@progReplaceValTemp nvarchar(4000) OUTPUT', @progReplaceValTemp OUTPUT

gotafly
Yak Posting Veteran

54 Posts

Posted - 2005-06-14 : 12:34:25

Works great
Looks ugly
Works great
Looks ugly

LOL Thanks

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-06-14 : 20:54:22

You could be describing either my code or myself

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources