CREATE PRO PRONULL(@STR VARCHAR(20)) AS BEGIN IF @STR IS NULL BEGIN SELECT * FROM TB WHERE F1 IS NULL END ELSE BEGIN SELECT * FROM TB WHERE F1=@STR END END 用存储过程吧
CREATE PROC PRONULL(@STR VARCHAR(20)) AS BEGIN IF @STR IS NULL BEGIN SELECT * FROM TB WHERE F1 IS NULL END ELSE BEGIN SELECT * FROM TB WHERE F1=@STR END END
SELECT * FROM T WHERE F1 IS NULL or F1=@p
create function selectA(@F1 varchar(20))returns table as return( select * from A where F1=@F1--如果为null也可以。 )
create procedure PF1(@s varchar(10) null) as set nocount on select * from tb where F1=@sexec PF1 ----null--- exec PF1 '参数' -----输入数据--------
这个 简单:SELECT * FROM TB WHERE ((@F1=null) or (F1=@F1))
SELECT语句写为(假设@F1为函数参数名) SELECT * FROM [TableName] WHERE (@F1 IS NULL AND F1 IS NULL) OR (@F1 IS NOT NULL AND F1=@F1) 即可查找满足条件的记录
SELECT语句中,列名或变量名与空的比较一般用:为空IS NULL,非空IS NOT NULL
更好的SELECT写法可以是: SELECT * FROM [TableName] WHERE (@F1 IS NULL AND F1 IS NULL) OR (@F1 IS NOT NULL AND F1 IS NOT NULL AND F1=@F1)
decalre @str varchar(100),@i char(10) set @str='select * from tb where F1= ' +@i exec(@str) null exec(@str) '参数'
感觉生成过程比较好。 create proc te(@param 类型) as begin select * from usertable where F1 = isnull(@param,F1) end红色部分就可以满足要求。当参数为null时,条件就变成 F1 = F1是个永真的,相当于忽略了F1条件。
AS
BEGIN
IF @STR IS NULL
BEGIN
SELECT * FROM TB WHERE F1 IS NULL
END
ELSE
BEGIN
SELECT * FROM TB WHERE F1=@STR
END
END
用存储过程吧
AS
BEGIN
IF @STR IS NULL
BEGIN
SELECT * FROM TB WHERE F1 IS NULL
END
ELSE
BEGIN
SELECT * FROM TB WHERE F1=@STR
END
END
as
return(
select * from A where F1=@F1--如果为null也可以。
)
create procedure PF1(@s varchar(10) null)
as
set nocount on
select * from tb where F1=@sexec PF1 ----null---
exec PF1 '参数' -----输入数据--------
SELECT * FROM [TableName]
WHERE (@F1 IS NULL AND F1 IS NULL) OR (@F1 IS NOT NULL AND F1=@F1)
即可查找满足条件的记录
SELECT * FROM [TableName]
WHERE (@F1 IS NULL AND F1 IS NULL) OR (@F1 IS NOT NULL AND F1 IS NOT NULL AND F1=@F1)
decalre @str varchar(100),@i char(10)
set @str='select * from tb where F1= ' +@i
exec(@str) null
exec(@str) '参数'
create proc te(@param 类型)
as
begin
select * from usertable where F1 = isnull(@param,F1)
end红色部分就可以满足要求。当参数为null时,条件就变成 F1 = F1是个永真的,相当于忽略了F1条件。
很遗憾,如果F1为空,F1 = isnull(@param,F1),即使参数为空也无法查询F1为空的字段,好像只能用is null 来判断