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.
