怎么写不定项参数的存储过程

Question

一个表结构如下:
ID
名字
工资
输入条件:
1.名字
2.工资上限
3.工资下限名字为模糊查询.3个条件可以只输入一个,也可以输入2个或3个.甚至不输入.不输入就列出全部.
这个存储过程要怎么写好.
因为安全问题,不能使用拼SQL的方法.

Answer 1

如果不能使用动态查询，可以采用如下方式：create table #t(ID int,名字 varchar(100),工资 int)declare @id int,@名字 varchar(100),@工资 intselect * 
from #t
where 1=1 
    and ID=isnull(@id, id)
    and 名字=isnull(@名字, 名字)
    and 工资=isnull(@工资, 工资)drop table #t

Answer 2

--类似这样create procedure sp_test (@a varchar(100)='',@b varchar(100)='',@c varchar(100)='')
asdeclare @sql varchar(8000)
select @sql=''
if @a<>''
select @sql=@sql+@a+' and '
if @b<>''
select @sql=@sql+@b+' and '
if @c<>''
select @sql=@sql+@c+' and '
select @sql=reverse(stuff(reverse(@sql),1,5,''))
if len(@sql)<>0
select @sql='select * from tablename where '+@sql
else
select @sql='select * from tablename'
exec (@sql)

Answer 3

try:=======
create proc sp_serch @name varchar(20) ,
             @gzsx numeric(10,2), 
                     @gzxx numeric(10,2)
as 
begin 
declare      @name1 varchar(20) 
        declare      @gzsx1 numeric(10,2) 
                declare      @gzxx1 numeric(10,2)                select @name1 = isnull(@name,'%')
                select @gzsx1 = isnull(@name,99999999999.99)
                select @gzxx1 = isnull(@name,0.00) select * from tab
where name like @name 
and   gz between @gzxx1 and @gzsx1
end

Answer 4

楼上已经明确说明：因为安全问题,不能使用拼SQL的方法。使用这种方式代码最简单：select * 
from 表名
where 1=1 
    and ID=isnull(@id, id)
    and 名字=isnull(@名字, 名字)
    and 工资=isnull(@工资, 工资)当然，也可以使用if else逐个判断条件，但是代码量较大。

Answer 5

--这样就好了.--  创建测试表
create table 表(名字 varchar(50),工资 int)
Go
insert 表 select 'a',100
insert 表 select 'abc',500
insert 表 select 'dsc',200
insert 表 select 'e',800
Go
--  创建存储过程
create proc dbo._QuerySalary
        @Name nvarchar(50)=null,
        @Start int=null,
        @End int=null
as
set nocount onif @Name is null
        set @Name='%'
else
        set @Name='%'+@Name+'%'if @Start is null
        select @Start=min(工资) from 表
if @End is null
        select @End=max(工资) from 表--  返回结果
select *from 表where 名字 like @Name and 工资 between @Start and @EndGo
--执行存储过程exec dbo._QuerySalary 'a',200

Answer 6

--改一下declare @名字 varchar(100),@工资上限 int,@工资下限 intselect * 
from 表名
where 1=1 
    and 名字=isnull(@名字,名字)
    and 工资<=isnull(@工资上限,工资+1 )
    and 工资>=isnull(@工资下限, 工资-1)

Answer 7

--借用楼上数据
--  创建测试表
create table 表名(名字 varchar(50),工资 int)
Go
insert 表名 select 'a',100
insert 表名 select 'abc',500
insert 表名 select 'dsc',200
insert 表名 select 'e',800
Go
--  创建存储过程
create proc dbo._QuerySalary
        @名字 nvarchar(50)=null,
        @工资上限 int=null,
        @工资下限 int=null
as
select * 
from 表名
where 1=1 
    and 名字=isnull(@名字,名字)
    and 工资<=isnull(@工资上限,工资+1 )
    and 工资>=isnull(@工资下限, 工资-1)Go
--执行存储过程exec dbo._QuerySalary 'a',200
drop table 表名drop proc _QuerySalary

Answer 8

--如果名字用like：select * 
from 表名
where 名字 like isnull(@名字,'%')
    and 工资<=isnull(@工资上限,工资+1 )
    and 工资>=isnull(@工资下限, 工资-1)

Answer 9

拼SQL就不安全?
你写的ISNULL那段跟夜游神的有什么区别?如果调用存储过程之前不对参数做处理一样不安全.

Answer 10

尽量少使用动态SQL，使用动态SQL容易被注入。

Answer 11

create procedure p_test(@name varchar(10)=null,@pay1 int=null,@pay2 int=null)
as
select * from 表名 
 where (名字=@name or @name is null) and (工资<@pay1 or @pay1 is null) and (工资>@pay2 or @pay2 is null)

Answer 12

小问下:
where 1=1  有什么作用???
谢谢!!

Answer 13

where 1=1 是书写的一种方式，
假如 sql="" 的说程序不会出错，列出全部。

Answer 14

where 1=1的写法是为了检化程序中对条件的检测
打个比方有三个参数a, b, c
@sql=select * from tb'
这三个参数都可能为空
这时你要构造语句的话,一个个检测再写语句就麻烦
比如
if @a is not null
 @sql=@sql + " where a=' + @a
if @b is not null
 这里你怎么写?要不要加where 或直接用 and ?,你这里还要对@a是否为空进行检测用上 where 1=1 之后,就不存在这样的问题, 条件是 and 就直接and ,是or就直接接 or

Answer 15

上面手误 @sql='select * from tb'

Answer 16

最好不要拼sqlcreate procedure p_test(
@name varchar(10)='%',
@pay1 int=0,
@pay2 int=999999999 
--或者
--@pay1 int=-2147483648,
--@pay2 int=2147483647          
as
select * from 表名 
where 名字 like @name
and 工资 between @pay1 and @pay2go这样是不是最简单？

Answer 17

是最简单,但是错误的.
declare @t table(i varchar(10))
insert @t select null
select * from @t
select * from @t where i like'%'

Answer 18

请问各位大侠，isnull第二个参数是什么意思？

Answer 19

ISNULL(a,'x')
当a为空(NULL)时,表达式的值为 'x',否则为 a的值.

Answer 20

CREATE PROC dbo._QuerySalary
(
        @名字 NVARCHAR(50) =NULL,
        @工资上限 INT =NULL,
        @工资下限 INT =NULL
)
ASIF @名字 = ''
SET @名字 = NULLIF @工资上限 = ''
SET @工资上限 = NULLIF @工资上限 = ''
SET @工资上限 = NULLSELECT * 
FROM 
表名
WHERE 
名字=ISNULL(@名字,名字)
and 工资<=ISNULL(@工资上限,工资+1 )
and 工资>=ISNULL(@工资下限, 工资-1)

Answer 21

oracle中有存储过程的重载
sqlserver也应该有