Dynamic SQL
June 8, 2004 · 2 Comment s
A problem I came up against a short while ago was how to get Stored Procedures in SQL Server to handle lists. The task in question involved inserting, updating or indeed deleting x number of rows in my database. Now, I could have initiated my <cfstoredproc></cfstoredproc> (or <cfquery></cfquery>) x number of times but to me this seemed unnecessary, after all why can't you just pass a coma-delimited list in?
Well, by default there is no way for SQL Server to handle a list, or indeed an array but as always there is at least one way around it, and my solution is posted below.
CREATE PROCEDURE usp_save_changes
@id varchar(1000) -- this should be big enough to handle a lengthy list
AS
SET NOCOUNT ON
DECLARE @separator_position int -- used to locate each separator character in list
DECLARE @list_value varchar(1000) -- holds each list value as it's returned
DECLARE @separator char(1) -- this is our separator character, which we are going to SET to a comma below
SET @separator=',' -- We could easily change the Stored Procedure so that the separator character can be passed in
-- We need an extra separator character at the end of the list we are passing in
SET @id = @id + @separator
-- Loop through list searching for separator characters
WHILE patindex('%' + @separator +'%', @id) <> 0
BEGIN
-- patindex matches the pattern against a string
SELECT @separator_position = patindex('%' + @separator + '%', @id)
SELECT @list_value = LEFT(@id,@separator_position -1)
-- NOW DO YOUR ACTION HERE (COULD BE INSERT/UPDATE/DELETE)
UPDATE newsletter
set active=1
WHERE id=@list_value
-- Replace what we just processed with an empty string
SELECT @id = stuff((@id, 1, @separator_position, ' ')
END
And there you have it, Dynamic SQL at work. @id varchar(1000) -- this should be big enough to handle a lengthy list
AS
SET NOCOUNT ON
DECLARE @separator_position int -- used to locate each separator character in list
DECLARE @list_value varchar(1000) -- holds each list value as it's returned
DECLARE @separator char(1) -- this is our separator character, which we are going to SET to a comma below
SET @separator=',' -- We could easily change the Stored Procedure so that the separator character can be passed in
-- We need an extra separator character at the end of the list we are passing in
SET @id = @id + @separator
-- Loop through list searching for separator characters
WHILE patindex('%' + @separator +'%', @id) <> 0
BEGIN
-- patindex matches the pattern against a string
SELECT @separator_position = patindex('%' + @separator + '%', @id)
SELECT @list_value = LEFT(@id,@separator_position -1)
-- NOW DO YOUR ACTION HERE (COULD BE INSERT/UPDATE/DELETE)
UPDATE newsletter
set active=1
WHERE id=@list_value
-- Replace what we just processed with an empty string
SELECT @id = stuff((@id, 1, @separator_position, ' ')
END
Tags: SQL Server
2 response s so far ↓
1 Arabic Translation Service // Aug 29, 2009 at 7:48 AM
2 ed hardy mens Jeans // Sep 17, 2009 at 9:16 AM
Leave a Comment