SQL Server - Dynamic "and" condition in sql

Asked By kughan j on 27-Nov-11 11:21 PM

Hi,

I need to handle a search criteria with four parameters.

The condition is, if the user inputs parameters may be (1 or 2 or 3 or 4)
based on the input the search should happen by using "and" condition for the passed parameters.

How to write an query for that?
Thanks,
Kughan

Jitendra Faye replied to kughan j on 27-Nov-11 11:25 PM

For that use Default parameter in your Stored procedure like this-

CREATE PROCEDURE SelectEmployees ( @Param1 int = NULL, @Param2 int = NULL)
AS

if( @Param1 is not Null)
begin
SELECT * FROM studentTable WHERE colname= @Param1
end

GO

Like this you can check for other parameters.

Try this and let me know.

kughan j replied to Jitendra Faye on 27-Nov-11 11:37 PM

Hi ,

Thanks for ur response.

As per your idea using not null condition is ok if I use one or two parameters.
But if I user four parameters then i need to use a probability of 16 not null condition
for four parameters which is a cumbersome task.

Hope u understand.
Thanks,
Kughan

Riley K replied to kughan j on 27-Nov-11 11:42 PM

There are several ways to do that, see this sample SProc

BEGIN

IF @forename IS NOT NULL

BEGIN

SET @sqlQuery = @ sqlQuery + ' AND forename LIKE ''' + @forename +'%''';

END

END

BEGIN

IF @surname IS NOT NULL

BEGIN

SET @ sqlQuery = sqlQuery + ' AND surname LIKE ''' + @surname +'%''';

END

END

Regards

Web Star replied to kughan j on 27-Nov-11 11:48 PM

You need to pass 4 parameter to sp and than generate search criteria with using AND operator in dynamic query as follows

CREATE PROCEDURE spName
(
  @Parm1 int = NULL, @Parm2 int = NULL, @Parm3 int = NULL, @Parm4 int = NULL )
AS
DECLARE @sqlQuery varchar(MAX)
DECLARE @sqlSearch varchar(2000)
--here you first make your condition as

if( @Param1 is not Null)
begin
SET @sqlSearch = ' AND COLNAME = ''' + @Param1 + ''''
end
if( @Param2 is not Null)
begin
SET @sqlSearch = ' AND COLNAME = ''' + @Param2 + ''''
end
if( @Param3 is not Null)
begin
SET @sqlSearch = ' AND COLNAME = ''' + @Param3 + ''''
end

if( @Param4 is not Null)
begin
SET @sqlSearch = ' AND COLNAME = ''' + @Param4 + ''''
end

SET @sqlQuery =' SELECT * FROM studentTable WHERE 1=1 '
SET @sqlQuery = @sqlQuery + @sqlSearch
-- run dynamic query as follows
exec sp_executesql @sqlQuery

end

GO

Jitendra Faye replied to kughan j on 27-Nov-11 11:48 PM

You can use Temp Table to complete this task , like this-

CREATE PROCEDURE SelectEmployees ( @Param1 int = NULL, @Param2 int = NULL)
AS

if( @Param1 is not Null)
begin
SELECT * into #temp1 FROM studentTable WHERE colname= @Param1
end
else if( @Param2 is not Null)
begin
SELECT * into #temp2 FROM #temp WHERE colname= @Param2
end

GO

Now you will get final result in temp2

Try this and let me know.

Suchit shah replied to kughan j on 28-Nov-11 12:13 AM

You can use the Dynamic condition like below SP sample

CREATE PROCEDURE search_orders_1 -- 1

@orderid int = NULL, -- 2

@fromdate datetime = NULL, -- 3

@todate datetime = NULL, -- 4

@minprice money = NULL, -- 5

@maxprice money = NULL, -- 6

@custid nchar(5) = NULL, -- 7

@custname nvarchar(40) = NULL, -- 8

@city nvarchar(15) = NULL, -- 9

@region nvarchar(15) = NULL, -- 10

@country nvarchar(15) = NULL, -- 11

@prodid int = NULL, -- 12

@prodname nvarchar(40) = NULL, -- 13

@debug bit = 0 AS -- 14

-- 15

DECLARE @sql nvarchar(4000), -- 16

@paramlist nvarchar(4000) -- 17

-- 18

SELECT @sql = -- 19

'SELECT o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity, -- 20

c.CustomerID, c.CompanyName, c.Address, c.City, -- 21

c.Region, c.PostalCode, c.Country, c.Phone, -- 22

p.ProductID, p.ProductName, p.UnitsInStock, -- 23

p.UnitsOnOrder -- 24

FROM dbo.Orders o -- 25

JOIN dbo.[Order Details] od ON o.OrderID = od.OrderID -- 26

JOIN dbo.Customers c ON o.CustomerID = c.CustomerID -- 27

JOIN dbo.Products p ON p.ProductID = od.ProductID -- 28

WHERE 1 = 1' -- 29

-- 30

IF @orderid IS NOT NULL -- 31

SELECT @sql = @sql + ' AND o.OrderID = @xorderid' + -- 32

' AND od.OrderID = @xorderid' -- 33

-- 34

IF @fromdate IS NOT NULL -- 35

SELECT @sql = @sql + ' AND o.OrderDate >= @xfromdate' -- 36

-- 37

IF @todate IS NOT NULL -- 38

SELECT @sql = @sql + ' AND o.OrderDate <= @xtodate' -- 39

-- 40

IF @minprice IS NOT NULL -- 41

SELECT @sql = @sql + ' AND od.UnitPrice >= @xminprice' -- 42

-- 43

IF @maxprice IS NOT NULL -- 44

SELECT @sql = @sql + ' AND od.UnitPrice <= @xmaxprice' -- 45

-- 46

IF @custid IS NOT NULL -- 47

SELECT @sql = @sql + ' AND o.CustomerID = @xcustid' + -- 48

' AND c.CustomerID = @xcustid' -- 49

-- 50

IF @custname IS NOT NULL -- 51

SELECT @sql = @sql + ' AND c.CompanyName LIKE @xcustname + ''%''' -- 52

