Solved

Use variable for IN clause

Posted on 2010-11-16
8
301 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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

707 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

14 Experts available now in Live!

Get 1:1 Help Now