如何在Sql Server存储过程中检查不同的条件？ [英] How to check for different condition in Sql Server stored procedure?

问题描述

我无法执行我的存储过程

I'm not able execute my stored procedure which is

@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

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

(1 row(s) affected)

那么有人可以帮我解决这个问题吗？

推荐答案

替换：

Replace:

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

with：

with:

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

在下一行中做同样的事。

Do the same in next lines.

尝试修改代码

Try with modified code

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+''')'

查看代码你执行SP的地方：你没有设置@Country参数，或者拼错了它。

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

这篇关于如何在Sql Server存储过程中检查不同的条件？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！