Help with using an In Statement when declaring a parameter

Question

Help with using an In Statement when declaring a parameter

sdgray01

SSC-Addicted

Points: 401
More actions
July 28, 2011 at 9:37 am

#255009

Hi,
I'm fairly new to sql and i'm just starting to grasp the basics, so please bare with me.
Anyway, what i want to be able to do is to use an IN statement which i Declare a parameter, for example;
DECLARE @level varchar(5)
Set @level IN ('Level1','Level2','Level8')
--- and then when i come to call this i'd use something like;
Select * From Table1 where level IN @Level
HOWEVER, it doesn't seem to work and can't see any examples around the internet of how you get it to recognise a range of OR values -what am i missing, is this possible and if so how. Also, to add a bit of complexity i'll be using 5 different declared parameters.
Hope this makes sense.
Thanks
S

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323546 More actions · Answer 1

parameters don't do what you are expecting them to do there...@level is a string, which [by coincidence] contains commas, which makes you think it might do somethign special.

to do what you want, you'll wan tot grab a copy of one of the split functions; which converts a delimited list into a table

i really suggest this one:DelimitedSplit8K

then you can do this:

DECLARE @level varchar(8000)

Set @level='Level1,Level2,Level8'

Select * From Table1 where level IN (dbo.DelimitedSplit8K(@Level)

one of several copies here on the forums:

CREATE FUNCTION dbo.DelimitedSplit8K

--===== Define I/O parameters

(@pString VARCHAR(8000), @pDelimiter CHAR(1))

RETURNS TABLE WITH SCHEMABINDING AS

RETURN

--===== "Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000...

-- enough to cover VARCHAR(8000)

WITH E1(N) AS (

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

), --10E+1 or 10 rows

E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows

E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max

cteTally(N) AS (--==== This provides the "zero base" and limits the number of rows right up front

-- for both a performance gain and prevention of accidental "overruns"

SELECT 0 UNION ALL

SELECT TOP (DATALENGTH(ISNULL(@pString,1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4

),

cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)

SELECT t.N+1

FROM cteTally t

WHERE (SUBSTRING(@pString,t.N,1) = @pDelimiter OR t.N = 0)

)

--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.

SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY s.N1),

Item = SUBSTRING(@pString,s.N1,ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000))

FROM cteStart s

;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

Unfortunately you can't do it as simply as that. You could use dynamic SQL, but that has many pitfalls. The best thing to do is use a splitter function (search this site) to break your string into its elements, and then join that to your table to get your results.

John

sdgray01 SSC-Addicted Points: 401 More actions · Answer 3

Thanks for this it seems there is a way. However, I can't seem to get it to work, i just get the following error message

Msg 4121, Level 16, State 1, Line 4

Cannot find either column "dbo" or the user-defined function or aggregate "dbo.DelimitedSplit8K", or the name is ambiguous.

What am i doing wrong?, how should the Function code look in my example.

Sorry for being so cheeky.

Thanks

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

The function definition is there in Lowell's second block of code. You need to create the function before the first block of code will work. By the way, it looks as if a close parenthesis is missing from the end of the first block.

John

sdgray01 SSC-Addicted Points: 401 More actions · Answer 5

Thanks,

Yeha i ran the function first, refreshed the DB to be sure and added the closing parameter and still i get the same error ?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 6

Does it work if you change the last line something like this?

Select * From Table1 where level IN (SELECT Item FROM dbo.DelimitedSplit8K(@Level))

John

sdgray01 SSC-Addicted Points: 401 More actions · Answer 7

Hi, firstly thank you very much for your help on this. But, i'm afraid not...now i get the following;

Msg 313, Level 16, State 3, Line 10

An insufficient number of arguments were supplied for the procedure or function dbo.DelimitedSplit8K.

Sorry! as you can tell i'm pretty much a SQL novice, so any help is greatly appreciated

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 8

Right, you should be able to solve that one yourself. Look at the function definition - how many arguments are required?

You call a table valued function like this:

SELECT <column list> FROM MyFunction(arg1,arg2,...,argn)

John

sdgray01 SSC-Addicted Points: 401 More actions · Answer 9

Nope still beat....sorry i thoght i had included all the arguements required, it looks right to me what i've done 🙁

Sorry about this

sdgray01 SSC-Addicted Points: 401 More actions · Answer 10

Well i've cracked the number of arguement issue, i was being so dumb.

UNFORTUNATELY, though the query now just returns everything and ignores the search parameters completely....what am i missing?, it must be something quite obvious and i'm just being blind.

Thanks

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 11

Please can we have a CREATE TABLE statement for Table1, sample data in the form of INSERT statements, and what results you expect to see.

Thanks

John

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 12

Am suprised no one has suggested using a table datatype or a table variable yet

Jayanth Kurup[/url]

sdgray01 SSC-Addicted Points: 401 More actions · Answer 13

Hi,

When i creat a ne table and insert sample dat into it it still returns all the values, but when i do a join to that table it limits them correctly, but that obviously that ignores the function. Is there anyway where the function can be adapted so that it makes a table with these seperate values in, and then i could simple do a inner join against this table. I think i'd understand that a little more.

Thanks

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 14

If you want to join to the results of a table valued function, use CROSS APPLY or OUTER APPLY. Please post DDL, sample data, results and expected results if you need specific help - it's difficult to visualise just from a description.

John