Solved

Use variable for IN clause

Posted on 2010-11-16
8
302 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
 
LVL 142

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
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 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 142

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 142

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

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.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

943 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now