SQL Server Performance Forum – Threads Archive

Two cursors in T-SQL

Hi , is it possible to make with 2 cursor . One read some records and the second make with every record..
Thanks, Lubo

You can have two cursors open at the same time, no problem at all. Can you give a little more information what you want to use the second cursor for?

Ok, I did it. It’s ok. But I found out i have problem with delete of (not)existed table. I try it with 3 ways but no one is correct….
/* 1.
If (Select object_id(‘[email protected]+’)) > 0
begin
print ‘Deleting NOVATAB’
Exec (‘Drop table ‘ [email protected])
end */ /*
2.
if exists (select * from dbo.sysobjects where id = object_id(‘[email protected]+’) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)
begin
print ‘Deleting NOVATAB’
execute (‘drop table ‘[email protected] )
end
*/
/*
3.
IF OBJECT_ID(‘[email protected]+’)IS not NULL
begin
execute (‘drop table ‘[email protected] )
end
*/

Look at the quotes:
object_id(‘[email protected]+’)
This tries to find the id of an object with the name [email protected]+ —and that is not the name you’re looking for. Just use
OBJECT_ID(@novaTab) You use the quotes only when building up a query statement – like your DROP TABLE statement, which is already correct.

Sorry, but to me it is not clear why you use a cursor here at all. What are you really after? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

Post the full code you used and tell us why you need Cursor
Madhivanan Failing to plan is Planning to fail

Thanks, my code is now rigth. But during run of this …. /*
Where is the problem ? I need dynamicly insert data to table. When i use this part of code i have an error. When @pole1 begin with some letter , i have this message Server: Msg 128, Level 15, State 1, Line 1
The name ‘a’ is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.
*/
Declare @Pole1 varchar, @Pole2 varchar(255), @sqlcommand varchar(255), @tmp_table varchar(255)
Set @tmp_table=’tempor’ IF OBJECT_ID(@tmp_table)IS not NULL
Set @sqlcommand=’Drop table ‘ + @tmp_table
Execute (@sqlcommand) Set @sqlcommand =’create table ‘ + @tmp_table+’ ( Col1 varchar(255),Col2 varchar(255) )’
Execute (@sqlcommand)
Set @Pole1=’abc123456′
Set @sqlCommand =’INSERT Into ‘ [email protected]_table+ ‘(COl1 ) Values( ‘+ @pole1+’)’
Execute (@sqlCommand)

You code won’t work. Look at your definition of @pole1.
DECLARE @pole1 VARCHAR is equivalent to
DECLARE @pole1 VARCHAR(1) Now, when you try to assign
SET @Pole1=’abc123456′ it will be truncated after the first character. That’s why you see the message "The name ‘a’ is not permitted in this context" Next, since this is a VARCHAR you need to escape it properly.
Set @sqlCommand =’INSERT Into ‘ [email protected]_table+ ‘(COl1 ) Values( ”’+ @pole1+”’)’ should work. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

]]>