下面是存储过程:
CREATE PROCEDURE proc_addUser
@name varchar(50)='',
@age int=0,
@sex bit=0,
@address varchar(100)
AS
BEGIN
declare @u_error int
set @u_error=0
insert into dv_user values(@name,@age,@sex,@address)
set @u_error=@u_error+@@error
IF @u_error>0
BEGIN
PRINT '添加信息时出现错误'
END ELSE
BEGIN
PRINT '添加成功!'
END
END普通调用是:EXEC proc_addUser '小强',25,1,'黑龙江省哈尔滨'现在我要调用这个存储过程,但是我不想给第二个参数,请问如何写?
CREATE PROCEDURE proc_addUser
@name varchar(50)='',
@age int=0,
@sex bit=0,
@address varchar(100)
AS
BEGIN
declare @u_error int
set @u_error=0
insert into dv_user values(@name,@age,@sex,@address)
set @u_error=@u_error+@@error
IF @u_error>0
BEGIN
PRINT '添加信息时出现错误'
END ELSE
BEGIN
PRINT '添加成功!'
END
END普通调用是:EXEC proc_addUser '小强',25,1,'黑龙江省哈尔滨'现在我要调用这个存储过程,但是我不想给第二个参数,请问如何写?
set @u_error=@u_error+@@error
--
看你写的存储过程中,已经对age 设置了默认值了,你可以直接这样调用
EXEC proc_addUser @name ='小强',@sex = 1,@address ='黑龙江省哈尔滨'
ALTER PROCEDURE proc_addUser
@name varchar(50)='',
@sex bit=0,
@address varchar(100),
@age int=0
AS
BEGIN
declare @u_error int
set @u_error=0
insert into dv_user values(@name,@age,@sex,@address)
set @u_error=@u_error+@@error
IF @u_error>0
BEGIN
PRINT '添加信息时出现错误'
END ELSE
BEGIN
PRINT '添加成功!'
END
ENDEXEC proc_addUser '小强',1,'黑龙江省哈尔滨'
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE xtype='P' and [NAME]='proc_addUser')
drop procedure proc_addUser
go
CREATE PROCEDURE proc_addUser
@name varchar(50)='',
@age int=0,
@sex bit=0,
@address varchar(100)
AS
BEGIN
if @name is null
begin
print '名字为空'
set @name=''
end
if (@age is null)
begin
print '@age空'
set @age=18
end
if (@sex is null) or (@sex<>0 and @sex<>1)
begin
print '您输入的sex性别不正确,程序将默认输入0'
set @sex=0
end
declare @u_error int
set @u_error=0
insert into dv_user values(@name,@age,@sex,@address)
set @u_error=@u_error+@@error
IF @u_error>0
BEGIN
PRINT '添加信息时出现错误'
END ELSE
BEGIN
PRINT '添加成功!'
END
END
go