Useing Dymanic SQL and Cursors

[Nordlund]

Is there a way to use cursors on dymanically created queries or has anyone other solutions in their bags?

 DECLARE deviceid_cursor CURSOR FOR 
   SELECT * FROM @TableName

 OPEN deviceid_cursor
  FETCH NEXT FROM deviceid_cursor INTO @DeviceKey

 CLOSE deviceid_cursor
 DEALLOCATE deviceid_cursor

[tt]~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Do not underestimate the power of [!]Google[/!][/tt]

 

[dhulbert]

I've got this one using cursors to find the values to generate dynamic SQL if it's any help.

It loops through all of the tables in a specific catalogue then generates insert selects to populate a history table.

DECLARE @TABLENAME AS NVARCHAR(50)
DECLARE @DYNSQL AS NVARCHAR(250)
DECLARE @COUNTA AS NUMERIC
DECLARE TABLE_CURSOR CURSOR FOR
	SELECT	TABLE_NAME
	FROM	INFORMATION_SCHEMA.TABLES
	WHERE	TABLE_CATALOG = 'CAT01';
OPEN TABLE_CURSOR
	FETCH NEXT FROM TABLE_CURSOR
		WHILE @@FETCH_STATUS  = 0
			BEGIN
				FETCH NEXT FROM TABLE_CURSOR
				INTO @TABLENAME;
				SET @DYNSQL = 
				'INSERT INTO REPORT.TBL_TABLECHECK SELECT ''' + @TABLENAME + ''', 
				COUNT(*) as tablerows ,
				MAX(TOTAL_ROW_COUNT) as maxrow FROM LOADER.' + @TABLENAME 
				PRINT @DYNSQL;
				EXEC (@DYNSQL);
			END;
CLOSE TABLE_CURSOR;
DEALLOCATE TABLE_CURSOR;

I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[Nordlund]

Thanks.
There is no problem to create the dynamic sql, but to parse the data in the dymanic created sql is a little bit tricky.
Especially when the procedure has to be executed fast.

I have Temperature data in 2 different tables: Temp1 and Temp2:

This table defines where the temperature data are stored:

SensorGroup   TemperatureTable
MainGroup 1   Temperature1
MainGroup 2   Temperature2

When I want to create a diagram from the data, i execute a stored procedure numerus of times with 2 arguments (Sensorgroup and a deviceid)

sp_GetTemp 'MainGroup 1', 1
sp_GetTemp 'MainGroup 1', 2
sp_GetTemp 'MainGroup 1', 3
sp_GetTemp 'MainGroup 2', 2
sp_GetTemp 'MainGroup 2', 5

In the above mentioned procedure I have to do a lookup on 'MainGroup 1' and 'MainGroup 2' to find out where the data are stored, and then loop through the dataset to eliminate a bunch of error readings.

I have solved it by creating a temporary table and then do a dynamic INSERT into the temptable.
When i have the data into a temptable, I'm able to loop through it.

It feels a little bit unnessecary to copy the data into a temptable just to loop through the data when the data is stored the same way already. the difference is that the @Tablename variable is dynamic, and the temptable's name is static.

Did you guys understand a word i wrote, I just threw out a bunch of thoughts?


[tt]~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Do not underestimate the power of [!]Google[/!][/tt]