What is preferred method for searching table data using stored procedure?
- by Mourya
I have a customer table with Cust_Id, Name, City and search is based upon any or all of the above three.
Which one Should I go for ?
Dynamic SQL:
declare @str varchar(1000)
set @str = 'Select [Sno],[Cust_Id],[Name],[City],[Country],[State]
from Customer where 1 = 1'
if (@Cust_Id != '')
set @str = @str + ' and Cust_Id = ''' + @Cust_Id + ''''
if (@Name != '')
set @str = @str + ' and Name like ''' + @Name + '%'''
if (@City != '')
set @str = @str + ' and City like ''' + @City + '%'''
exec (@str)
Simple query:
select
[Sno],[Cust_Id],[Name],[City],[Country],[State]
from
Customer
where
(@Cust_Id = '' or Cust_Id = @Cust_Id) and
(@Name = '' or Name like @Name + '%') and
(@City = '' or City like @City + '%')
Which one should I prefer (1 or 2) and what are advantages?
After going through everyone's suggestion , here is what i finally got.
DECLARE @str NVARCHAR(1000)
DECLARE @ParametersDefinition NVARCHAR(500)
SET @ParametersDefinition = N'@InnerCust_Id varchar(10),
@InnerName varchar(30),@InnerCity varchar(30)'
SET @str = 'Select [Sno],[Cust_Id],[Name],[City],[Country],[State]
from Customer where 1 = 1'
IF(@Cust_Id != '')
SET @str = @str + ' and Cust_Id = @InnerCust_Id'
IF(@Name != '')
SET @str = @str + ' and Name like @InnerName'
IF(@City != '')
SET @str = @str + ' and City like @InnerCity'
-- ADD the % symbol for search based upon the LIKE keyword
SELECT @Name = @Name + '%', @City = @City+ '%'
EXEC sp_executesql @str, @ParametersDefinition,
@InnerCust_Id = @Cust_Id,
@InnerName = @Name,
@InnerCity = @City;
References :
http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/changing-exec-to-sp_executesql-doesn-t-p
http://msdn.microsoft.com/en-us/library/ms175170.aspx
