CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlsearch
Ram Mourya
Ram Mourya

Reputation: 2548

What is preferred method for searching table data using stored procedure?

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 ?

  1. 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)

  2. 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;

Note : @Cust_Id, @Name and @City are parameters being passed to the stored procedure

References : http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/changing-exec-to-sp_executesql-doesn-t-p

http://www.sommarskog.se/dynamic_sql.html

http://msdn.microsoft.com/en-us/library/ms175170.aspx

Upvotes: 1

Views: 474

Answers (3)

HLGEM
HLGEM

Reputation: 96648

Dynamic SQL is likley to be more performant which is generally important in a search.

However, it is more diffiult to write and debug and test. First you need to make sure it will not allow SQL injection attacks. Next you need to make sure that the variables you use are large enough to contain the largest possible final SQl statement you would create.

Then you need to create a good number of test cases to make sure that there is not some sort of subtle bug.

You will also need to grant read permissions to the underlying tables which you normally don't need to do if you use Stored procs.

Finally when doing dynamic SQL in a stored proc, please add an input variable called @debug as the last input variable and give it a default value of 0. When a 1 is passed in, instead of executing the dynamic SQL, it will send you the SQL that is created. This will help you debug the proc and and is especially helpful when there is a an error in some future search because you can see exactly what SQL was run for those values.

Upvotes: 4

George Mastros
George Mastros

Reputation: 24506

Dynamic SQL can be a little more difficult to write, and it is vulnerable to SQL Injection if you are not careful. However, it outperforms the "non-dynamic"/Simple or query.

Read more about it here. http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/do-you-use-column-param-or-param-is-null

Upvotes: 4

abatishchev
abatishchev

Reputation: 100366

From my experience, Dynamic SQL makes sense (gains performance) only of decreases the number of JOINs.

Otherwise it only worsen code readability and maintainability.

Upvotes: 1

Related Questions