Databases
StackOverflow2013
Postsdbo
POA different approach to using a SQL cursors in SP?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POA different approach to using a SQL cursors in SP?
    primarykey
    data
    text
    <p>I am writing a stored procedure in SQL and I am currently in the position of having 3 nested cursors (which isn;t running quite how I would like). I'd appreciate any suggestions on how I can break out of the cursor is the solution trap</p> <p>I basically have 2 tables (both temporary during the SP populated by SELECT statements from the application)</p> <p>1 - A list of tables, columns and datatypes</p> <pre><code>Table1 | SURNAME | VARCHAR | SEX | VARCHAR | DOB | DATETIME ------------------------------ Table2 | ADDRESS | VARCHAR ------------------------------ Table3 | SALARY | INT ------------------------------ Table4 | USERNAME | VARCHAR | PASSWORD | VARCHAR </code></pre> <p>2 - A one column list of user numbers</p> <p>My application has to loop through each user number, then for each user I have to loop through the distinct tables (Table1, Table2, Table3, Table4) and see if there is any data in that tabel for that user (using dyanmic SQL so I can pass the table name as a parameter). If there is, I then have to then loop through each column that relates to that table and build a dynamic SQL INSERT statement</p> <p>So for user number 2... any data in Table1? No - skip any data in Table2? Yes - copy rows into temp table then build dynamic SQL for ADDRESS value any data in Table3? No - skip any data in Table4? Yes - copy rows into temp table then build dynamic SQL for USERNAME and PASSWORD columns</p> <p>The cursor approach works fine from a functionality point of view but performance is a little slow. I've made sure the 2 source tables are as tight as possible, when creating my dynamic SQL I copy the relevant rows only into a temp table to process, I've made my cursors FAST_FORWARD and READ_ONLY</p> <p>Is there any approach I can take instead?</p> <p>ORIGINAL CODE POSTED AS REQUESTED:</p> <pre><code>IF OBJECT_ID('tempdb..##MembersToDelete') IS NOT NULL DROP TABLE ##MembersToDelete IF OBJECT_ID('tempdb..##TempDataTable') IS NOT NULL DROP TABLE ##TempDataTable DELETE FROM @CompSetTable DELETE FROM DataRefreshDeletes DELETE FROM DataRefreshInserts --BUILD THE INDIVIDUAL SELECT STATEMENTS INTO A TEMP TABLE SET @RowPosition = 1; SET @inSelectFilter = @inSelectFilter + ';'; SET @inSelectFilter = REPLACE(@inSelectFilter,'/',''''); WHILE LEN(@inSelectFilter) &gt; 0 BEGIN IF PATINDEX('%;%',@inSelectFilter) &gt; 0 BEGIN SET @SelectParameter = SUBSTRING(@inSelectFilter, 0, PATINDEX('%;%',@inSelectFilter)) SET @inSelectFilter = SUBSTRING(@inSelectFilter, LEN(@SelectParameter + ';') + 1,LEN(@inSelectFilter)) IF @RowPosition = 1 BEGIN INSERT INTO @SelectParameterTable VALUES ('WHERE ' + @SelectParameter) END ELSE BEGIN INSERT INTO @SelectParameterTable VALUES (' AND ' + @SelectParameter) END SET @RowPosition = @RowPosition + 1 END ELSE BEGIN SET @SelectParameter = @inSelectFilter SET @inSelectFilter = NULL END END --BUILD THE COMPLETE DELETE STATEMENT SET @SelectParameter = NULL; SELECT @SelectParameter = COALESCE(@SelectParameter, '') + statementString FROM @SelectParameterTable --INSERT THE MEMBER NUMBERS INTO THE TEMP TABLE IF OBJECT_ID('tempdb..##MembersToDelete') IS NOT NULL DROP TABLE ##MembersToDelete SET @SelectParameter = 'SELECT MEMBNO INTO ##MembersToDelete FROM BASIC ' + @SelectParameter BEGIN TRY EXECUTE sp_executesql @SelectParameter END TRY BEGIN CATCH Print 'The following statement could not be run - please check the syntax...' Print @SelectParameter GOTO cleanUpAndFinish END CATCH SELECT @MembersToDeleteCount = COUNT(*) FROM ##MembersToDelete Print '##MembersToDelete TABLE BUILT - ' + CONVERT(VARCHAR(26), GETDATE(), 109) --BUILD LIST OF COMPENDIA TABLES (ORDERED BY DSET) DELETE FROM @CompSetTable INSERT INTO @CompSetTable SELECT d.DSNAME, c.column_name, d.DSET, c.data_type FROM DICTIONARY d, INFORMATION_SCHEMA.COLUMNS c WHERE DNUM = -1 AND DSET &lt; 250 AND c.table_name = d.DSNAME ORDER BY d.DSET Print '@CompSetTable TABLE BUILT - ' + CONVERT(VARCHAR(26), GETDATE(), 109) DECLARE setInsertCursor CURSOR GLOBAL FAST_FORWARD READ_ONLY FOR SELECT DISTINCT setName, setNumber FROM @CompSetTable ORDER BY setNumber --WE NOW HAVE THE LIST OF MEMBER NUMBERS AND THE LIST OF TABLES TO BUILD THE DELETE STATEMENT SELECT @MemberNumberString = COALESCE(@MemberNumberString + ', ', '') + LTRIM(STR(MEMBNO)) FROM ##MembersToDelete DECLARE setDeleteCursor CURSOR READ_ONLY SCROLL FOR SELECT DISTINCT setName, setNumber FROM @CompSetTable ORDER BY setNumber OPEN setDeleteCursor FETCH LAST FROM setDeleteCursor INTO @SetName, @SetNumber WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO @DeleteStatementTable VALUES ('DELETE FROM ' + UPPER(@SetName) + ' WHERE MEMBNO IN (' + @MemberNumberString + ')') FETCH PRIOR FROM setDeleteCursor INTO @SetName, @SetNumber END CLOSE setDeleteCursor DEALLOCATE setDeleteCursor Print '@DeleteStatementTable TABLE BUILT - ' + CONVERT(VARCHAR(26), GETDATE(), 109) DECLARE memberInsertCursor CURSOR FOR SELECT MEMBNO FROM ##MembersToDelete OPEN memberInsertCursor FETCH NEXT FROM memberInsertCursor INTO @MemberNumber WHILE @@FETCH_STATUS = 0 BEGIN --NOW BUILD THE INSERT STATEMENTS OPEN setInsertCursor FETCH NEXT FROM setInsertCursor INTO @SetName, @SetNumber WHILE @@FETCH_STATUS = 0 BEGIN --CHECK IF MEMBER HAS ANY ROWS IN THIS SET - IF NOT, SKIP SET @ROWCOUNT = 0 SELECT @COUNTSQL = N'SELECT @countOUT = COUNT(*) FROM ' + @SetName + ' WHERE MEMBNO = ' + LTRIM(STR(@MemberNumber)) EXEC sp_executesql @COUNTSQL, N'@countOUT INT OUTPUT', @countOUT=@ROWCOUNT OUTPUT; IF @ROWCOUNT = 0 BEGIN GOTO nextSet END SET @VALUES = NULL; --DROP TEMPORARY TABLE IF OBJECT_ID('tempdb..##TempDataTable') IS NOT NULL DROP TABLE ##TempDataTable --POPULATE TEMPORARY TABLE SET @SQL = 'SELECT * INTO ##TempDataTable FROM ' + @SetName + ' WHERE MEMBNO = ' + LTRIM(STR(@MemberNumber)) EXECUTE sp_executesql @SQL --BUILD SELECT STATEMENT SET @INSERTSTRING = NULL SET @INSERTSTRING = CAST('' as nVarChar(MAX)) + 'SELECT ''INSERT INTO ' + @SetName + ' VALUES (''' DECLARE setColumnCursor CURSOR FAST_FORWARD READ_ONLY FOR SELECT columnName, dataType FROM @CompSetTable WHERE setName = @SetName OPEN setColumnCursor FETCH NEXT FROM setColumnCursor INTO @ColumnName, @DataType WHILE @@FETCH_STATUS = 0 BEGIN IF @DataType IN ('text','varchar','nvarchar','ntext','char') BEGIN SET @INSERTSTRING = CAST('' as nVarChar(MAX)) + @INSERTSTRING + ''''' + ISNULL(' + @ColumnName + ',''NULL'') + '''''',''' END ELSE IF @DataType IN ('int','decimal','smallint','numeric','tinyint','bigint','float') BEGIN --SET @INSERTSTRING = @INSERTSTRING + ' + ' + @COLUMNNAMENULL + ' + '',''' SET @INSERTSTRING = CAST('' as nVarChar(MAX)) + @INSERTSTRING + ' + ISNULL(CONVERT(VARCHAR(MAX),' + @ColumnName + '),''NULL'')' + ' + '',''' END ELSE IF @DataType IN ('datetime') BEGIN --SET @INSERTSTRING = @INSERTSTRING + ' + ' + @COLUMNNAMENULL + ' + '',''' SET @INSERTSTRING = CAST('' as nVarChar(MAX)) + @INSERTSTRING + ''''' + ISNULL(CONVERT(VARCHAR(MAX),' + @ColumnName + '),''NULL'')' + ' + '''''',''' END FETCH NEXT FROM setColumnCursor INTO @ColumnName, @DataType END CLOSE setColumnCursor DEALLOCATE setColumnCursor SET @INSERTSTRING = @INSERTSTRING + '+'')''' SET @INSERTSTRING = @INSERTSTRING + ' FROM ##TempDataTable' INSERT INTO @InsertStatementTable EXECUTE sp_executesql @INSERTSTRING nextSet: FETCH NEXT FROM setInsertCursor INTO @SetName, @SetNumber END FETCH NEXT FROM memberInsertCursor INTO @MemberNumber END CLOSE memberInsertCursor DEALLOCATE memberInsertCursor CLOSE setInsertCursor DEALLOCATE setInsertCursor </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USMike
    1. USMike
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POA different approach to using a SQL cursors in SP?
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    2. VO
      singulars
      1. POA different approach to using a SQL cursors in SP?
      1. This table or related slice is empty.
      1. VTDownMod
    3. VO
      singulars
      1. POA different approach to using a SQL cursors in SP?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COMight be easier to understand what you are doing if you post your existing code - i.e. the existing stored proc
      singulars
      1. POA different approach to using a SQL cursors in SP?
      1. USFergus Bown
    2. COEach of your tables is missing a user number column, I think. Otherwise, how do you correlate between your tables?
      singulars
      1. POA different approach to using a SQL cursors in SP?
      1. USTim Coker
    3. CO@TimCoker - there is no correlation - it is "For each row in user table perform an action using each row in the Table table". So if I have 20 rows in the user table and 10 rows in the Table table, I will loop 20 times and for each loop, loop 10 times.
      singulars
      1. POA different approach to using a SQL cursors in SP?
      1. USMike
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload