从SQL中的存储过程返回多个值 [英] Return multiple values from a stored procedure in SQL

问题描述

 ALTER proc [dbo].[BelgeKayit]
(
@OgrenciNo int,
@BelgeId int,
@BelgeBaslik nvarchar(30),
@BelgeAciklama nvarchar(250)
)
as 
declare @OgrenciKayitKontrol int
declare @Durum int 
declare @BelgeKayitKontrol int
declare @Sonuc int
Set @OgrenciKayitKontrol=(select count(*) from Ogrenci where Ogrenci.OgrenciNo=@OgrenciNo)
if(@OgrenciKayitKontrol=1)
begin
insert into OgrenciBelge(OgrenciNo,BelgeId,BelgeBaslik,BelgeAciklama) values(@OgrenciNo,@BelgeId,@BelgeBaslik,@BelgeAciklama)
set @Durum=0
end
else
begin
set @Durum=1
end
Set @BelgeKayitKontrol=(select count(*) from OgrenciBelge where OgrenciBelge.OgrenciNo=@OgrenciNo and OgrenciBelge.BelgeId=@BelgeId)
if(@BelgeKayitKontrol=1)
begin
set @Sonuc=0
end
else
begin
set @Sonuc=1
end
return @Durum
return @Sonuc

但是sonuc为null我怎么能在sp中返回2个值？

but sonuc is null how can i do that 2 values returning in sp?

推荐答案

您不能以现在的方式从存储过程返回多个值。但是，您可以将参数指定为OUTPUT，以便可以访问它们。请参见此处 [ ^ ]以获取更多说明。

You can't return multiple values from stored procedure in the way you are doing it now. You can, however, specify your parameters to be OUTPUT so you can access them. See here[^] for additional explanation.

使用输出参数，或返回一个选择：

Use output parameters, or return a select:

ALTER PROC Testy(@ID int, @OUT1 nvarchar(100) OUTPUT, @OUT2 nvarchar(100) OUTPUT)
AS
SET @OUT1 = (Select Customer FROM Customers WHERE Id=@ID)
SET @OUT2 = (SELECT [TEXT] from myTable WHERE iD=@ID)
return @ID

或

or

ALTER PROC Testy(@ID int)
AS
DECLARE @OUT1 nvarchar(100)
DECLARE @OUT2 nvarchar(100)
SET @OUT1 = (Select Customer FROM Customers WHERE Id=@ID)
SET @OUT2 = (SELECT [TEXT] from myTable WHERE iD=@ID)
SELECT @OUT1, @OUT2

@Durum int output,
@Sonuc int output

然后在aspx页面

then in aspx page

sqlCmd.Parameters.Add("@Durum",SqlDbType.Int).Direction=ParameterDirection.Output;
DataTable dtTable=new datalayer().SelectSpData("Sp_Name",sqlCmd);
int Durum=Convert.ToInt32(sqlCmd.Parameters["@Durum"].value);

Sonuc的相同代码也是..

我认为这可能有助于你..

same code for Sonuc also..
I think this may help u..

这篇关于从SQL中的存储过程返回多个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！