Geeks With Blogs
New Things I Learned
I usually stay on the C# development of things, and rarely venture to create SQL statements/code, other than the occasional SELECT / UPDATE / INSERT.  However, we have a requirement from our Product Management group to DELETE records permanently from the database.  We have proper enforced foreign keys in our DB schema, so one option we have is to create a stored procedure to help deletion, which will walk thru the referencing foreign keys to the record being deleted and then also delete those records (the children records essentially).  Yes, I know records should not be deleted (it should just be marked as deleted or inactive or something), but it was a requirements insistence where my opinion was overridden.

Having the stored procedure (hence I'll refer to this as sproc) is nice since consumer just need to call the same sproc passing in the table name & record id to delete, and the sproc will do the heavy lifting.  However, the sproc has some tasks to solve; since children records can contain further children records, the sproc needs to call itself recursively.  Since the sproc needs to query different tables (depending on which record is deleted), the sproc has to build dynamic SQL statements and execute that.  Since you need the values of the record Ids that references the parent (so it can see if those records have further children), you also need a CURSOR to loop thru those Ids).

Separately, each of the problem is fairly simple enough, sprocs can call itself recursively, you can create sprocs that execute dynamic SQL, and you can have sprocs that has a CURSOR as OUTPUT.  However when you combine them together, I consider them to be a fairly neat challenge to try to tackle.  The hard part is actually with getting a CURSOR back from a dynamic SQL, so I'm going to focus on that part.

To execute dynamic SQL, you need to use the built-in sp_executesql sproc in SQL Server.  The sp_executesql sproc can accept the following parameters:
  1. The SQL statement that will be executed
  2. The parameters & types that's needed by the SQL statement (#1) in text format
  3. The actual parameters to use in that SQL statement

So, essentially sp_executesql will create a stored procedure, containing the SQL statement passed (#1), with the parameter list of that sproc as defined in #2 and then it will invoke that newly created stored procedure, passing in the parameters as defined in the latter parameters.

To solve the problem, then we just need to create a dynamic SQL that has a CURSOR parameter which is an OUTPUT parameter.  The following test sproc demonstrates this:

ALTER PROC TestProc

AS

       DECLARE @dynamicSQL nvarchar(200)

       -- Have code that will construct the dynamic SQL

       SET @dynamicSQL = 'SELECT FirstName FROM Contacts'

 

       -- The cursor that will be filled by the dynamic SQL

       DECLARE @outputCursor CURSOR

       -- Create the dynamic SQL to fill a CURSOR instead

       SET @dynamicSQL = 'SET @outputCursor = CURSOR FORWARD_ONLY STATIC FOR ' +

              @dynamicSQL + ' ; OPEN @outputCursor'

 

       -- Execute dynamic sql

       exec sp_executesql                  -- sp_executesql will essentially create a sproc

              @dynamicSQL,                 -- The SQL statement to execute (body of sproc)

              N'@outputCursor CURSOR OUTPUT', -- The parameter list for the sproc: OUTPUT CURSOR

              @outputCursor OUTPUT         -- The parameter to pass to the sproc: the CURSOR

 

       -- Code that will just output the values from the cursor

       DECLARE @firstName nvarchar(200)

       FETCH NEXT FROM @outputCursor INTO @firstName

 

       -- Loop while there're more things in the cursor

       WHILE @@FETCH_STATUS = 0

       BEGIN

              PRINT @firstName

              FETCH NEXT FROM @outputCursor INTO @firstName

       END

 

       -- Be nice, close & deallocate cursor

       CLOSE @outputCursor

       DEALLOCATE @outputCursor

I just choose to use a simple SELECT to a table as the dynamic SQL statement; you can make it to be whatever SELECT is needed based on parameter being passed.  Then the sproc declares a CURSOR, which will then be used to contain the result of the SQL statement.  The whole text, including the CURSOR is the dynamic SQL we want to execute.  sp_executesql is then called with the SQL statement, we then define the statement to have 1 parameter (which is the CURSOR as an OUTPUT parameter), and then we pass in the cursor as the object to pass into the dynamic SQL to be executed.  The latter half of the code just loops thru the cursor to proof that the results are proper.

Not having dealt with much SQL code, this was an interesting journey for me.  There are other ways to solve the problem (DELETE records), we can use cascade delete, but it doesn't support self-referencing tables.  In the end we didn't use this approach, instead we just create a sproc for each table that needs to be deleted; it seems like a heck of a lot of maintenance job, but I'll let the DBA handle that. Posted on Tuesday, August 26, 2008 11:49 AM SQL | Back to top


Comments on this post: Returning Cursor from Stored Procedure Executing Dynamic Query

# re: Returning Cursor from Stored Procedure Executing Dynamic Query
Requesting Gravatar...
This is the the best example of a true dynamic cursor for TSQL I have seen. I have searched the internet but only found examples of simple SQL statments, non of which the FETCH command could be used. Thanks for the discovery!!
Left by Lee on Dec 22, 2008 5:02 PM

# re: Returning Cursor from Stored Procedure Executing Dynamic Query
Requesting Gravatar...
Hi!, This is a really great piece. Thanks so much for providing astounding example on how to use dynamic query in cursors. I would like to ask related to your sample given above if it is possible to query a table in a different database. Like for example "SELECT database1.dbo.tablename1.firstname, database2.dbo.tablename2.lastname" but instead of having a fully qualified query in a specific column, local variable to be used and concatenating it. I'm not pretty sure if this can be done. Your response are highly valuable. Thanks in advance!
Left by roms on Jan 05, 2009 4:19 AM

# re: Returning Cursor from Stored Procedure Executing Dynamic Query
Requesting Gravatar...
Querying different tables/columns in different databases is possible (have to be the same server though); you can use local variables to contain your text (for the db/table/column names) & then concatenate it to form your SQL query. In relation to the article, that particular query can then be used to have its output sent to a cursor by the calling stored procedure. Make sure the login you use has access to both databases.

You can use the full name, like database1.dbo.tablename1.firstname, or a shorthand (as suggested here: http://www.sqlteam.com/article/selecting-data-from-different-databases) so you can write it as database1..tablename1.firstname. I thought that was pretty neat.
Left by Muljadi Budiman on Jan 05, 2009 1:23 PM

# re: Returning Cursor from Stored Procedure Executing Dynamic Query
Requesting Gravatar...
The above code gives me error that the variable @outputcursor does not have cursor allocated to it
Left by Sneha on Apr 18, 2009 12:48 AM

# re: Returning Cursor from Stored Procedure Executing Dynamic Query
Requesting Gravatar...
Wow...This is a great. This code helps me a lot. Thank u for providing such a great example...
Left by Krishnraj Rana on Aug 11, 2009 1:31 AM

# re: Returning Cursor from Stored Procedure Executing Dynamic Query
Requesting Gravatar...
Another wow... I stumbled upon this after discovering Sprocs could actually output cursors. This demonstration is very clever and valuable. I have plans to use it. I'll give you full credit Muljadi! keep it up
Left by JH Higgins on Aug 21, 2009 8:26 AM

# re: Returning Cursor from Stored Procedure Executing Dynamic Query
Requesting Gravatar...
Nice Post. Really Help me a lot.
Left by Prabir Basak on Mar 07, 2010 10:12 PM

# re: Returning Cursor from Stored Procedure Executing Dynamic Query
Requesting Gravatar...
Great post, very useful. Thanks a lot for sharing your knowledge
Left by Xavier García on Apr 20, 2010 8:04 PM

# re: Returning Cursor from Stored Procedure Executing Dynamic Query
Requesting Gravatar...
I also wrote stored procedure as you wrote. It gets executed in MS SQL. My problem is how to capture the cursor when I call it through code in C#. There is no datatype which matches 'cursor'. Please let me know about that. Thanks in advance.
Left by Sushil Pugalia on Jul 12, 2012 1:39 PM

# re: Returning Cursor from Stored Procedure Executing Dynamic Query
Requesting Gravatar...
Very good example and explanation. Very good site too , nicely laid out
Left by Nidhin thomas on Dec 07, 2012 4:54 AM

# re: Returning Cursor from Stored Procedure Executing Dynamic Query
Requesting Gravatar...
Executed without any error. Great work buddy keep it up !!!
Left by Vinay on Aug 18, 2013 11:05 PM

Your comment:
 (will show your gravatar)


Copyright © Muljadi Budiman | Powered by: GeeksWithBlogs.net