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