[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 310
  • Last Modified:

Use variable for IN clause

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
WingYip
Asked:
WingYip
1 Solution
 
ralmadaCommented:
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
 
wls3Commented:
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
 
SharathData EngineerCommented:
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I had written an article about that:
http://www.experts-exchange.com/A_1536.html
0
 
VBisMeCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
VBisMeCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now