How to avoid Cursors?

Question

Post reply

How to avoid Cursors?

kishoregowtam

SSC Enthusiast

Points: 114
More actions
February 16, 2009 at 2:16 am

#103685

Hi,
I have requirement where I stores all quesries which need to be executed are stored in a table.
Name of Table: Table1
Column Name: Query--- Varchar(MAX)
Query contains values like "Selec * from emp", "Select * from dept".
In a SP i have to read all the values and execute them one by one.
Without using cursors is there any way to read and execute the values.
Thankx,
Gowtam

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

NitinPatel31 Hall of Fame Points: 3721 More actions · Answer 1

Yes it is possible using WHILE loop and temporary table (or table variable).

Regards,
Nitin

NitinPatel31 Hall of Fame Points: 3721 More actions · Answer 2

Use below script

DECLARE @max-2 INT, @cnt INT

DECLARE @queryString VARCHAR(MAX)

DECLARE @Query TABLE (id INT IDENTITY, queryString VARCHAR(MAX))

INSERT INTO @Query(queryString)

SELECT 'SELECT * FROM TAB1' UNION SELECT 'SELECT * FROM TAB2'

SET @max-2 = @@IDENTITY

SET @cnt = 1

WHILE @cnt<=@max

BEGIN

SELECT @queryString = queryString FROM @QueryWHERE id = @cnt

EXEC @queryString

SET @cnt = @cnt + 1

END

Regards,
Nitin

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 3

Or you can do something like...

DECLARE @strSQL VARCHAR(MAX)

SELECT @strSQL = COALESCE( @strSQL + '; ', '' ) + Query FROM Table1

PRINT @strSQL

--EXECUTE( @strSQL )

--Ramesh

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

Ramesh (2/16/2009)
Or you can do something like...
DECLARE @strSQL VARCHAR(MAX)
SELECT @strSQL = COALESCE( @strSQL + '; ', '' ) + Query FROM Table1
PRINT @strSQL
--EXECUTE( @strSQL )

Well done.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

kishoregowtam (2/16/2009)
Hi,
I have requirement where I stores all quesries which need to be executed are stored in a table.
Name of Table: Table1
Column Name: Query--- Varchar(MAX)
Query contains values like "Selec * from emp", "Select * from dept".
In a SP i have to read all the values and execute them one by one.
Without using cursors is there any way to read and execute the values.
Thankx,
Gowtam

Just an FYI... this is one of the few places where, as a DBA, I would probably not balk at the use of a cursor. The cursor would have very little impact on this so far as performance goes.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

kishoregowtam SSC Enthusiast Points: 114 More actions · Answer 6

Hi,

Thanks for your solution. Its really great to see a slution without using cursors as well as any loops.

The following solution also working..

DECLARE @strTemp VARCHAR(MAX)

SET @strTemp = ''

Select @strTemp = @strTemp + Query + '; ' From Test

PRINT @strTemp

EXEC (@strTemp)

Thanks,

Gowtam