Multiple where conditions in SQL

Stainless_Steel

Hi,

Hoping someone can help me.

I have a stored procedure (MS SQL Server) that selects data. The where clause has a parameter @LOCATION. I pass the value of location from my front end (VB.NET).

A change was requested to allow the user to select multiple locations and filter accordingly.

I've thought about creating multiple @LOCATION parameters (@LOC1, @LOC2 etc) and modifying the where clause to only use the ones that are not blank. I think I could do this, but it seems like one of those things that there has to be a better solution for!

What if I had 50 possible locations....the SP would be a nightmare!

So what are the other solutions out there???

Thanks

Budawanny

Stainless_Steel wrote: »

Hi,

Hoping someone can help me.

I have a stored procedure (MS SQL Server) that selects data. The where clause has a parameter @LOCATION. I pass the value of location from my front end (VB.NET).

A change was requested to allow the user to select multiple locations and filter accordingly.

I've thought about creating multiple @LOCATION parameters (@LOC1, @LOC2 etc) and modifying the where clause to only use the ones that are not blank. I think I could do this, but it seems like one of those things that there has to be a better solution for!

What if I had 50 possible locations....the SP would be a nightmare!

So what are the other solutions out there???

Thanks

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...);

BrokenArrows

Stainless_Steel wrote: »

I've thought about creating multiple @LOCATION parameters (@LOC1, @LOC2 etc) and modifying the where clause to only use the ones that are not blank. I think I could do this, but it seems like one of those things that there has to be a better solution for!

I think this is your only option. I cant think of anything else.

Stainless_Steel

Budawanny wrote: »

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...);

If I was doing inline SQL then this would be easy. But I've to declare the parameters in a procedure.

RangeR

Stainless_Steel wrote: »

If I was doing inline SQL then this would be easy. But I've to declare the parameters in a procedure.

Is this for school / college or for work?

Maximus Alexander

I don't know if it's an option for you, but you could always send the entire query from your front end instead of using a stored procedure.

Then you could have:

SELECT column_name
FROM table_name
WHERE column_name IN (<placeholder>)

Which would allow you to replace <placeholder> with any string.

Budawanny

Stainless_Steel wrote: »

If I was doing inline SQL then this would be easy. But I've to declare the parameters in a procedure.

can you not pass in an array?? or does that ansi standard not work in SQL server or have you tried?

seamus

You can't really parameterise an IN clause. If you can, perhaps change the statement to LINQ, or if you're really stuck you can call the SQL directly from the script.

If you have to use the sproc, there are options;

Pass an XML parameter: http://stackoverflow.com/questions/1069311/passing-an-array-of-parameters-to-a-stored-procedure
Pass in a CSV list and then split it inside the sproc: http://stackoverflow.com/questions/11102358/how-to-pass-an-array-into-a-sql-server-stored-procedure

You can possibly also call the sproc once for every location that's selected. You can then filter out duplicates in the code, or you could possibly use the UNION ALL statement to do it for you (I haven't tested that though). That's a fairly messy one though and performance will take a big hit.

Gordon

Can you not just send the locations via front end, so that it sends "Location='Loc1' OR Location='Loc2'" and the procedure is amended to fit?

markpb

If you're using MS-SQL, you can create a table value parameter. You define a new type in SQL which is an array of locations, populate the array in your client side code and then pass the array into the SP or parameter. Then you can use WHERE IN to you hearts content.

Gordon wrote: »

Can you not just send the locations via front end, so that it sends "Location='Loc1' OR Location='Loc2'" and the procedure is amended to fit?

Wouldn't this be open to SQL injection attacks?

viper123

Or pass it in as XML and decode it into a @table in sql, use a join to this table instead of a where clause.

For an example :- http://stackoverflow.com/questions/15128999/passing-xml-string-parameter-to-sql-server-stored-procedure

db

You could pass in a long string containing all the possible values, append that to a variable containing the rest of the SQL statement and execute it using EXEC. This is the easiest way to do it but not as elegant as Viper123's solution.

aristotle25

This is what I do:

Pass in a parameter to the stored procedure as a varchar(max). That will be just a string with all the items separated by a comma e.g. 'location1,location2,location3,location4'

Then in the stored procedure I extract the items into a table using a function as per below.

Call that function into your stored procedure to get back a table variable e.g.
declare @tempTable table (location varchar(50))

insert @tempTable (location )
select * from dbo.SplitCommaSeperatedListIntoTable(@locations)

