Declare a dynamic cursor - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Declare a dynamic cursor

Author

Topic

GustiX
Starting Member

28 Posts

Posted - 2011-05-19 : 07:57:52

Hi.

I am creating a stored procedure. In that procedure I declare
a cursor like this

DECLARE CURSOR TEST FOR
SELECT NAME FROM [SERVER].[DBNAME].[dbo].SOME_TABLE

I need to make my procedure more flexible
and allow the server name (@SERVERNAME) to
be a parameter in my procedure. Then I need
my cursor to be

DECLARE CURSOR TEST FOR
SELECT NAME FROM [@SERVERNAME].[DBNAME].[dbo].SOME_TABLE

The syntax of this gives me an error (of course)

Any ideas on how to do this?

Cheers,
Agust

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-19 : 08:06:27

If you're using SQL 2005 or higher, you can create synonyms that point to linked server objects. An easier method would be to re-create the synonym based on the server that's been passed:

IF OBJECT_ID('MySynonym') IS NOT NULL DROP SYNONYM MySynonym
EXEC('CREATE SYNONYM MySynonym FOR ' + QUOTENAME(@ServerName) + '.[DBNAME].[dbo].[SOME_TABLE]')

DECLARE CURSOR TEST FOR
SELECT NAME FROM MySynonym

Of course, running cursors against a linked server (already slow) will be even slower, but that's another discussion.

GustiX
Starting Member

28 Posts

Posted - 2011-05-19 : 08:25:08

That's brilliant, thank you.

I have never seen those synonyms before.
They could have made some of my code a
whole lot more readable

And yes... I have noticed the slowness
of linked server and cursors :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources