How to declare cursor in dinamic sql

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)

How to declare cursor in dinamic sql

Author

Topic

juicyapple
Posting Yak Master

176 Posts

Posted - 2008-02-12 : 08:28:43

Hi, I want to declare a cursor in dinamic sql, but I do not know how to execute it, this is the code,

SET @nsql = 'DECLARE @TableCursor CURSOR FOR SELECT ObjOwner, TableName FROM ' + quotename(@SysSvr) + '.' + quotename(@SysDBName) + '[dbo].[tbl_Table_Setting] WHERE Id = ' + convert(NVARCHAR,@Id) + ''
				
EXEC sp_executesql @nsql, N'@TableCursor CURSOR OUTPUT',@TableCursor = @TableCursor OUTPUT;

Any idea? Thanks.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-12 : 08:38:55

There is no need to create output parameter.

SET @nsql = 'DECLARE TableCursor CURSOR FOR SELECT ObjOwner, TableName FROM ' + quotename(@SysSvr) + '.' + quotename(@SysDBName) + '[dbo].[tbl_Table_Setting] WHERE Id = ' + convert(NVARCHAR,@Id) + ''
				
EXEC sp_executesql @nsql

Open TableCursor 

-- do cursor operations

Close TableCursor 
Deallocate TableCursor

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-12 : 08:42:48

Both CURSOR and dynamic SQL?
I think you need to redesign your system if possible.

E 12°55'05.25"
N 56°04'39.16"

juicyapple
Posting Yak Master

176 Posts

Posted - 2008-02-12 : 19:31:25

Both CURSOR and dynamic SQL?
I think you need to redesign your system if possible.

Yes. Because of security issue?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-12 : 19:48:31

Security issues.
Performance issues.
Maintenance issues.

E 12°55'05.25"
N 56°04'39.16"

juicyapple
Posting Yak Master

176 Posts

Posted - 2008-02-12 : 19:57:40

Because the server, database name and table name is dinamic, so I have to pass in these parameters. I can't think other way other than using dinamic sql.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-13 : 04:42:19

www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Qualis
Posting Yak Master

145 Posts

Posted - 2008-02-13 : 09:22:29

I agree with Peso. What are you doing with the cursor results? Can you show us some example code as to what you are doing? If you could transform what you are doing to do some set based operation, that would be better. You might also consider filling a # table with the results of the query and then using your cursor if you absolutely must use the cursor. That way the data in local and not pulling from the remote server.

juicyapple
Posting Yak Master

176 Posts

Posted - 2008-02-13 : 19:48:34

I am doing a scheduled maintenance which will insert first part of data in a table(s)(db 1) to archive table(s)(db 2) and second part of data to temporary table(s)(db1). Then, the original table(s) will be replaced by the temporary table(s).

1. Server 1 keeps the tables setting like server name, table name...The setting can be changed when user adds table name.
2. Run query in server 2 to retrieve the setting from Server 1.
3. Run query in server 2 to copy the data from database 1 to database 2.
4. Run query in server 2 to copy the data to temporary table(s) in database 1 and replace original table(s) with the temporary table(s).

The cursor is used to loop the insert transaction by table name defined in tables setting.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources