如何通过存储过程避免重复值到Sql Server中的表..?在下面的代码我试图通过输出参数显示Msg ... [英] How To Avoid Duplicate Value Into Table In Sql Server By Stored Procedure..? In Below Code I M Trying To Display That Msg By Output Parameter...
问题描述
ALTER PROCEDURE [dbo]。[免费复制]
@username varchar(20),
@pword nvarchar(20),
@empname varchar(20),
@email nvarchar (50),
@designation varchar(20),
@reportto varchar(20),
@Result varchar(50)输出
as
如果存在(从testduplicate中选择用户名= @Username的用户名)
开始
设置@Result ='已经有'
返回
end
插入testduplicate(用户名,pword,empname,email,names,reportto)值(@ username,@ pword,@ empname,@ email, @ designation,@ renportto)
set @ Result ='Success'
如果我在下面插入值错误即将来临。
错误:
消息 201 ,等级 16 ,状态 4 ,Procedure avoidduplicate,Line 0
过程或函数'avoidduplicate'期望参数'@Result ',这是不提供。
好像你没有正确编写你的SP,试试这种方式。
ALTER PROCEDURE [dbo]。[avoidduplicate]
@ username varchar ( 20 ),
@ pword nvarchar ( 20 ),
@ empname varchar ( 20 ),
@ email nvarchar ( 50 ),
@ designation varchar ( 20 ),
@ reportto varchar ( 20 ),
@ Result varchar (< span class =code-digit> 50 )输出
as
BEGIN
如果 存在(选择用户名来自 testduplicate 其中 username = @ Username )
开始
set @ Result = ' 已经'
返回
end
insert into testduplicate(username,pword,empname,email,指定,报告到)值( @ username , @ pword , @ empname , @ email , @ designation , @ reportto )
set @ Result = ' 成功'
END
ALTER PROCEDURE [dbo].[avoidduplicate]
@username varchar(20),
@pword nvarchar(20),
@empname varchar(20),
@email nvarchar(50),
@designation varchar(20),
@reportto varchar(20),
@Result varchar(50) output
as
if exists(Select username from testduplicate where username = @Username)
begin
set @Result = 'Already there'
return
end
insert into testduplicate(username,pword,empname,email,designation,reportto)values(@username,@pword,@empname,@email,@designation, @reportto )
set @Result= 'Success'
if im inserting values below error is coming.
ERROR:
Msg 201, Level 16, State 4, Procedure avoidduplicate, Line 0
Procedure or function 'avoidduplicate' expects parameter '@Result', which was not supplied.
Seems you have not correctly written your SP, try this way.
ALTER PROCEDURE [dbo].[avoidduplicate] @username varchar(20), @pword nvarchar(20), @empname varchar(20), @email nvarchar(50), @designation varchar(20), @reportto varchar(20), @Result varchar(50) output as BEGIN if exists(Select username from testduplicate where username = @Username) begin set @Result = 'Already there' return end insert into testduplicate(username,pword,empname,email,designation,reportto)values(@username,@pword,@empname,@email,@designation, @reportto ) set @Result= 'Success' END
这篇关于如何通过存储过程避免重复值到Sql Server中的表..?在下面的代码我试图通过输出参数显示Msg ...的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!