Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Dynamic sql search Conditions

Author  Topic 

nehakapoor2010
Starting Member

7 Posts
Posted - 2010-03-03 : 05:47:33
Hi,

I am trying to develop a dynamic search conditions sql for my project based on http://www.sommarskog.se/dyn-search-2005.html. I need to develop for "like" condition and "=" condition for "like" condition, it is not working . for "=" condition it is working



My sql is given below.I am using sp_executesql



DECLARE @sql        nvarchar(4000),                                 
        @paramlist  nvarchar(4000)                                 
                                                                    
SELECT @sql =                                                       
    'SELECT o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity,     
            c.CustomerID, c.CompanyName, c.Address, c.City,         
            c.Region,  c.PostalCode, c.Country, c.Phone,            
            p.ProductID, p.ProductName, p.UnitsInStock,            
            p.UnitsOnOrder                                        
     FROM   dbo.Orders o                                            
     JOIN   dbo.[Order Details] od ON o.OrderID = od.OrderID       
     JOIN   dbo.Customers c ON o.CustomerID = c.CustomerID        
     WHERE  1 = 1'                                                  
                                                                    
IF @orderid <> 0                                            
   SELECT @sql = @sql + ' AND o.OrderID = @xorderid'               

IF @custid <>''                                              
   SELECT @sql = @sql + ' AND o.CustomerID like   @xcustid +  ''%'''




when i print the developed sql statement

SELECT o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity,      
            c.CustomerID, c.CompanyName, c.Address, c.City,       
            c.Region,  c.PostalCode, c.Country, c.Phone,            
            p.ProductID, p.ProductName, p.UnitsInStock,            
            p.UnitsOnOrder                                          
     FROM   dbo.Orders o                                            
     JOIN   dbo.[Order Details] od ON o.OrderID = od.OrderID        
     JOIN   dbo.Customers c ON o.CustomerID = c.CustomerID          
	JOIN   dbo.Products p ON p.ProductID = od.ProductID           
     WHERE  1 = 1 AND o.CustomerID like   @xcustid +  '%' ORDER BY o.OrderID



EXEC Modified_search_orders_1 @custid = 'ALF'

It is not returning any rows.

Could Some one please guide me on this.


Thanks

Neha

vaibhavktiwari83
Aged Yak Warrior

843 Posts
Posted - 2010-03-03 : 06:05:40
What is XcustID and custid as you are using custid outside and xcustid inside the query i m confused please explain

Vabhav T
Go to Top of Page

nehakapoor2010
Starting Member

7 Posts
Posted - 2010-03-03 : 06:11:36
@CustiD is parameter passing for the customer
I have modified the Sql.


DECLARE @sql        nvarchar(4000),                                 
        @paramlist  nvarchar(4000)                                 
                                                                    
SELECT @sql =                                                       
    'SELECT o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity,     
            c.CustomerID, c.CompanyName, c.Address, c.City,         
            c.Region,  c.PostalCode, c.Country, c.Phone,            
            p.ProductID, p.ProductName, p.UnitsInStock,            
            p.UnitsOnOrder                                        
     FROM   dbo.Orders o                                            
     JOIN   dbo.[Order Details] od ON o.OrderID = od.OrderID       
     JOIN   dbo.Customers c ON o.CustomerID = c.CustomerID        
     WHERE  1 = 1'                                                  
                                                                    
IF @orderid <> 0                                            
   SELECT @sql = @sql + ' AND o.OrderID = @xorderid'               

IF @custid <>''                                              
   SELECT @sql = @sql + ' AND o.CustomerID like   @custid +  ''%'''


when i print the developed sql statement


SELECT o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity,      
            c.CustomerID, c.CompanyName, c.Address, c.City,       
            c.Region,  c.PostalCode, c.Country, c.Phone,            
            p.ProductID, p.ProductName, p.UnitsInStock,            
            p.UnitsOnOrder                                          
     FROM   dbo.Orders o                                            
     JOIN   dbo.[Order Details] od ON o.OrderID = od.OrderID        
     JOIN   dbo.Customers c ON o.CustomerID = c.CustomerID          
	JOIN   dbo.Products p ON p.ProductID = od.ProductID           
     WHERE  1 = 1 AND o.CustomerID like   @custid +  '%' ORDER BY o.OrderID




EXEC Modified_search_orders_1 @custid = 'ALF'
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts
Posted - 2010-03-03 : 06:12:42
why not ditch the dynamic sql altogether and go for:

SELECT o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity,     
            c.CustomerID, c.CompanyName, c.Address, c.City,         
            c.Region,  c.PostalCode, c.Country, c.Phone,            
            p.ProductID, p.ProductName, p.UnitsInStock,            
            p.UnitsOnOrder                   
                     
 FROM   dbo.Orders o                                            
 JOIN   dbo.[Order Details] od ON o.OrderID = od.OrderID       
 JOIN   dbo.Customers c ON o.CustomerID = c.CustomerID 

WHERE
		(
		o.[OrderId] = @xorderID
		OR @xOrderID = 0
		)

	AND (
		o.[CustomerID] LIKE @xcustId + '%'
		OR @xcustId = ''
		)
ORDER BY
	o.[OrderId]



Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

vaibhavktiwari83
Aged Yak Warrior

843 Posts
Posted - 2010-03-03 : 06:18:02
quote:
Originally posted by nehakapoor2010