Then use that @temptable to join against whatever other tables you want into your sql select query.


--Function
create FUNCTION [dbo].[SplitCommaSeperatedListIntoTable] (@List As varchar(max))
RETURNS @Items table (Item int)
AS

BEGIN
DECLARE @Item As varchar(8000), @Pos As int
WHILE DATALENGTH(@List)&gt;0
BEGIN
SET @Pos=CHARINDEX(',',@List)
IF @Pos=0 SET @Pos=DATALENGTH(@List)+1
SET @Item = LTRIM(RTRIM(LEFT(@List,@Pos-1)))
IF @Item&lt;>'' INSERT INTO @Items SELECT @Item
SET @List=SUBSTRING(@List,@Pos+DATALENGTH(','),len(@List))
END
RETURN
END

Gordon

markpb wrote: »

If you're using MS-SQL, you can create a table value parameter. You define a new type in SQL which is an array of locations, populate the array in your client side code and then pass the array into the SP or parameter. Then you can use WHERE IN to you hearts content.



Wouldn't this be open to SQL injection attacks?

Yeah suppose so, I should have added that I'm not an experienced coder!

Stainless_Steel

Thanks for all the replies!

This is an actual application, not college work. I am a hobbyist coder turned part time developer (eek!).

I will read up on the suggested techniques and revert.

seamus

It is worth remembering that there may be multiple places where this sproc is called, even from within other sprocs, so making fundamental changes to the parameters which are passed in can cause the rest of the application to fall over.

So solutions which maintain the status quo can be the "best" ones. In this case, the solution where you pass in a CSV list and then split it out can be implemented and any code which just passes a single variable will still work fine.

If you wanted to use XML or another solution, you could set up a second parameter. So something like

CREATE PROCEDURE dbo.GetLocationDetailsFromId
    @LOCATION int = NULL,
    @LOCATION_LIST XMLDoc = NULL
AS
    SET NOCOUNT ON; 

-- Validate the parameters.
IF @LOCATION IS NULL AND @LOCATION_LIST IS NULL
BEGIN
   PRINT 'ERROR: Missing parameter. Please specify @LOCATION or @LOCATION_LIST.'
   RETURN
END

IF @LOCATION_LIST IS NULL 
   BEGIN
        -- Get single location
   END
ELSE
   BEGIN
       -- Get multiple locations
   END

viper123

aristotle25 wrote: »

This is what I do:

Pass in a parameter to the stored procedure as a varchar(max). That will be just a string with all the items separated by a comma e.g. 'location1,location2,location3,location4'

Then in the stored procedure I extract the items into a table using a function as per below.

Call that function into your stored procedure to get back a table variable e.g.
declare @tempTable table (location varchar(50))

insert @tempTable (location )
select * from dbo.SplitCommaSeperatedListIntoTable(@locations)

Then use that @temptable to join against whatever other tables you want into your sql select query.


--Function
create FUNCTION [dbo].[SplitCommaSeperatedListIntoTable] (@List As varchar(max))
RETURNS @Items table (Item int)
AS

BEGIN
DECLARE @Item As varchar(8000), @Pos As int
WHILE DATALENGTH(@List)&gt;0
BEGIN
SET @Pos=CHARINDEX(',',@List)
IF @Pos=0 SET @Pos=DATALENGTH(@List)+1
SET @Item = LTRIM(RTRIM(LEFT(@List,@Pos-1)))
IF @Item&lt;>'' INSERT INTO @Items SELECT @Item
SET @List=SUBSTRING(@List,@Pos+DATALENGTH(','),len(@List))
END
RETURN
END

This will work fine so long as there are no commas in the actual search criteria. if there is then you'll have to either encode them or strip them out somehow.

Lemming

Regards the stack overflow samples for XML; it should be noted that using sp_preparexmldocument will give you a notable performance hit. It is also very much avoidable if you are using any version of SQL Server greater than 2000 (i.e. 2005/2008/2008R2/2012) as XML has been a native type since SQL Server 2005.

Personally, I would either pass in an XML string and use XPath to create a table variable inside your sproc that you can then inner join against for your query (instead of using preparexmldocument). This allows you to throw as many additional location values into the mix as you like.

Or use the aforementioned table based parameter.

stevenmu

I kinda hate myself for suggesting this, but sometimes the path of least resistance is the best; if performance/scalability isn't an issue you could always just split up the values on the client side, call the SP once for each value and merge the results.