CURSOR With Dynamic SQL

Question

CURSOR With Dynamic SQL

scking

SSCrazy

Points: 2598
More actions
October 29, 2003 at 10:59 am

#82526

All I'm trying to do is fill in a cursor with dynamic SQL because of different tablenames for data to be imported. Although the following seems like it should logically work I get and error 'Incorrect syntax near SQL' in the line where I declare the cursor. I've verified the SQL properly creates the SQL. This is already functioning on a procedure where I must edit for each tablename. Any help would be appreciated.
CREATE PROCEDURE dbo.sp_Pn3
(
@TableName nvarchar(32)
)
AS
BEGIN
DECLARE @PN nvarchar(32), @Cage nvarchar(7), @Table nvarchar(255)
DECLARE @sql nvarchar(4000)
SET @sql = 'SELECT PN, Cage FROM [dbo].[' + @TableName + ']'
DECLARE crs_pn CURSOR FOR @sql
OPEN crs_pn CURSOR
Steve

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

gljjr SSChampion Points: 10080 More actions · Answer 1

This is not allowed in SQL Server. I would use your dynamic sql to fill a temp table and then run the cursor on the temp table. Better yet is there a way to do without the cursor all together?

One way I have gotten rid of cursors is to create a temp table with an identity column on it. Then use that column in a where clause in a while loop. Same functionality but no cursor overhead. On small datasets it doesn't make a whole lot of difference performance wise but with larger datasets it seems to help.

Gary Johnson

Microsoft Natural Language Group

DBA, Sr. DB Engineer

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

scking SSCrazy Points: 2598 More actions · Answer 2

Since this is importing data from an external source I need to perform multiple checks to determine whether the data should be merged into the tables. How would one go about filling a temporary table with the table data using the dynamic sql. An alternative would be to edit the sp 50 times. Maybe a pain but the data needs to be loaded and I have to deal with another orgs data tables, naming, and structures.

I used visual basic read though every tablename in the Access database, creating dynamic sql insert statements, and append all data to a single table in about 10 minutes. My impression was that there wasn't much I couldn't do with T-SQL that I did with VB but, at least in this case, maybe I was wrong.

Steve

AndyLoughman SSC-Addicted Points: 422 More actions · Answer 3

If you must use a cursor then

exec 'DECLARE crs_pn CURSOR FOR ' + @sql

should work.

CURSOR With Dynamic SQL

Cookies on SQLServerCentral