dynamic sql context ... - 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 2005 Forums

Transact-SQL (2005)

dynamic sql context ...

Author

Topic

ZenRoe
Starting Member

14 Posts

Posted - 2008-04-11 : 09:42:12

Hi,
I need to build up dynamically cursor and fetch variables in a script. I use exec(@sql) to declare the cursor and my hope was I could use the same method for doing the fetch. Unfortunately I run into a

Must declare the scalar variable "@c1".

Here's the part of the code:
... snippet

DECLARE @c1 varchar(max)
DECLARE @c2 char(3)
DECLARE @c3 char(3)
DECLARE @c4 char(3)
DECLARE @c5 char(3)
DECLARE @c6 char(3)
DECLARE @c7 char(3)
DECLARE @c8 char(3)
DECLARE @c9 char(3)
DECLARE @c10 char(3)
DECLARE @c11 char(3)
DECLARE @c12 char(3)
DECLARE @c13 char(3)
DECLARE @c14 char(3)
DECLARE @c15 char(3)
DECLARE @c16 char(3)
DECLARE @c17 char(3)
DECLARE @c18 char(3)
DECLARE @c19 char(3)
DECLARE @c20 char(3)
DECLARE @c21 char(3)
DECLARE @c22 char(3)
DECLARE @c23 char(3)
DECLARE @c24 char(3)
DECLARE @sql VARCHAR(MAX)
DECLARE @insert VARCHAR(MAX)
DECLARE @fetch varchar(max)

SET @sql ='DECLARE C1 CURSOR FOR SELECT * FROM OPENROWSET(' +
@apo+@int_provider_name+@apo+ ',' +
@apo+@int_provider_string+@apo + ',' +
@apo+@int_query_string+@apo +
') FOR READ ONLY'
PRINT @sql
EXEC(@SQL)

OPEN c1

set @fetch = 'FETCH c1 INTO '+@int_fetch
print @fetch
EXEC (@fetch)

... snippet

Output:
DECLARE C1 CURSOR FOR SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\work\2007schenker.xls','SELECT * FROM [Datei$]') FOR READ ONLY

FETCH c1 INTO @c1,@c2,@c3,@c4,@c5,@c6,@c7,@c8,@c9,@c10,@c11,@c12,@c13,@c14,@c15,@c16,@c17,@c18,@c19,@c20,@c21,@c22,@c23,@c24

Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@c1".

How can I declare the dynamically built 'fetch into' variables (@c1 etc.) so they are in the context of dynamic sql ?

Thanks: Peter

gypo
Starting Member

9 Posts

Posted - 2008-04-11 : 12:57:18

Hi

(My 1st post as well!)

I've had this before and it's because c1 only exists inside the EXEC(@SQL) instance and does not exist inside your scripts instance.

You need to make the OPEN c1 and subsequent calls inside the same EXEC statement.
eg.
SET @SQL = @SQL + 'OPEN c1....'
EXEC(@SQL)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources