using a parameter of table type in dynamic sql

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 2008 Forums

Transact-SQL (2008)

using a parameter of table type in dynamic sql

Author

Topic

sarahmfr
Posting Yak Master

214 Posts

Posted - 2011-08-06 : 10:24:33

I am having a problem to use a declared table in a dynamic sql statement
please see my code I am getting an error saying must declare @id1

declare @id1 table (id int, sp2sectionid int,sp2lessonid int, learningobjectcontent nvarchar(20))

DECLARE @columnname nvarchar(50)
declare @mysql nvarchar(1000)

--declare the cursor
DECLARE @LearningObject CURSOR
SET @LearningObject =cursor
FOR

SELECT column_name from INFORMATION_SCHEMA.COLUMNS
where TABLE_CATALOG=’xxxxx’
and TABLE_NAME =’xxxxxxccccc’
and substring(column_name,1,7)='BitRef_'

OPEN @Learningobject
Fetch next
from @LearningObject Into @ColumnName

WHILE @@Fetch_Status = 0

BEGIN

set @mysql=N'
insert into @id1
(

SP2LessonID,
SP2SectionID,
LearningObjectContent)
(
select '
+''''+substring(@ColumnName,8,30)+''' ,'+'ID ,SectionID ,'+
@columnname+' from ffffffff where id='+cast(@SP2LessonID as nvarchar(10))+'
)
'

exec (@mysql)

fetch next
from @learningObject into @columnname

END

CLOSE @learningobject

DEALLOCATE @learningobject
Thanks

sarah

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-08-06 : 10:34:22

Change table variable @id1 to a temporary table instead ( #id1 ).
Table variables doesn't reach through different scopes. Temporary tables do.

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources