dynamic sql doubt in sql server

Question

0.00/5 (No votes)

See more:

SQL

--if no parameter is passed the sp should return all the result. 
--if i pass any one parameter then value should display as per the parameter
--i tried this. i think i am doing correct but if i execute 
--SearchEmployee '','','',''
--i get error 

select * from employee where  Name=
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '='.

--why i get this error ...as i understand all the parameter are '' . so why i am getting like this select * from employee where  and Gender=

=======================================stored procedure======================================

SQL

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) or @Name <> '')
begin
set @sqlwhere=' Name='+ @Name 
end

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

 
else if ((@Email is not null) or @Email <> '')
begin
set @sqlwhere=' and email='+ @Email
end

 

else if ((@Gender is not null) or @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	anu@gmail.com
abhi	22	Male	abhi@gmail.com
ruchi	23	Female	ruchi@gmail.com
siba	24	Male	siba@gmail.com
mukua	24	Male	mukua@gmail.com
prachi	24	Female	prachi@gmail.com
preeti	24	Female	preeti@gmail.com


--executing 
SearchEmployee '','','',''

this should give me all the result.

--executing the below should give me all the employees who are 24 years
SearchEmployee '','24','',''

----------------as suggested i changed from or to and and tried again-----------

SearchEmployee '','','','' --this gave me all the result

but when i tried the below

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

i got error

SQL

select * from employee where  Name=anurag
Msg 207, Level 16, State 1, Line 1
Invalid column name 'anurag'.

Posted 3-Oct-13 10:51am

anurag19289

Updated 4-Oct-13 7:32am

v8

Add a Solution

Comments

mgoad99 3-Oct-13 17:41pm

hmmm... i think you want and, not or
if ((@Name is not null) or @Name <> '')

anurag19289 4-Oct-13 13:23pm

ok i replaced or by and.
SearchEmployee '','','',''
now the above execution give me all the result.

but when i try

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

i get
select * from employee where Name=anurag
Msg 207, Level 16, State 1, Line 1
Invalid column name 'anurag'.

2 solutions

Solution 1

Essentially do this

SQL

SELECT * 
FROM Employee
WHERE Email = @Email OR COALESCE(@Email, '') = ''

If no email is passed in then the or will take care of it.

Posted 3-Oct-13 15:58pm

ZurdoDev

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

anurag19289 · Accepted Answer · 2013-10-04T11:15:00

SQL

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)

--second solution using dynamic sql

SQL

--exec SearchEmployee @Name='anurag',@Age=23 
--exec SearchEmployee @Age=23 
alter proc SearchEmployee-- '','','',''
@Name varchar(50)=null,
@Age int=null
as
begin
 
declare @sql varchar(max),@sqlwhere varchar(max)
  
set @sql='select * from employee' 
 
if ((@Name is not null) and @Name <> '')
begin
set @sqlwhere=' Name='+ '''' + @Name + ''''
print 'sqlwhere '
end
 
 if ((@Age is not null) and @Age <> '')
begin
 print 'i m in age block '
    set @sqlwhere =isnull(@sqlwhere + ' and ', '') + ' Age = ''' + cast(@Age as varchar(50)) + ''' ';
print 'sqlwhere ' + cast(@sqlwhere as varchar(50))
end
 

 
if (@sqlwhere is not null)
begin
set @sql=@sql+' where ' + @sqlwhere;
end
else
begin
set @sql=@sql;
end
 
print @sql
exec (@sql) 
 
end