Solved

Use variable for IN clause

Posted on 2010-11-16
8
303 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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 setup several different housekeeping processes for a SQL Server.

813 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

17 Experts available now in Live!

Get 1:1 Help Now