Solved

Use variable for IN clause

Posted on 2010-11-16
8
304 Views
Last Modified: 2012-05-10
Hi
I want to run a delete stored proc on a table like the following

DELETE  FROM bb_classes
    WHERE   ( AccountNo_FK = @AccountNo
              AND ClassName NOT IN (  @ExludeClasses  )

Classname is an nvarchar(50) datatype - could be Class 1 or Class 2 etc

Not having much luck getting this to work

How should the @ExcludeClasses parameter be constructed?

Wing
0
Comment
Question by:WingYip
8 Comments
 
LVL 41

Expert Comment

by:ralmada
ID: 34146865
So you mean that @excludeclasses could be something like a comma separated string?

set @excludeclasses = 'class1,class2,class3'

Then you can
1) create the parmstolist function attached below
2) use it in your delete query like this

DELETE  FROM bb_classes
    WHERE   ( AccountNo_FK = @AccountNo
              AND ClassName NOT IN (select [Value] from dbo.parmstolist(@ExludeClasses, ','))


create FUNCTION [dbo].[ParmsToList] (@Parameters varchar(8000), @delimiter varchar(10) ) 
returns @result TABLE (Value varchar(500), rn int identity) 
AS 
begin 
declare @dx varchar(9) 
-- declare @loops int 
--set @loops = 0 

DECLARE @TempList table 
( 
Value varchar(500) 
) 

if @delimiter is null set @delimiter = ' ' 
if len(@delimiter) < 1 set @delimiter = ' ' 
set @dx = left(@delimiter, case when @delimiter = ' ' then 1 else len(@delimiter) end -1) 

DECLARE @Value varchar(8000), @Pos int 

SET @Parameters = LTRIM(RTRIM(@Parameters))+ @delimiter 
SET @Pos = CHARINDEX(@delimiter, @Parameters, 1) 

IF REPLACE(@Parameters, @delimiter, @dx) <> '' 
BEGIN 
WHILE @Pos > 0 -- AND @Loops < 100 
BEGIN 
--set @loops = @loops + 1 
SET @Value = LTRIM(RTRIM(LEFT(@Parameters, @Pos - 1))) 
IF @Value <> '' 
BEGIN 
INSERT INTO @TempList (Value) VALUES (@Value) --Use Appropriate conversion 
END 
SET @Parameters = SUBSTRING(@Parameters, @Pos+ case when @delimiter = ' ' then 1 else len(@delimiter) end, 8000) 
SET @Pos = CHARINDEX(@delimiter, @Parameters, 1) 

END 
END 
INSERT @result 
SELECT value 
FROM @TempList 
RETURN 
END

Open in new window

0
 
LVL 10

Expert Comment

by:wls3
ID: 34146949
IN works against a subquery (which requires a select statement) or a list.  In your case, surrounding the variable with a pair of single quotes may work.  For example,

DELETE  FROM bb_classes
    WHERE   ( AccountNo_FK = @AccountNo
              AND ClassName NOT IN ( '' + @ExludeClasses  + '')

Note, there is a typo in the variable name for this post (@ExludeClasses), which, I assume, you do not have in the actual query.
0
 
LVL 40

Expert Comment

by:Sharath
ID: 34150107
If you do not want to create a function as ralmada mentioned, try like this.
DELETE FROM bb_classes 
      WHERE (AccountNo_FK = @AccountNo 
             AND ClassName NOT IN (SELECT Ltrim(Substring(ExcludeClasses,n,Charindex(',',ExcludeClasses + ',',n) - n)) AS ExcludeClasses 
                                     FROM (SELECT @ExcludeClasses ExcludeClasses) AS t1 
                                          CROSS JOIN (SELECT NUMBER 
                                                        FROM MASTER..spt_values 
                                                       WHERE TYPE = 'P') AS Numbers(n) 
                                    WHERE Substring(',' + ExcludeClasses,n,1) = ',' 
                                          AND n < Len(ExcludeClasses) + 1))

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34153960
I had written an article about that:
http://www.experts-exchange.com/A_1536.html
0
 
LVL 1

Expert Comment

by:VBisMe
ID: 34161370
Splitting up strings using a separator is a fairly nasty solution - though until recently, was the only solution.  Instead, you can use the new User Defined Table Type (UDTT) that SQL Server 2008 provides.

Create the UDTT and stored proc:

CREATE TYPE [dbo].[udttClassName] AS TABLE(
      [ClassName] nvarchar(50) NOT NULL)
GO

CREATE PROCEDURE dbo.DeleteClasses(
    @AccountNo as varchar(20),
    @ExcludeClasses as udttClassName READONLY
)
AS
    DELETE FROM bb_classes
    WHERE AccountNo_FK = @AccountNo
      AND ClassName NOT IN (SELECT ClassName FROM @ExcludeClasses)

GO


Execute your stored proc this way:

DECLARE @ClassNames as udttClassName
INSERT INTO @ClassNames SELECT 'Class1' UNION
                        SELECT 'Class2'
                       
EXEC DeleteClasses '123', @ClassNames
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34161774
VBisMe,
 
 the main issue is still the same: the input comes from the end-user, so you have to "split" the data (1 string) into several strings anyhow?
0
 
LVL 1

Expert Comment

by:VBisMe
ID: 34161887
I don't see anywhere in the above requirement that he specified that he must use a comma separated string as a parameter for his stored procedure.  

On the flip side, if you want to pass a sring, then you don't need to split them at all.  You can simply perform a LIKE statement as follows.  Note: Start/End string commas are required to ensure the full ClassName is matched.

DELETE FROM bb_classes
WHERE AccountNo_FK = @AccountNo
  AND (',' + @ExcludeClasses + ',') NOT LIKE ('%,' + ClassName + ',%')


Personally, I'd use a UDTT.
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 34162403
>I don't see anywhere in the above requirement that he specified that he must use a comma separated string as a parameter for his stored procedure.  
I do agree on that, I just speak from experience :)
the GUI for the user would be to enter 1 item per line in a grid, for example...

>You can simply perform a LIKE statement as follows.
just that even if it's NOT LIKE it will surely be not be able to use any index ...

furthermore, your code to call starts like this:
DECLARE @ClassNames as udttClassName
INSERT INTO @ClassNames SELECT 'Class1' UNION
                        SELECT 'Class2'

Open in new window

so, this means, that the SELECT part of the SQL needs to be done "ad-hoc" anyhow, based on the user input ...

for the rest, the suggestion is ok, but I really see the usefulness of UDT as procedure arguments only in scenarios where data has to be passed between procedures/functions ...

let's see what the asker really needs and what he can do
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question