sql server programming: sp_executesql problem -- sql server programming

Re: sp_executesql problem

Adam Machanic
6/22/2007 1:17:06 PM

sql server programming:

Take the INSERT out of the dynamic SQL, and then do:

INSERT @Callout
EXEC sp_executesql @sql

--

Adam Machanic
SQL Server MVP

Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220

[quoted text, click to view]

sp_executesql problem

mike
6/22/2007 4:46:48 PM

I'm getting an error that @Callout must be delcared, but as you see it
is. What might I be doing wrong?

begin
declare @Sql nvarchar(2000)
declare @LinkedServerName nvarchar(35)
declare @DatabaseName nvarchar(35)

set @LinkedServerName='camdev0'
set @DatabaseName='ttrax'

declare @Callout table(CalloutID int identity(1,1)
,SchedDetailSerialNum int
,SchedStartDt datetime
,ForeignSysKey varchar(30)
)
set @Sql=N'insert into ' + @Callout +
'(SchedDetailSerialNum,SchedStartDt,ForeignSysKey)
SELECT sched_detail_serial_num
,sched_start_dt
,ud.foreign_sys_key
from ' + @LinkedServerName +'.' + @DatabaseName +
'.dbo.callout_sched_detail csd
join ' + @LinkedServerName +'.' + @DatabaseName + '.dbo.[user_desc]
ud
on csd.driver_user_id=ud.user_id
where (copied_to_payroll is null or copied_to_payroll=0)
and (isnumeric(foreign_sys_key)=1)
and csd.sched_start_dt is not null'

exec sp_executesql @Sql
end

I have also tried but I still get the same error message.

set @Sql=N'insert into @Callout
(SchedDetailSerialNum,SchedStartDt,ForeignSysKey)
SELECT sched_detail_serial_num
,sched_start_dt
,ud.foreign_sys_key
from ' + @LinkedServerName +'.' + @DatabaseName +
'.dbo.callout_sched_detail csd
join ' + @LinkedServerName +'.' + @DatabaseName + '.dbo.[user_desc]
ud
on csd.driver_user_id=ud.user_id
where (copied_to_payroll is null or copied_to_payroll=0)
and (isnumeric(foreign_sys_key)=1)
and csd.sched_start_dt is not null'

Re: sp_executesql problem

SQL Menace
6/22/2007 5:14:21 PM

[quoted text, click to view]

@Callout is a table variable which will be out of scope
use a temp table instead

create table #Callout (CalloutID int
identity(1,1)
,SchedDetailSerialNum int
,SchedStartDt datetime
,ForeignSysKey varchar(30)
)
set @Sql=N'insert into ' + #Callout + ......

you also might want to take a look at http://www.sommarskog.se/dynamic_sql.html#sp_executesql

Denis The SQL Menace
http://sqlservercode.blogspot.com
http://sqlblog.com/blogs/denis_gobo/default.aspx

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.