CODEWITHSUNDEEP
sqlsql-server-2008
anurag
anurag

Reputation: 39

dynamic sql in stored procedure unable to fetch the required result

If no parameter is passed to the stored procedure, it should return all the result.

If I pass any one parameter then value should display as per the parameter

alter proc SearchEmployee --'','','',''
    @Name varchar(50)=null,
    @Age int=null,
    @Gender varchar(50)=null,
    @Email varchar(50)=null
as
begin

    declare @sql varchar(max), @sqlwhere varchar(max)
    set @sqlwhere = ''; 

    set @sql = 'select * from employee' 

   if ((@Name is not null) and @Name <> '')
   begin
      set @sqlwhere=' Name='+ @Name 
   end
   else if ((@Age is not null) and @Age <> '')
   begin
      set @sqlwhere='and Age='+ cast(@Age as varchar(50)) 
   end
   else if ((@Email is not null) and @Email <> '')
   begin
      set @sqlwhere=' and email='+ @Email
   end
   else if ((@Gender is not null) and @Gender <> '')
   begin
       set @sqlwhere=' and Gender='+ @Gender
   end

   if (@sqlwhere <> '')
   begin
       set @sql = @sql + ' where ' + @sqlwhere;
   end
   else
   begin
        set @sql = @sql;
   end

   print @sql
   exec (@sql) 
end

employee table

Name    Age Gender  email
anurag  24  Male    [email protected]
abhi    22  Male    [email protected]
ruchi   23  Female  [email protected]
siba    24  Male    [email protected]
mukua   24  Male    [email protected]
prachi  24  Female  [email protected]
preeti  24  Female  [email protected]

Executing 

SearchEmployee '','','',''

gives me all the results.

But executing the below gives error

SearchEmployee 'anurag','','',''`

select * from employee where  Name=anurag

Error:

Msg 207, Level 16, State 1, Line 1
Invalid column name 'anurag'.

Kindly help me modify the query. And where i am doing wrong ?

Upvotes: 3

Views: 713

Answers (2)

pkuderov
pkuderov

Reputation: 3571

You forgot about wrapping varchar/nvarchar values with single quotes.
Also if you'll not provide @Name your dynamic query will have e.g. where and Age = ... part, i.e. will have unwanted and before first filter condition.
Even more, in your implementation there can be only one filter condition (zero or one) because of if else if chain. But it seems you want use all passing (i.e. not null) parameters.

By the way, there's no need to use dynamic query. For example you can use this query:

select 
    * 
from employee
where
    Name = isnull(@Name, Name)
    and Age = isnull(@Age, Age)
    and email = isnull(@Email, email)
    and Gender = isnull(@Gender, Gender)

or

select 
    * 
from employee
where
    (@Name is null or Name = @Name)
    and (@Age is null or Age = @Age)
    and (@Email is null or email = @Email)
    and (@Gender is null or Gender = @Gender)

UPD. Answer to question in comments. Yes, it can be done. And again by isnull (or coalesce) function:

-- remove set @sqlwhere = '' in top of the query so @sqlwhere 
-- will be null - it's usefull. 
-- also change if (@sqlwhere <> '') to if (@sqlwhere is not null)

set @sqlwhere = isnull(@sqlwhere + ' and ', '') 
                + ' Age = ''' + cast(@Age as varchar(50)) + ''' ';

-- as you see, I also added single quotes wrapping for @Age value

isnull has simple logic - if first parameter is not null then return it else return second parameter. Also there's a rule in sql that if null value appears in arithmetic operation or in string concatenation (our case) then all result will be null. So if @sqlwhere is null then @sqlwhere + ' and ' also will be null. It means if there's no conditions in @sqlwhere then there's no need in and word.
Hope it's clear :)

Upvotes: 2

logixologist
logixologist

Reputation: 3834

The best way to debug this type of Dynamic SQL is to simply remove the exec (@sql) and just print the SQL Statement. You can then use that statement to work with the query until its right and then make the adjustments in the code based on the changes you made.

In this case you would find that you are missing apostrophes around the parameter. Try something like this:

set @sqlwhere=' Name='+ '''' + @Name + ''''

Do that for any parameter that is passing in text

Upvotes: 1

Related Questions