dynamic sql help - 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 help

Author

Topic

dasu
Posting Yak Master

104 Posts

Posted - 2004-09-08 : 09:32:43

i want some help for dynamic sql

this is my stored procedure

alter proc del @jid int
as
declare @t varchar(30),
@a varchar(1000)
set @a='select @t = feed_cd from t_cbs_job_ctrl where job_id='+cast (@jid as varchar(30))
print(@a)
execute(@a)
print (@t)

iam executing like this

exec del 5

iam getting following errors

select @t = feed_cd from t_cbs_job_ctrl where job_id=5
Server: Msg 137, Level 15, State 1, Line 1

please suggest me the solution

regards
dasu.g

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-09-08 : 09:42:31

You don't need to use dynamic sql for this.

Scott
Posting Yak Master

145 Posts

Posted - 2004-09-08 : 09:51:37

Change it to the following:


alter proc del 
   @jid int,
   @t varchar(30) OUTPUT
as
select @t = feed_cd 
from t_cbs_job_ctrl 
where job_id = cast(@jid as varchar(30))

You only need to use dynamic SQL when you need to dynamicly set the table name or field name.

Scott

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources