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:
- The SQL statement that will be executed
- The parameters & types that's needed by the SQL statement (#1) in text format
- 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.