以下是本人乱写的,大概想要实现的意思是传来三个参数据,如果其中一个参数为空时,就执行另一个查询语句,我是照我的想法乱写了下面,可是不行.我也不知应该怎么写?请高手帮帮.谢谢!!
CREATE PROCEDURE dbo.Us
@user_id nvarchar (50),
@user_sex nvarchar (50),
@user_email nvarchar (50)
AS
SELECT * from User_ta where
if(@user_id is not null)
begin
user_id=@user_id
endif(@user_sex is not null)
begin
if(@user_id is not null)
begin
and user_sex=@user_sex
end
else
begin
user_sex=@user_sex
end
end if(@user_email is not null)
begin
if((@user_id is not null) or (@user_sex is not null))
begin
and user_email=@user_email
end
else
begin
user_email=@user_email
end
end
order by ID descGO
CREATE PROCEDURE dbo.Us
@user_id nvarchar (50),
@user_sex nvarchar (50),
@user_email nvarchar (50)
AS
SELECT * from User_ta where
if(@user_id is not null)
begin
user_id=@user_id
endif(@user_sex is not null)
begin
if(@user_id is not null)
begin
and user_sex=@user_sex
end
else
begin
user_sex=@user_sex
end
end if(@user_email is not null)
begin
if((@user_id is not null) or (@user_sex is not null))
begin
and user_email=@user_email
end
else
begin
user_email=@user_email
end
end
order by ID descGO
建议先去学学SQL,Stored Procedure先!
--通过SQLServer2000测试
--经过下表进行测试:
Create table User_ta
(
id int null,
user_id varchar(32) null,
user_sex varchar(32) null,
user_email varchar(32) null
)
Insert into User_ta
select 1,'t1','s1','e1'
Insert into User_ta
select 2,'t2','s2','e2'
--执行语句
Execute Us 't2',null,null*/
Create PROCEDURE dbo.Us
@user_id varchar (50),
@user_sex varchar (50),
@user_email varchar (50)
ASBegin --Begin Procedure --定义查询的最终SQL语句
Declare @SQLStr varchar(128)
set @SQLStr = ''
--根据条件为需要查询的SQL语句赋值
If (@user_id is not null)
set @SQLStr = @SQLStr + ' and user_id = ''' + @user_id + ''''
If (@user_sex is not null)
set @SQLStr = @SQLStr + ' and user_sex = ''' + @user_sex + ''''
If (@user_email is not null)
set @SQLStr = @SQLStr + ' and user_email = ''' + @user_email + ''''
--执行最终的SQL语句
Execute('SELECT * from User_ta where 1 = 1'
+ @SQLStr + ' Order by ID desc ')
--测试语句,已注释。
-- Print('SELECT * from User_ta where 1 = 1 '
-- + @SQLStr + ' Order by ID desc ')End --End Procedure
GO