用于基于多个输入返回值的存储过程

This is my stored procedure to return a value based on multiple inputs.But it is not working.

这是我的存储过程,用于根据多个输入返回值。但它不起作用。

USE [E_clinic]
GO
ALTER PROCEDURE  [dbo].[Diseasesp1]

    @id1 nchar(10),
    @id2 nchar(10),
    @id3 nchar(10)

AS

BEGIN
 SET NOCOUNT ON;


    set @id1=@id1+1
    set @id2=@id2+1
    set @id3=@id3+1

    SELECT [Dname]
    From [dbo].[Disease] AS D
    Left Join [dbo].[Symptom] AS S ON D.[DiseaseID] = S.[DiseaseID]
    Where [SymptomID] = @id1 AND [SymptomID] = @id2 AND [SymptomID] = @id3
END

3 个解决方案

#1

Consider changing your procedure like

考虑改变您的程序

ALTER PROCEDURE  [dbo].[Diseasesp1]

    @id1 int,
    @id2 int,
    @id3 int

AS

BEGIN
 SET NOCOUNT ON;


    set @id1=@id1+1
    set @id2=@id2+1
    set @id3=@id3+1

    SELECT [Dname]
    From [dbo].[Disease] AS D
    Left Join [dbo].[Symptom] AS S ON D.[DiseaseID] = S.[DiseaseID]
    AND s.[SymptomID] = @id1 
    Left Join [dbo].[Symptom] AS S1 ON D.[DiseaseID] = S1.[DiseaseID]
    AND s1.[SymptomID] = @id2 
    Left Join [dbo].[Symptom] AS S2 ON D.[DiseaseID] = S2.[DiseaseID]
    AND s2.[SymptomID] = @id3;
END

#2

You could simply use IN for this.

您可以简单地使用IN。

Where [SymptomID] in (@id1, @id2, @id3)

But I would be significantly concerned that you receive parameters as character data and then do math with them. This is just a bad approach concerning datatypes. What happens when you receive 'a'?

但我会非常担心你接收参数作为字符数据然后用它们做数学。这只是一种关于数据类型的错误方法。收到'a'后会发生什么?

#3

-1

You need to change following things in your stored procedure. 1.@id1,@id2 and @id3 datatypes are nchar(10) but you are try to perform the addition operation with integer(@id1=@id1+1). This is not possible so please change the datatype of @id. 2.In that select statement, where clause you have used the same column with AND logical operator. This is not a correct way to write a SQL select with join query. please use correct where clause with alias name and use correct logical operator and then try..

您需要更改存储过程中的以下内容。 1. @ id1,@ id2和@id3数据类型是nchar(10)但是您尝试使用整数执行加法运算(@ id1 = @ id1 + 1)。这是不可能的,所以请更改@id的数据类型。 2.在select语句中,where子句使用了与AND逻辑运算符相同的列。这不是使用连接查询编写SQL选择的正确方法。请使用带别名的正确where子句并使用正确的逻辑运算符,然后尝试..

#1