存储过程返回值 [英] Stored Procedure Return Value
问题描述
我有桌子名称：国家
我需要存储过程进行插入和更新，检查已存在国家名称。
I am Having Table Name : Country
I need Stored Procedure for insertion and Updation,check already country name exist.
create PROCEDURE Country
(
@Id1 int,
@name varchar(50),
@checkCondType varchar(10), ---Country
@tbCond varchar(15)
)
AS
BEGIN
if @checkCondType='Country'
BEGIN
if @tbCond='Insert'
BEGIN
if not exists(select * from Country where CoName=@name)
BEGIN
if exists(select * from Country)
BEGIN
select @AutoId=MAX(CoId)+1 from Country
END
else
BEGIN
set @AutoId=1
END
END
ELSE
BEGIN
insert into Country
(
CoId,CoName
)
VALUES
(
@AutoId,@name
)
END
END
ELSE IF @tbCond='Update'
BEGIN
update Country
set
CoName=@name where CoId=@Id1
END
END
来自此存储过程，我需要将值返回到C＃page.If已经存在国家名称存在返回一些值，如果插入发生返回一些值，如果更新发生返回一些值
From this Stored Procedure , I need to Return Values to C# page.If Already Country Name Exist return some value,if insertion happen return some value,if updation happen return some value
推荐答案
您可以将一个变量定义为OUTPUT，如下所示：
You can define one of your variables as an OUTPUT like so:
@retvalue bit OUTPUT
像任何其他变量一样设置值，当SPROC完成时它将被返回。
我不保证这个的语法（我复制了您粘贴的代码，并按照您在评论中的要求添加了我的建议）：
Set the value like you would any other variable and it''ll get returned when the SPROC completes.
I''m not guaranteeing the syntax of this (I copied your pasted code and am adding my suggestion as you requested in a comment):
create PROCEDURE Country
(
@Id1 int,
@name varchar(50),
@checkCondType varchar(10), ---Country
@tbCond varchar(15),
@retval varchar(50) OUTPUT
)
AS
BEGIN
if @checkCondType='Country'
BEGIN
if @tbCond='Insert'
BEGIN
if not exists(select * from Country where CoName=@name)
BEGIN
if exists(select * from Country)
BEGIN
select @AutoId=MAX(CoId)+1 from Country
END
else
BEGIN
set @AutoId=1
END
END
ELSE
BEGIN
insert into Country
(
CoId,CoName
)
VALUES
(
@AutoId,@name
)
SET @retval = 'insert'
END
END
ELSE IF @tbCond='Update'
BEGIN
update Country
set
CoName=@name where CoId=@Id1
SET @retval = 'update'
END
END
类似的东西......
Something like that...
这是一种方法。我已经修改了你的存储过程，注释表明我已经完成了什么。
Here is an approach. I have modified your stored procedure with the comments indicating what i have done.
create PROCEDURE Country
(
@Id1 int,
@name varchar(50),
@checkCondType varchar(10), ---Country
@tbCond varchar(15)
)
AS
BEGIN
--Declare a variable to identify if its insert or update operation
DECLARE @IsInsert BIT
if @checkCondType=''Country''
BEGIN
if @tbCond=''Insert''
BEGIN
if not exists(select * from Country where CoName=@name)
BEGIN
if exists(select * from Country)
BEGIN
select @AutoId=MAX(CoId)+1 from Country
END
else
BEGIN
set @AutoId=1
END
END
ELSE
BEGIN
insert into Country
(
CoId,CoName
)
VALUES
(
@AutoId,@name
)
END
--Set @IsInsert to true as its an insert
SET @IsInsert = 1
END
ELSE IF @tbCond=''Update''
BEGIN
update Country
set
CoName=@name where CoId=@Id1
--Set @IsInsert to false as its an Update
SET @IsInsert = 0
END
--RETURN THE @IsInsert value
SELECT @IsInsert AS OperationType
END
然后您可以使用 SqlCommand.ExecuteScalar Method [ ^ ]从应用程序中的存储过程中检索值。
我还注意到您手动递增Id值，然后将新值插入CoId列。您可以使用身份 [ ^ ] SQL Server中将自动生成的属性将数据插入行时的连续数字。使用Identity始终是一个更好的选择。
同样查看存储过程，看起来您正在if块中递增CoId并将数据插入到else块中的表中。我认为可能无法插入数据。我可能错了，但只是想到会让你知道。
You can then use SqlCommand.ExecuteScalar Method[^] to retrieve the value from the stored procedure in your application.
I also noticed that you are manually incrementing the Id value and then inserting the new value into your CoId column. You can use Identity[^] property in SQL server that will automatically generate consecutive numbers as you insert data into your rows. Using Identity is always a better option.
Also looking at your stored procedure it looks like you are incrementing the CoId in the if block and inserting the data into your table in the else block. I think the data may not get inserted. I might be wrong, but just thought will let you know.
这篇关于存储过程返回值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
