Declaring a cursor with table name as parameter
-
Hi SQL Gurus
When you execute the following code it works perfectly:
DECLARE @CursorVar CURSOR
SET @CursorVar = CURSOR SCROLL DYNAMIC
FOR
SELECT *
FROM Northwind.dbo.Employees
--WHERE LastName like 'B%'
OPEN @CursorVar
FETCH NEXT FROM @CursorVar
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM @CursorVar
END
CLOSE @CursorVar
DEALLOCATE @CursorVar
BUT when I try to open a cursor by supplying table name as a parameter, which I need to do, it fails:
declare @sql varchar(200)
declare @tablename varchar(200)
set @tablename = 'sysobjects'
SET @sql = 'declare @CursorVar cursor ' --OR declare @CursorVar cursor --without using SET @sql
SET @sql = @sql + 'SET @CursorVar = CURSOR FOR SELECT * from ' + @tablename
PRINT @sql
exec (@SQL)
OPEN @CursorVar
FETCH NEXT FROM @CursorVar
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM @CursorVar
END
CLOSE @CursorVar
DEALLOCATE @CursorVar
Is there a way to achieve that - open a cursor with table name as a parameter?
Thanks
Gary
-
Can you post what are you trying to achieve?
What is the purpose of this ?
* Noel -
(1) Do not use a Cursor Variable
(2) Declare the cursor Global
This allows what you need to do. So, this code should do it:
declare @sql varchar(200)
declare @tablename varchar(200)
set @tablename = 'sysobjects'
SET @sql = 'Declare c_CursorVar CURSOR GLOBAL FOR SELECT * from ' + @tablename
PRINT @sql
exec (@SQL)
OPEN c_CursorVar
FETCH NEXT FROM c_CursorVar
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM c_CursorVar
END
CLOSE c_CursorVar
DEALLOCATE c_CursorVar
-
3) Listen to Noeld and try to find a set based approach for this.
What is the goal of that stored proc?
-
Time for me to agree with ALL your points
* Noel -
Agreed... but that could cause an infinite loop so I might retract that statement
. -
Fantastic - thanks a lot Brendthess. I really appreciate it
-
Looks like my font was wrong :
3) Listen to Noeld and try to find a set based approach for this.
What is the goal of that stored proc?
-
Hi Remi
I am writing a stored procedure that opens a cursor with all the records (select * from @table) of the tablename supplied as parameter by the user. It formates each row according to the need and populates a temporary table for further business logic implementation and processing.
Is the global cursor approach not recommended?
Thanks
Gary
-
I've been working for 18 months with sql server. I never found a situation where a cursor is the only solution and where a set based approach doesn't exists. I've seen a single situation where the cursor was faster than the set-based approach (out of 5k-8K queries). I've also seen a report with an approach like this go down from 24 hours to 1 minute. Now I don't know how complexe the problem is but I suggest you at least consider a set based approach, maybe even DTS if you have a lot of steps needed to do this report.
Can you explain in more details what you need to do. Ideally with some sample data, table definition and the expected results?
-
OK
I am actually using the identity col approach at the moment. But there will be situations when I cannot use Identity col all the time. I have different tables that get popluated based on criteria. And then INSERT stmts are generated calling a different stored procedure.
HOW it works:
Table A has some rows that I want.
Exec Stored Proc 1 and create Table B from Table A with required rows and introduce an Identity column in Table B.
Exec Stored Proc 2 @tablename = Table B
I scroll via a while loop through the records in Table B based on Identity Col criteria (where Ident_col = current counter value) - I want to eliminate this dependency of identity column.
Then I open another While loop for each column in the row. Check if the column is Primary Key, get the value into a #tempTable, if yes:
PRINT 'IF NOT EXISTS(SELECT * FROM @TABLENAME WHERE PK = value)'
I put the value into the temporary table becuase I was getting same type of problems becuase of the dynamic SQL (@tablename), I could not put the value in a variable. Want to reolve this too.
Then add the column to the columnlist and value to the valuelist.
Once the inner while loop finishes:
PRINT 'INSERT INTO @TABLE (COLLIST) VALUES (VALUELIST)
In this process values for Table_id column(Identity Col) are also generated, so I have to check if the column is Table_Id, do not add to columnlist or valuelist) - a drawback of the current situation.
Here is the code:
ALTER PROCEDURE up_generate_inserts
(@tablename varchar(255),
@target varchar(255))
AS
BEGIN
set nocount on
declare @cnum int
declare @rnum int
declare @rcount int
declare @ccount int
declare @onedone int
--declare @tablename varchar(255)
--declare @target varchar(255)
declare @columnname varchar(255)
declare @data_type varchar(255)
declare @sql nvarchar(4000)
declare @if_stmt varchar(4000)
declare @columnvalue varchar(8000)
declare @INS_Stmt varchar(8000)
declare @columnlist varchar(8000)
declare @valuelist varchar(8000)
--set @tablename = 'CNH_Format_Cat2'
--set @target = 'Format'
If exists(select [Name] from tempdb.dbo.sysobjects where [name] = '##TEMPCOUNT')
DROP TABLE ##TEMPCOUNT
set @sql = 'Select COUNT(*) as COUNT into ##TEMPCOUNT from ' + @tablename
set @columnlist = ''
set @valuelist = ''
EXECUTE sp_executesql @sql
SELECT @rcount = [COUNT] FROM ##TEMPCOUNT
set @rnum = 1
--ROW LOOP
while (@rnum < = @rcount)
begin
SELECT @ccount = max(ordinal_position) from information_schema.columns where table_name = @tablename
set @cnum = 1
set @onedone = 0
SET @IF_stmt = 'IF NOT EXISTS ( SELECT * FROM ' + @target + ' WHERE '
SET @INS_stmt = 'INSERT INTO ' + @target
set @columnlist = ''
set @valuelist = ''
--COLUMN LOOP
While (@cnum < = @ccount)
Begin
select @columnname = column_name, @data_type = data_type from information_schema.columns where table_name = @tablename and ordinal_position = @cnum
IF @Columnname in (select Column_Name from information_schema.Key_Column_Usage kcu
inner join information_schema.Table_Constraints tc on kcu.constraint_name = tc.constraint_name
where tc.Constraint_Type = 'PRIMARY KEY' AND KCU.TABLE_NAME = @tablename)
Begin
IF @onedone = 0
Begin
set @if_stmt = @if_stmt + @columnname + ' = '
set @sql = 'SELECT ' + CAST (@columnname as Varchar(1024)) + ' as PKCOL INTO ##TMPCOL from ' + @tablename + ' where tabID = ' + CAST(@rnum as Varchar(5))
If exists(select [Name] from tempdb.dbo.sysobjects where [name] = '##TMPCOL')
DROP TABLE ##TMPCOL
EXECUTE sp_executesql @sql
select @columnvalue = ISNULL(PKCOL, 'NOTHING') FROM ##TMPCOL
set @if_stmt = @if_stmt + @columnvalue
set @onedone = 1
end
else
Begin
set @if_stmt = @if_stmt + ' AND ' + @columnname + ' = '
If exists(select [Name] from tempdb.dbo.sysobjects where [name] = '##TMPCOL')
DROP TABLE ##TMPCOL
set @sql = 'SELECT ' + CAST (@columnname as Varchar(1024)) + ' as PKCOL INTO ##TMPCOL from ' + @tablename + ' where tabID = ' + CAST(@rnum as Varchar(5))
set @onedone = 1
EXECUTE sp_executesql @sql
select @columnvalue = ISNULL(PKCOL, 'NULL') FROM ##TMPCOL
set @if_stmt = @if_stmt + @columnvalue
end
end
If exists(select [Name] from tempdb.dbo.sysobjects where [name] = '##TEMPCOL')
DROP TABLE ##TEMPCOL
set @sql = 'SELECT ' + CAST (@columnname as Varchar(1024)) + ' as CCOL INTO ##TEMPCOL from ' + @tablename + ' where tabID = ' + CAST(@rnum as Varchar(5))
EXECUTE sp_executesql @sql
if (SELECT CCOL FROM ##TEMPCOL) IS NOT NULL
select @columnvalue = CCOL FROM ##TEMPCOL
ELSE
SET @COLUMNVALUE = 'NULL'
IF @Data_Type IN ('char','varchar','nchar','nvarchar') AND @columnvalue <> 'NULL'
Set @columnvalue = '''' + cast(@ColumnValue as varchar(4000)) + ''''
IF @Data_Type IN ('datetime','smalldatetime') AND @columnvalue <> 'NULL'
Set @columnvalue = convert(varchar(100),convert (datetime, @columnvalue), 101)
-- Set @columnvalue = convert (varchar(100), @columnvalue, 101) --Does not work
-- DROP TABLE ##TEMPCOL
If UPPER(@columnname) = 'TABID'
set @columnvalue = ''
If UPPER(@columnname) = 'TABID'
set @columnname = ''
SET @columnlist = @columnlist + ' ' + @columnname + ','
set @valuelist = @valuelist + ' ' + @columnvalue + ','
set @cnum = @cnum + 1
end
SET @IF_STMT = @IF_STMT + ' )'
PRINT @IF_STMT
SET @columnlist = LEFT(@columnlist, LEN(@columnlist)-1)
SET @columnlist = RIGHT(@columnlist, LEN(@columnlist)-2)
SET @valuelist = LEFT(@valuelist, LEN(@valuelist)-1)
SET @valuelist = RIGHT (LTRIM(@valuelist), LEN(@valuelist)-2)
Set @INS_Stmt = @Ins_Stmt + '(' + @columnlist + ' ) Values (' + @valuelist + ' )'
print @INS_Stmt
print ''
set @rnum = @rnum + 1
end
End
-----------------------
I know people have written generate insert stored procs, already, but they don't solve my purpose, as I need the IF NOT EXISTS stmt as well and avoid primary key errors - thats how our business works. We generate stuff over and over again and need to first check if the primary key will be violated.
Therefore, the goals are:
1. Eliminate Identity Col dependency
2. Eliminate the need to check whether column is TabID (extra Identity col added to @tablename)
-
Sorry to hear that. Looks like you're stuck with this situation. Good luck with that.
-
I'd replace the section of code:
If exists(select [Name] from tempdb.dbo.sysobjects where [name] = '##TEMPCOUNT')
DROP TABLE ##TEMPCOUNT
set @sql = 'Select COUNT(*) as COUNT into ##TEMPCOUNT from ' + @tablename
set @columnlist = ''
set @valuelist = ''
EXECUTE sp_executesql @sql
SELECT @rcount = [COUNT] FROM ##TEMPCOUNT
to:
SET @columnlist = ''
SET @valuelist = ''
CREATE TABLE #TempCount ([COUNT] INT)
SET @sql = 'INSERT INTO #TempCount SELECT COUNT(*) FROM ' + @tablename
EXECUTE sp_executesql @sql
SELECT @rcount = [COUNT] FROM #TempCount
DROP TABLE #TempCount
1. A single # makes the table local to this thread, there's no chance this proc could be run on separate threads and one killing the table while the other is trying to read it.
2. I reordered the steps because there are basically two processes going on. Initializing variables and getting a count variable. The "SET @sql" is not initializing data, it is setting it for a specific purpose. I put it after "CREATE TABLE" simply because it reads better for a human. I included the "DROP TABLE" for the same reason.
3. It's good practice to make SQL key words stand out in your code. The best way to do that is to capitalize the key words. SQL is not case sensitive, humans are somewhat case sensitive.
Later on you have "set @sql = 'SELECT ' + CAST (@columnname as Varchar(1024)) + ..." This is rather silly. @columnname is defined as VARCHAR(255), it isn't going to suddenly increase in size just because you cast it bigger. (OK, you can increase the size, but you would have to cast to CHAR(1024) and then you are just taking up unneeded space.) The value of this variable comes from a "sysname" type of variable, which is equivalent to NVARCHAR(128), (256 bytes - 128 characters.) that when cast as VARCHAR will never be longer than 128 characters. (Now maximum of 128 bytes. It might lose UNICODE characters, but since you probably aren't shipping this code to Europe/Asia, you are probably safe doing this cast.) Same complaint about the ## table here.
You've repeated "If UPPER(@columnname) = 'TABID'" in the last part of your proc. You might consider setting that up as a BEGIN/END segment.
First thing I did was copy your proc into a Microsoft Development Environment TextFile, so I could easily indent the code. After that I could easily see the scope of BEGIN/END blocks. This makes it more readable. (If you removed them before posting, sorry about this comment.) In the same vein, if a command spans lines, indenting the following line(s) makes it easier to see the lines are related.
Suggest you rewrite:
If UPPER(@columnname) = 'TABID'
set @columnvalue = ''
If UPPER(@columnname) = 'TABID'
set @columnname = ''
SET @columnlist = @columnlist + ' ' + @columnname + ','
set @valuelist = @valuelist + ' ' + @columnvalue + ','
as:
If UPPER(@columnname) <> 'TABID'
BEGIN
SET @columnlist = @columnlist + ' ' + @columnname + ','
set @valuelist = @valuelist + ' ' + @columnvalue + ','
END
Of course this whole logic emulates a cursor. You'd be better off to re-write it so that the command reads:
INSERT INTO TargetTable (ColumnList)
SELECT ColumnList FROM SourceTable s
LEFT JOIN TargetTable t ON s.PrimaryKey=t.PrimaryKey
WHERE t.PrimaryKey IS NULL
This proc would be a lot faster to execute (It doesn't read SourceTable data, just the schema.), produce a lot less output, and executing the output would be faster. The variables in the output would be TargetTable, ColumnList, SourceTable, s.PrimaryKey=t.PrimaryKey, and t.PrimaryKey. (t.PrimaryKey would only be the first field, s.PrimaryKey=t.PrimaryKey would be the combination of all fields that make up the primary key.)
What's going on: The LEFT JOIN combines the two tables, retaining all the SourceTable rows and only the matching TargetTable rows. Since you don't want to add rows that already exist in the target, the WHERE clause removes them. (You could replace the WHERE with AND, but I think this is more readable.) Since all fields in a primary key must not be null, it doesn't matter if the first field in the target is nullable because null values in the target would never match. The only way the first field would have a value in the target is if all primary fields matched.
Oops, one more variable. Since both tables will have the same ColumnList, all fields in the SELECT ColumnList should be prefixed with "s." and you should not have the prefix in the (ColumnList) fields.
-
Thanks Kenneth - it was informative. I will make the corrections.
I could not understand this part:
INSERT INTO TargetTable (ColumnList)
SELECT ColumnList FROM SourceTable s
LEFT JOIN TargetTable t ON s.PrimaryKey=t.PrimaryKey
WHERE t.PrimaryKey IS NULL
Does the above produce same output? The goal is to first run the generated script in Beta, QA, Staging for testing and then to Production environments.
Do u have the re-written code that you can send me?
Also is it possible to eliminate this:
set @sql = 'SELECT ' + CAST (@columnname as Varchar(1024)) + ' as CCOL INTO ##TEMPCOL from ' + @tablename + ' where tabID = ' + CAST(@rnum as Varchar(5))
EXECUTE sp_executesql @sql
if (SELECT CCOL FROM ##TEMPCOL) IS NOT NULL
select @columnvalue = CCOL FROM ##TEMPCOL
WITH:
set @sql = 'SELECT @columnvalue =' + CAST (@columnname as Varchar(1024)) + ' from ' + @tablename + ' where tabID = ' + CAST(@rnum as Varchar(5))
EXECUTE sp_executesql @sql
- The above fails.
MOST important: is how to write a cursor and eliminate dependency on TABID - which I am still unable to do even after declaring it as Global.
Thanks
Gary
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply