create proc p_test (@name varchar(10),@xh varchar(20),@rt int output) as begin if exists(select 1 from tb where name=@name and xh=@xh) set @rt=1 else set @rt=0 end
CREATE PROC p_checkstudent @sno VARCHAR(10)=NULL, @sname VARCHAR(10)=NULL AS SELECT 1 FROM students WHERE sno = ISNULL(@sno, sno) AND sname = ISNULL(@sname, sname) IF @@rowcount>0 PRINT '该学生存在' ELSE PRINT '该学生不存在' GO
CREATE PROC p_checkstudent @sno VARCHAR(10)=NULL, @sname VARCHAR(10)=NULL AS SELECT 1 FROM students WHERE sno = ISNULL(@sno, sno) AND sname = ISNULL(@sname, sname) IF @@rowcount>0 RETURN 1 ELSE RETURN 0 GO
declare @name varchar(10),@xh varchar(20),@rt int set @name='王刚' set @xh='201003001' exec p_test @name,@xh,@rt outputprint @rt
--> 生成测试数据表: [students] IF OBJECT_ID('[students]') IS NOT NULL DROP TABLE [students] GO CREATE TABLE [students] ([sno] [nvarchar](10),[sname] [nvarchar](10)) INSERT [students] SELECT '0001','张三' union all SELECT '0002','李四' GO -->SQL查询如下: -- --创建存储过程 IF OBJECT_ID('[p_checkstudent]') IS NOT NULL DROP PROC [p_checkstudent] GO CREATE PROC p_checkstudent @sno VARCHAR(10)=NULL, @sname VARCHAR(10)=NULL AS SELECT 1 FROM students WHERE sno = ISNULL(@sno, sno) AND sname = ISNULL(@sname, sname) IF @@rowcount>0 RETURN 1 ELSE RETURN 0 GO
--> 生成测试数据表: [students] IF OBJECT_ID('[students]') IS NOT NULL DROP TABLE [students] GO CREATE TABLE [students] ([sno] [nvarchar](10),[sname] [nvarchar](10)) INSERT [students] SELECT '0001','张三' union all SELECT '0002','李四' GO -->SQL查询如下: -- --创建存储过程 IF OBJECT_ID('[p_checkstudent]') IS NOT NULL DROP PROC [p_checkstudent] GO CREATE PROC p_checkstudent @sno VARCHAR(10)=NULL, @sname VARCHAR(10)=NULL AS SELECT 1 FROM students WHERE sno = ISNULL(@sno, sno) AND sname = ISNULL(@sname, sname) IF @@rowcount>0 RETURN 1 ELSE RETURN 0 GO --调用示例: --两个参数都输入 EXEC p_checkstudent '0001','张三' /* ----------- 1(1 行受影响) */ EXEC p_checkstudent '0003','张三' /* -----------(0 行受影响) */--输入其中一个参数EXEC p_checkstudent @sname='张三' /* ----------- 1(1 行受影响) */EXEC p_checkstudent @sno='0002' /* ----------- 1(1 行受影响) */
if object_id('std') is not null drop table stdcreate table std ( id varchar(20), name varchar(20) )insert into std select 'mg041251072','andy' union all select '041223454','jack' union all select 'mg324233422', 'lily'create proc checkstd @id varchar(20)=null, @name varchar(20)=null, @rtvalue bit output as if exists(select 1 from std where id=isnull(@id, id) and name=isnull(@name,name)) set @rtvalue=1 else set @rtvalue=0declare @isexist1 bit,@isexist2 bit, @isexist3 bit, @isexist4 bit exec checkstd 'mg041251072','andy',@isexist1 output exec checkstd 'mg041251072',null,@isexist2 output exec checkstd '041223456','jack',@isexist3 output exec checkstd null,'lily',@isexist4 output select @isexist1 as isexist1,@isexist2 as isexist2,@isexist3 as isexist3,@isexist4 as isexist4
if object_id('std') is not null drop table stdcreate table std ( id varchar(20), name varchar(20) )insert into std select 'mg041251072','andy' union all select '041223454','jack' union all select 'mg324233422', 'lily'create proc checkstd @id varchar(20)=null, @name varchar(20)=null, @rtvalue bit output as if exists(select 1 from std where id=isnull(@id, id) and name=isnull(@name,name)) set @rtvalue=1 else set @rtvalue=0declare @isexist1 bit,@isexist2 bit, @isexist3 bit, @isexist4 bit exec checkstd 'mg041251072','andy',@isexist1 output exec checkstd 'mg041251072',default,@isexist2 output exec checkstd '041223456','jack',@isexist3 output exec checkstd default,'lily',@isexist4 output select @isexist1 as isexist1,@isexist2 as isexist2,@isexist3 as isexist3,@isexist4 as isexist4 调用时我把null改成了default,不过没什么区别
as
begin
if exists(select 1 from tb where name=@name and xh=@xh)
set @rt=1
else
set @rt=0
end
@sno VARCHAR(10)=NULL,
@sname VARCHAR(10)=NULL
AS
SELECT 1
FROM students
WHERE sno = ISNULL(@sno, sno)
AND sname = ISNULL(@sname, sname)
IF @@rowcount>0
PRINT '该学生存在'
ELSE PRINT '该学生不存在'
GO
@sno VARCHAR(10)=NULL,
@sname VARCHAR(10)=NULL
AS
SELECT 1
FROM students
WHERE sno = ISNULL(@sno, sno)
AND sname = ISNULL(@sname, sname)
IF @@rowcount>0
RETURN 1
ELSE
RETURN 0
GO
set @name='王刚'
set @xh='201003001'
exec p_test @name,@xh,@rt outputprint @rt
IF OBJECT_ID('[students]') IS NOT NULL
DROP TABLE [students]
GO
CREATE TABLE [students] ([sno] [nvarchar](10),[sname] [nvarchar](10))
INSERT [students]
SELECT '0001','张三' union all
SELECT '0002','李四'
GO
-->SQL查询如下:
--
--创建存储过程
IF OBJECT_ID('[p_checkstudent]') IS NOT NULL
DROP PROC [p_checkstudent]
GO
CREATE PROC p_checkstudent
@sno VARCHAR(10)=NULL,
@sname VARCHAR(10)=NULL
AS
SELECT 1
FROM students
WHERE sno = ISNULL(@sno, sno)
AND sname = ISNULL(@sname, sname)
IF @@rowcount>0
RETURN 1
ELSE
RETURN 0
GO
IF OBJECT_ID('[students]') IS NOT NULL
DROP TABLE [students]
GO
CREATE TABLE [students] ([sno] [nvarchar](10),[sname] [nvarchar](10))
INSERT [students]
SELECT '0001','张三' union all
SELECT '0002','李四'
GO
-->SQL查询如下:
--
--创建存储过程
IF OBJECT_ID('[p_checkstudent]') IS NOT NULL
DROP PROC [p_checkstudent]
GO
CREATE PROC p_checkstudent
@sno VARCHAR(10)=NULL,
@sname VARCHAR(10)=NULL
AS
SELECT 1
FROM students
WHERE sno = ISNULL(@sno, sno)
AND sname = ISNULL(@sname, sname)
IF @@rowcount>0
RETURN 1
ELSE
RETURN 0
GO
--调用示例:
--两个参数都输入
EXEC p_checkstudent '0001','张三'
/*
-----------
1(1 行受影响)
*/
EXEC p_checkstudent '0003','张三'
/*
-----------(0 行受影响)
*/--输入其中一个参数EXEC p_checkstudent @sname='张三'
/*
-----------
1(1 行受影响)
*/EXEC p_checkstudent @sno='0002'
/*
-----------
1(1 行受影响)
*/
drop table stdcreate table std
(
id varchar(20),
name varchar(20)
)insert into std
select 'mg041251072','andy' union all
select '041223454','jack' union all
select 'mg324233422', 'lily'create proc checkstd @id varchar(20)=null, @name varchar(20)=null, @rtvalue bit output
as
if exists(select 1 from std where id=isnull(@id, id) and name=isnull(@name,name))
set @rtvalue=1
else set @rtvalue=0declare @isexist1 bit,@isexist2 bit, @isexist3 bit, @isexist4 bit
exec checkstd 'mg041251072','andy',@isexist1 output
exec checkstd 'mg041251072',null,@isexist2 output
exec checkstd '041223456','jack',@isexist3 output
exec checkstd null,'lily',@isexist4 output
select @isexist1 as isexist1,@isexist2 as isexist2,@isexist3 as isexist3,@isexist4 as isexist4
/*
isexist1 isexist2 isexist3 isexist4
-------- -------- -------- --------
1 1 0 1
*/
if object_id('std') is not null
drop table stdcreate table std
(
id varchar(20),
name varchar(20)
)insert into std
select 'mg041251072','andy' union all
select '041223454','jack' union all
select 'mg324233422', 'lily'create proc checkstd @id varchar(20)=null, @name varchar(20)=null, @rtvalue bit output
as
if exists(select 1 from std where id=isnull(@id, id) and name=isnull(@name,name))
set @rtvalue=1
else set @rtvalue=0declare @isexist1 bit,@isexist2 bit, @isexist3 bit, @isexist4 bit
exec checkstd 'mg041251072','andy',@isexist1 output
exec checkstd 'mg041251072',default,@isexist2 output
exec checkstd '041223456','jack',@isexist3 output
exec checkstd default,'lily',@isexist4 output
select @isexist1 as isexist1,@isexist2 as isexist2,@isexist3 as isexist3,@isexist4 as isexist4
调用时我把null改成了default,不过没什么区别