@CustiD is parameter passing for the customer
I have modified the Sql.


DECLARE @sql        nvarchar(4000),                                 
        @paramlist  nvarchar(4000)                                 
                                                                    
SELECT @sql =                                                       
    'SELECT o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity,     
            c.CustomerID, c.CompanyName, c.Address, c.City,         
            c.Region,  c.PostalCode, c.Country, c.Phone,            
            p.ProductID, p.ProductName, p.UnitsInStock,            
            p.UnitsOnOrder                                        
     FROM   dbo.Orders o                                            
     JOIN   dbo.[Order Details] od ON o.OrderID = od.OrderID       
     JOIN   dbo.Customers c ON o.CustomerID = c.CustomerID        
     WHERE  1 = 1'                                                  
                                                                    
IF @orderid <> 0                                            
   SELECT @sql = @sql + ' AND o.OrderID = @xorderid'               

IF @custid <>''                                              
   SELECT @sql = @sql + ' AND o.CustomerID like   @custid +  ''%'''


when i print the developed sql statement


SELECT o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity,      
            c.CustomerID, c.CompanyName, c.Address, c.City,       
            c.Region,  c.PostalCode, c.Country, c.Phone,            
            p.ProductID, p.ProductName, p.UnitsInStock,            
            p.UnitsOnOrder                                          
     FROM   dbo.Orders o                                            
     JOIN   dbo.[Order Details] od ON o.OrderID = od.OrderID        
     JOIN   dbo.Customers c ON o.CustomerID = c.CustomerID          
	JOIN   dbo.Products p ON p.ProductID = od.ProductID           
     WHERE  1 = 1 AND o.CustomerID like   @custid +  '%' ORDER BY o.OrderID




EXEC Modified_search_orders_1 @custid = 'ALF'



Rewrite the query like this and try to execute

DECLARE @sql nvarchar(4000),
@paramlist nvarchar(4000)
DECLARE @orderid INT
SET @orderid = 0

SELECT @sql =
'SELECT o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity,
c.CustomerID, c.CompanyName, c.Address, c.City,
c.Region, c.PostalCode, c.Country, c.Phone,
p.ProductID, p.ProductName, p.UnitsInStock,
p.UnitsOnOrder
FROM dbo.Orders o
JOIN dbo.[Order Details] od ON o.OrderID = od.OrderID
JOIN dbo.Customers c ON o.CustomerID = c.CustomerID
WHERE 1 = 1'

IF @orderid <> 0
SELECT @sql = @sql + ' AND o.OrderID = @xorderid'

IF @custid <>''
SELECT @sql = @sql + ' AND o.CustomerID like ''' + @custid + '%'''


Vabhav T
Go to Top of Page

nehakapoor2010
Starting Member

7 Posts
Posted - 2010-03-03 : 06:20:33
when I hardcoded 'ALF' directly in the printed sql, I am getting the result.but when i call SP,not getting result
SELECT o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity,
c.CustomerID, c.CompanyName, c.Address, c.City,
c.Region, c.PostalCode, c.Country, c.Phone,
p.ProductID, p.ProductName, p.UnitsInStock,
p.UnitsOnOrder
FROM dbo.Orders o
JOIN dbo.[Order Details] od ON o.OrderID = od.OrderID
JOIN dbo.Customers c ON o.CustomerID = c.CustomerID
JOIN dbo.Products p ON p.ProductID = od.ProductID
WHERE 1 = 1 AND o.CustomerID like 'ALF' + '%' ORDER BY o.OrderID
Go to Top of Page

vaibhavktiwari83
Aged Yak Warrior

843 Posts
Posted - 2010-03-03 : 06:34:26
Okey i got it

rewrite the query like the below.

ALTER Proc Modified_search_orders_1
( @custid varchar(10) )
AS
BEGIN

DECLARE @sql nvarchar(4000),
@paramlist nvarchar(4000)
DECLARE @orderid INT
SET @orderid = 0
DECLARE @ParamDefinitions AS NVARCHAR(MAX)
SET @ParamDefinitions= '@xCustID VARCHAR(10)'

SELECT @sql =
'SELECT o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity,
c.CustomerID, c.CompanyName, c.Address, c.City,
c.Region, c.PostalCode, c.Country, c.Phone,
p.ProductID, p.ProductName, p.UnitsInStock,
p.UnitsOnOrder
FROM dbo.Orders o
JOIN dbo.[Order Details] od ON o.OrderID = od.OrderID
JOIN dbo.Customers c ON o.CustomerID = c.CustomerID
WHERE 1 = 1'

IF @orderid <> 0
SELECT @sql = @sql + ' AND o.OrderID = @xorderid'

IF @custid <>''
SELECT @sql = @sql + ' AND o.CustomerID like @xcustid + ''%'''

PRINT(@sql)
EXECUTE SP_ExecuteSQL @sql, @ParamDefinitions, @xcustID = @custID

END

--EXEC Modified_search_orders_1 'AFL'

Vabhav T
Go to Top of Page

nehakapoor2010
Starting Member

7 Posts
Posted - 2010-03-03 : 06:56:35
I tried with the query,but i am not getting the result. It is the same query i have written..Any difference in the code. I am not able to identify?
Go to Top of Page

vaibhavktiwari83
Aged Yak Warrior

843 Posts
Posted - 2010-03-03 : 08:11:19
Can you post actual stored procedure script because i m getting the result

Vabhav T
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site