Query based on more than one parameter being passed in

Question

I am working on a query / stored procedure that for simplicity simply says "Select * from table" Im about to change this to say select * from table where location=@location.

However, in saying this I may have to pass in more than one location. I do not necessarily want to create 30 @location variables and say when location =@location1 and location=@location2 etc etc. So what I have elected to do is,

SELECT *
FROM 
TABLE
WHERE 
LOCATION
IN (SELECT location FROM locationtable)

HOWEVER, again, the "locationtable" houses everylocation. (say locationtable has location A,B&C) how would I edit this to say select * where location is A & B ( bearing in mind A&B is passed in as parameters)

I would like to know if there is a way I can accomplish something similar in my stored procedure to do what I jut said.

Thank you

`Select * from location where location in (@locations)` and build locations as a `'A','B'` list. — xQbert, Jul 29 '14 at 15:18
you could pass in a table variable to the stored procedure, then use `where location in (select location from @locationtable)` — Jaloopa, Jul 29 '14 at 15:21
@xQbert Im not exactly sure how to/what you mean by "build locations as an 'A','B', list. — Liberace, Jul 29 '14 at 15:40
@Jaloopa locationtable has all the locations. I would only want to select those pasted in by my proc — Liberace, Jul 29 '14 at 15:46
@user3637951 rather than rehash... http://stackoverflow.com/questions/337704/parameterize-a-sql-in-clause — xQbert, Jul 29 '14 at 16:59

Harbinger · Accepted Answer · 2014-07-29T18:02:42.960

Create a function to split a list into a table. I wrap the results in a Common Table Expression (CTE) then join to that CTE. There are performance advantages to convert large lists from an in statement to a table for joins.

FUNCTION:

Example Execution of Function: select * from dbo.fn_SplitListToTable('one,two,three', ',')

CREATE FUNCTION [dbo].[fn_SplitListToTable] 
(   
    -- Add the parameters for the function here
    @list nvarchar(max)
    ,@delimiter char(1)
)
RETURNS @table TABLE 
(
    value nvarchar(max) 
)
AS
BEGIN
    declare @value nvarchar(max)

    while CHARINDEX(',', @list) > 0
    begin
        select 
            @value = SUBSTRING(@list, 1, CHARINDEX(',', @list) - 1)
            ,@list = SUBSTRING(@list, CHARINDEX(',', @list) + 1, LEN(@list) - CHARINDEX(',', @list))

        insert into @table select @value
    end

    insert into @table select @list

    RETURN
END

Pseudo Stored Procedure Example:

CREATE PROCEDURE [dbo].[prc_rpt_SplitListToTable]
    @List varchar(max)
AS
BEGIN

--Parameter Sniffing
Declare @ListLocal varchar(max) = @List;

--Function to split list into table for multiple criterion selection
with CreateTableFromList as
(
    select value as ListItem from [dbo].[fn_SplitListToTable](@ListLocal, ',')
)

Select * from SourceTable st
inner join CreateTableFromList tbl --CTE that now contains a table of values instead of list
    on st.value = tbl.value

END

From just looking at this proc, I am not exactly sure where you are specifying the values to be in "locationtable" — Liberace, Jul 29 '14 at 17:49
The With statement takes the string you passed into your stored procedure such as 'Location1, location2, location3, etc...' and builds a table (CTE) with column ListItem. Location1, 2, 3, etc are now rows in that table. Once they are in a table you will need to build a join to that table and in the on statement that will filter the results. — Harbinger, Jul 29 '14 at 18:06
exec [dbo].[prc_rpt_SplitListToTable] @list='t','a' results in an error ( i am attempting to pass in two paramaters to the proc) then it ask me to pass it in as '@name = value' but @list='t',@list='a' also results in error — Liberace, Jul 29 '14 at 18:20

Query based on more than one parameter being passed in

1 Answers1