-- 53

IF @city IS NOT NULL -- 54

SELECT @sql = @sql + ' AND c.City = @xcity' -- 55

-- 56

IF @region IS NOT NULL -- 57

SELECT @sql = @sql + ' AND c.Region = @xregion' -- 58

-- 59

IF @country IS NOT NULL -- 60

SELECT @sql = @sql + ' AND c.Country = @xcountry' -- 61

-- 62

IF @prodid IS NOT NULL -- 63

SELECT @sql = @sql + ' AND od.ProductID = @xprodid' + -- 64

' AND p.ProductID = @xprodid' -- 65

-- 66

IF @prodname IS NOT NULL -- 67

SELECT @sql = @sql + ' AND p.ProductName LIKE @xprodname + ''%''' -- 68

-- 69

SELECT @sql = @sql + ' ORDER BY o.OrderID' -- 70

-- 71

IF @debug = 1 -- 72

PRINT @sql -- 73

-- 74

SELECT @paramlist = '@xorderid int, -- 75

@xfromdate datetime, -- 76

@xtodate datetime, -- 77

@xminprice money, -- 78

@xmaxprice money, -- 79

@xcustid nchar(5), -- 80

@xcustname nvarchar(40), -- 81

@xcity nvarchar(15), -- 82

@xregion nvarchar(15), -- 83

@xcountry nvarchar(15), -- 84

@xprodid int, -- 85

@xprodname nvarchar(40)' -- 86

-- 87

EXEC sp_executesql @sql, @paramlist, -- 88

@orderid, @fromdate, @todate, @minprice, -- 89

@maxprice, @custid, @custname, @city, @region, -- 90

@country, @prodid, @prodname -- 91

Riley K replied to kughan j on 28-Nov-11 12:13 AM

The another effiecient way is to use COALESCE

The statement shown here creates a procedure that accepts the required parameters.

When a parameter value is not supplied it is set to NULL.

CREATE PROCEDURE ps_Customers_SELECT_NameCityCountry

@Cus_Name varchar(30) = NULL,

@Cus_City varchar(30) = NULL,

@Cus_Country varchar(30) =NULL

AS

SELECT Cus_Name,

Cus_City,

Cus_Country

FROM Customers

WHERE Cus_Name = COALESCE(@Cus_Name,Cus_Name) AND

Cus_City = COALESCE(@Cus_City,Cus_City) AND

Cus_Country = COALESCE(@Cus_Country,Cus_Country)

Regards