Tuesday, June 02, 2009

SQL: Executing a set of queries with different parameters in a loop

It has been quite long time since I did lot of SQL queries and recently had to get myself in one such task. There were some long set of queries to be executed in a loop that is supposed to run for around 50 times with different set of results.
With some search/research I found a quick solution using SQL Cursors and thought of sharing the same.
Example:
The scenario was as below:

DECLARE @Var1 NVARCHAR(100)

SET @Var1 = 'Some value'

SELECT VALUES FROM TABLENAME WHERE VALUE = @Var1
.
.
.
...-- many other similar queries using the value of @Var1

The challenge was to keep setting the @Var1 variable with 50 or more different values to get different set of results.
Instead of executing the above queries 50 odd times, the following steps could be used for once instead to achieve the same results:

1. The first step is to get the 50 or more odd values into a table using a condition that could be used further:
SELECT * INTO SETOFVALUESTABLE FROM SOMETABLE WHERE -- CONDITION

2. Next step is to declare a cursor that could accomplish our task:

DECLARE @Var1 NVARCHAR(100)

DECLARE db_cursor CURSOR FOR
SELECT COLUMNNAME FROM SETOFVALUESTABLE

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @Var1

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT VALUES FROM TABLENAME WHERE VALUE = @Var1
.
.
.
...-- many other similar queries using the value of @Var1

FETCH NEXT FROM db_cursor INTO @Var1
END

CLOSE db_cursor
DEALLOCATE db_cursor

Using the above steps saved me a lot of time and made it so simple and productive instead of the 50+ times execution of queries.

There might still be better efficient ways to achieve the same with which I plan to optimize, but the above method is one quick way to get around the problem we faced.

6 comments:

purush said...

Hi,

I think cursor will produce some performance issue, we can achieve this by using Table Variable and while loop instead of Cursor, it will give good performance also.

Thanks,
Purushoth(Chennai, India)

reganmchale said...

hello~..................................................

said...

成人女優電影成人圖片電影成人卡通電影成人免費影音電影av成人免費影音電影成人線上看成人線上看a片成人電影線上看寫真影片宏爺成人線上影片完全免費性影片學生性影片學生影片網學生巨乳影片學生妹色情影片辣妹胸部露點辣妹桌布辣妹個人聊天室辣妹俱樂部辣妹美眉愛愛辣妹掌门人辣妹援辣妹援交辣妹想跟男人做愛辣妹掰陰部辣妹視訊影音聊天室辣妹視訊網辣妹視訊秀辣妹視訊交友網辣妹絲襪高潮的插法遊戲高潮的好處高潮無碼下載高潮為何高潮+色情高潮小說高潮性愛體位高潮情色魔女之初影片魔女情色魔女視訊魔女色情魔女色色圖高潮了免費影片高潮椅子

假日的 said...

thank you for you to make me learn more,thank you∩0∩ ........................................

志源 said...

thank you for you to make me learn more,thank you∩0∩

HaroldM22 said...

Well done!............................................................