October 29, 2003 at 10:59 am
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
October 29, 2003 at 12:40 pm
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.
October 29, 2003 at 3:18 pm
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
Steve
October 30, 2003 at 5:33 am
If you must use a cursor then
exec 'DECLARE crs_pn CURSOR FOR ' + @SQL
should work.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply