How to check for different condition in Sql Server stored procedure?

Question

1.00/5 (1 vote)

See more:

I'm not able execute my stored procedure which is

SQL

@Name Nvarchar(250),
@Country Nvarchar(250),
@State Nvarchar(250),
@Speciality Nvarchar(250),
@Email Nvarchar(250),
@Grade Nvarchar(250)

as

BEGIN

DECLARE @sql AS NVarchar(4000)

    SET @sql = 'SELECT * FROM Doctorslist where (1=1)'
    
    if @Name is not null
    set @sql = @sql + ' And (DoctorName = @Name)'
    
    if @Country is not null
    set @sql = @sql+ ' And (Country = @Country)'
        
    if @State is not null
    set @sql = @sql + ' And (State = @State)'
        
    if @Speciality is not null
    set @sql = @sql + ' And (Speciality = @Speciality)'
 
EXEC(@sql)
END

While executing this stored procedure i'm getting error like this

SQL

Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@Country".

(1 row(s) affected)

So can anybody help me to solve this problem?

Posted 6-Aug-13 20:13pm

Aboobakkar Siddeq D U

Add a Solution

Comments

Ankur^\m/ 7-Aug-13 2:22am

Before EXEC(@sql), add print @sql and see the query that is generated. Try to execute that query in the query analyzer and see what's wrong.

4 solutions

Solution 2

Look at the code where you execute the SP: you have either not set the @Country parameter, or you have misspelled it.

Posted 6-Aug-13 20:21pm

OriginalGriff

Comments

Maciej Los 7-Aug-13 2:30am

I suppose that is a Store Procedure and variables @Country, @Name, @State, etc. are input parameters.
Cheers!
Maciej

Solution 4

Thanks to all Actually I solved this problem by adding like this

SQL

@DoctorName varchar(255),
@Degree varchar(255),
@Speciality varchar(255),
@TelNumber varchar(255),
@District varchar(255),
@State varchar(255),
@Country varchar(255),
@Email varchar(255)

AS

BEGIN

DECLARE @sql AS NVarchar(4000)

    SET @sql = 'select DoctorName,Degree,Speciality,TelNumber,Taluk,District,State,Country from Doctorslist where (1=1)'
    
    if @DoctorName is not null
    set @sql = @sql + ' And DoctorName =''' + @DoctorName + ''''
    
    if @Degree is not null
    set @sql = @sql + ' And Degree =''' + @Degree + ''''
    
    if @Speciality is not null
    set @sql = @sql + ' And Speciality =''' + @Speciality + ''''
    
    if @TelNumber is not null
    set @sql = @sql + ' And TelNumber =''' + @TelNumber + ''''
    
    if @District is not null
    set @sql = @sql + ' And District =''' + @District + ''''
        
    if @State is not null
    set @sql = @sql + ' And State =''' + @State + ''''
    
    if @Country is not null
    set @sql = @sql + ' And Country =''' + @Country + ''''
        
    if @Email is not null
    set @sql = @sql + ' And Email =''' + @Email + ''''
 
EXEC(@sql)

END

Mr. Maciej Los is helped me to do this changes.

Posted 6-Aug-13 20:39pm

Aboobakkar Siddeq D U

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)

Maciej Los · Accepted Answer · 2013-08-06T20:20:00

Solution 1

Replace:

SQL

set @sql = @sql + ' And (DoctorName = @Name)'

with:

SQL

set @sql = @sql + ' And (DoctorName =''' +  @Name + ''')'

Do the same in next lines.

Posted 6-Aug-13 20:20pm

Maciej Los

Comments

Ankur^\m/ 7-Aug-13 6:48am

Correct, 5!

Maciej Los 7-Aug-13 6:55am

Thank you ;)

ArunRajendra · Accepted Answer · 2013-08-06T20:33:00

Try with modified code

SQL

SET @sql = 'SELECT * FROM Doctorslist where (1=1)'

    if @Name is not null
    set @sql = @sql + ' And (DoctorName ='''+ @Name+''')'

    if @Country is not null
    set @sql = @sql+ ' And (Country = '''+@Country +''')'

    if @State is not null
    set @sql = @sql + ' And (State ='''+ @State+''')'

    if @Speciality is not null
    set @sql = @sql + ' And (Speciality ='''+ @Speciality+''')'

How to check for different condition in Sql Server stored procedure?

4 solutions

Solution 1

Solution 3

Solution 2

Solution 4

Add your solution here

Preview 0

Existing Members

...or Join us