我们后台程序员经常写存储过程,时不时会遇到多条件查询的语句。对于经验比较少的同学,都是一句一句写。
如:
if xx=''
begin
select *** from table where table.id= '' and table.xx1=@a and table.xx2=@b
end
else
begin
select *** from table where table.id= '' and table.xx1=@a and table.xx2=@b and table.xx = @xx;
end
这还只是写一个条件,如果查询 员工信息。条件有 按 部门,姓名,手机号,住址等查询。
如果按普通写法。
if 部门=NULL
begin
if 姓名=NULL
begin
if 手机号=NULL
begin
*****
end
end
else
begin
*****
end
end
else
begin
*****
end
太多了,写下去还会有很多。就算你写完了,出错的概率很大,而且出错后,也比较难查出来。这时候我们就需要条件拼接语句。
这里我也不多说了,直接写demo。
USE [ProjectAssistant]
GO
/****** Object: StoredProcedure [dbo].[pro_loadMaterials] Script Date: 05/21/2023 16:59:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[pro_loadMaterials]
-- Add the parameters for the stored procedure here
@beginTime varchar(20),
@endTime varchar(20),
@company varchar(15),
@code varchar(15),
@name varchar(15),
@kind varchar(15),
@attr varchar(15),
@place varchar(50),
@pageSize int,
@pageIndex int,
@totalCount int OUT,
@pageCount int OUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
declare @strWhere nvarchar(max)='';
declare @strWhere1 nvarchar(max)='';
declare @strWhere2 nvarchar(max)='';
set @strWhere1='select * from (select*,ROW_NUMBER() over ( order by dbo.Supmannage_MaterialInfo_table.ID) as num1 from dbo.Supmannage_MaterialInfo_table where dbo.Supmannage_MaterialInfo_table.TIME between' +''''+ @beginTime+''''+' and '+''''+ @endTime+''''
+' ';
--set @strWhere=cast(34151 as varchar(10));
set @strWhere2='select '+' @totalCount= COUNT(*) from dbo.Supmannage_MaterialInfo_table where (dbo.Supmannage_MaterialInfo_table.TIME between ' +''''+ @beginTime+''''+' and '+''''+ @endTime+''''+' )';
print @strWhere2
if(@company<>'')
begin
set @strWhere+=' and Supmannage_MaterialInfo_table.COMPANY = '+''''+@company+'''';
end
if(@code<>'')
begin
set @strWhere+=' and Supmannage_MaterialInfo_table.CODE LIKE '+''''+ '%'+@code+'%'+'''';
end
if(@name<>'')
begin
set @strWhere+=' and Supmannage_MaterialInfo_table.NAME LIKE '+''''+ '%'+@name+'%'+'''';
end
if(@kind<>'')
begin
set @strWhere+=' and Supmannage_MaterialInfo_table.KIND LIKE '+''''+ '%'+@kind+'%'+'''';
end
if(@attr<>'')
begin
set @strWhere+=' and Supmannage_MaterialInfo_table.ATTR LIKE '+''''+ '%'+@attr+'%'+'''';
end
if(@place<>'')
begin
set @strWhere+=' and Supmannage_MaterialInfo_table.PLACE LIKE '+''''+ '%'+@place+'%'+'''';
end
set @strWhere2+=@strWhere;
exec sp_executesql @strWhere2,N'@totalCount int out',@totalCount out
--print @strWhere2
--exec(@strWhere2)
set @totalCount=@totalCount
set @pageCount = CEILING(1.0*@totalCount/@pageSize);
if(@totalCount<@pageSize and @pageCount=0)
set @pageCount=1;
--select * from (select*,ROW_NUMBER() over ( order by dbo.Supmannage_MaterialInfo_table.ID) as num1 from dbo.Supmannage_MaterialInfo_table
-- where (dbo.Supmannage_MaterialInfo_table.TIME between @beginTime and @endTime ) and (Supmannage_MaterialInfo_table.COMPANY=@company)
-- and (Supmannage_MaterialInfo_table.PLACE LIKE '%'+@place+'%')) as temp where num1
--between @pageSize*(@pageIndex-1)+1 and @pageSize*@pageIndex;
declare @tempstr varchar(200);
set @tempstr=') as temp where num1 between '+ cast((@pageSize*(@pageIndex-1)+1) as varchar(10))+' and '+cast((@pageSize*@pageIndex) as varchar(10));
set @strWhere1+=@strWhere+@tempstr;
print @strWhere1
exec(@strWhere1)
END
执行结果
这个存储过程功能就是按多条件查询第一页的50记录。并查询出总记录数与总页数(以50个记录为一页)。
这里有个重点,网上没有讲清楚,也没有重点提一下。
就是这一句。与 下面一句的变量
拼接的方法:
1、首先在字符串的前后加单引号;
2、字符串中的变量以’’’+@para+’’’在字符串中表示;
3、若在执行时存在类型转换错误,则应用相应的类型转换函数,对变量进行类型转换(如cast()函数)。
4、对于含输出变量,采用这个执行 exec sp_executesql 这个方法执行语句。