常用方法
--字符串转换成数字
--CAST("1" AS int)
--CONVERT(int,"1")
--截取字符串
SUBSTRING(OccurreAddress,0,7)
--拼接字符串，数字和数字字符相加，会按照数字相加
select 'str'+'11'--str11
select '2'+1 --3
select 2+CONVERT(nvarchar(max),2)--4
select '2'+CONVERT(nvarchar(max),2)--22
select 'str'+'11'--str11
select '22'+'11' --2211
--时间相关
Year(getdate()) --当前年
Month(getdate()) --当前月
Day(getdate()) --当前日
select getdate() --2016-04-11 17:36:07.950
select SUBSTRING('2015rr05',5,2)--rr
select getdate()
select Month(getdate())
Datediff(d,时间字段,getdate()) --得到离过现在还剩的天数
select Datediff(DAY,2015-12-01,getdate()) --第2个开始 第3个结束
select CONVERT(Datetime,'20151201')--2015-12-01 00:00:00.000
select CONVERT(Datetime,'2015-12-01')--2015-12-01 00:00:00.000
--select @a=count(1) from tb 此处查询语句中不能@a+=
--我设置一个临时@temp， select @temp=count(1) from tb1
--然后先@a=@temp
--我设置一个临时@temp， select @temp=count(1) from tb2
--然后先@a+=','+@temp
--这个临时变量一直变
declare @temp int
declare @b nvarchar(max)
set @temp=1;
set @b=CONVERT(nvarchar(max), @temp )
select @b--1
set @temp=2;
set @b+=','+CONVERT(nvarchar(max), @temp )
select @b--12
--以上代码需要转换类型
--其实我们查询数量的并赋值给@temp的时候，是int
--我们在上面直接声明declare @temp nvarchar(max),就不需要再转了。
代码1 存储过程，循环，+=
USE [ISFTCMOPSSystem]
GO
/****** Object: StoredProcedure [dbo].[SelectEventCount] Script Date: 04/19/2016 10:47:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--重特大案件SeriousCase
--矛盾纠纷案件 DisputesCase
--师生安全案件 StudentSafetyCase
--护路护线案件 RoadLineProtectiveCase
ALTER PROCEDURE [dbo].[SelectEventCount]
@DistrictCode varchar(max), --传入
@SC NVARCHAR(max) output,
@DC NVARCHAR(max) output,
@SSC NVARCHAR(max) output,
@RPC NVARCHAR(max) output
as
--重特大案件SC
declare @str1 nvarchar(max)
declare @RecentMonthCount INT
SET @RecentMonthCount = 0
while (@RecentMonthCount <12)
begin
select @str1= COUNT(1) from ISFTCMOPSSystem.dbo.Info_SeriousCase where
SUBSTRING(OccurreAddress,0,7)=@DistrictCode
and Datediff(MONTH,CONVERT(Datetime,OccurreDate),getdate())=@RecentMonthCount
IF @RecentMonthCount = 0
begin
if @str1 ='0'
set @SC='0'
else
set @SC=@str1
end
else
if @str1 ='0'
set @SC = @SC +',0'
else
set @SC = @SC +','+@str1
select @str1= COUNT(1) from ISFTCMOPSSystem.dbo.Info_DisputesCase where
SUBSTRING(PerIdNumber,0,7)=@DistrictCode
and Datediff(MONTH,CONVERT(Datetime,OccurDate),getdate())=@RecentMonthCount
IF @RecentMonthCount = 0
begin
if @str1 ='0'
set @DC='0'
else
set @DC=@str1
end
else
if @str1 ='0'
set @DC = @DC +',0'
else
set @DC = @DC +','+@str1
select @str1= COUNT(1) from ISFTCMOPSSystem.dbo.Info_StudentSafetyCase where
SUBSTRING(PrinIdNumber,0,7)=@DistrictCode
and Datediff(MONTH,CONVERT(Datetime,OccDate),getdate())=@RecentMonthCount
IF @RecentMonthCount = 0
begin
if @str1 ='0'
set @SSC='0'
else
set @SSC=@str1
end
else
if @str1 ='0'
set @SSC = @SSC +',0'
else
set @SSC = @SSC +','+@str1
select @str1= COUNT(1) from ISFTCMOPSSystem.dbo.Info_RoadLineProtectiveCase where
SUBSTRING(PrinIdNumber,0,7)=@DistrictCode
and Datediff(MONTH,CONVERT(Datetime,OccDate),getdate())=@RecentMonthCount
IF @RecentMonthCount = 0
begin
if @str1 ='0'
set @RPC='0'
else
set @RPC=@str1
end
else
if @str1 ='0'
set @RPC = @RPC +',0'
else
set @RPC = @RPC +','+@str1
set @RecentMonthCount = @RecentMonthCount + 1
end
--矛盾纠纷案件DC
SET @RecentMonthCount = 0
--------------------------------------------------------------------------------
--重特大案件SC
--select @DC=COUNT(1) from ISFTCMOPSSystem.dbo.Info_DisputesCase where SUBSTRING(PerIdNumber,0,7)=@DistrictCode
--select @SSC=COUNT(1) from ISFTCMOPSSystem.dbo.Info_StudentSafetyCase where SUBSTRING(PrinIdNumber,0,7)=@DistrictCode
--select @RPC=COUNT(1) from ISFTCMOPSSystem.dbo.Info_RoadLineProtectiveCase where SUBSTRING(PrinIdNumber,0,7)=@DistrictCode
代码 例子2
ALTER proc [dbo].[SelectSpecialEventCount]
@name nvarchar(100),
@arraySting nvarchar(max) output--因为数据库中没有数组类型，将查询多个表的值，组装成一个字符串，输出类型。
as
--重特大案件SeriousCase
--命案防控 MurderPrevention
--矛盾纠纷案件 DisputesCase
--师生安全案件 StudentSafetyCase
--护路护线案件 RoadLineProtectiveCase
declare @temp int
set @temp=0
select @temp= COUNT(1) from ISFTCMOPSSystem.dbo.Info_SeriousCase where CaseName= 'd'
if @temp = 0
begin
set @arraySting='0' --第一，=以后+=
end
else
begin
set @arraySting=CONVERT(nvarchar(100),@temp)--第一，=以后+=
end
select @temp= COUNT(1) from ISFTCMOPSSystem.dbo.Info_MurderPrevention where CaseName= @name
if @temp=0
begin
set @arraySting+=',0'
end
else
begin
set @arraySting+=','+CONVERT(nvarchar(100),@temp)
end
select @temp+= COUNT(1) from ISFTCMOPSSystem.dbo.Info_DisputesCase where CaseName= @name
if(@temp=0)
begin
set @arraySting+=',0'
end
else
begin
set @arraySting+=','+CONVERT(nvarchar(100),@temp)
end
select @temp+= COUNT(1) from ISFTCMOPSSystem.dbo.Info_StudentSafetyCase where CaseName= @name
if(@temp=0)
begin
set @arraySting+=',0'
end
else
begin
set @arraySting+=','+CONVERT(nvarchar(100),@temp)
end
select @temp+= COUNT(1) from ISFTCMOPSSystem.dbo.Info_RoadLineProtectiveCase where CaseName= @name
if(@temp=0)
begin
set @arraySting+=',0'
end
else
begin
set @arraySting+=','+CONVERT(nvarchar(100),@temp)
end
--调试
--declare @a nvarchar(max)
--exec SelectSpecialEventCount 'd',@a output --调用存储过程 参数外不带（）
--print @a
--结果50,0,0,0,0
分页存储过程
USE [ISFTCMOPSSystem]
GO
/****** Object: StoredProcedure [dbo].[selectEventUnion] Script Date: 04/18/2016 16:01:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[selectEventUnion]
@name nvarchar(max),
@PageIndex int, --逗号
@PageSize int,
@PageCount int output
as
declare @Count int;
select @Count=count(1)FROM (
select '重特大案件' as title ,CaseName,SeriousCaseID as Id,'Info_SeriousCase' as tableName from dbo.Info_SeriousCase where CaseName like '%'+@name+'%'
union all
select '命案防控' as title ,CaseName,PreventionID as Id,'Info_MurderPrevention' as tableName from dbo.Info_MurderPrevention where CaseName like '%'+@name+'%'
union all
select '矛盾纠纷案件' as title ,CaseName,DCaseID as Id,'Info_DisputesCase' as tableName from dbo.Info_DisputesCase where CaseName like '%'+@name+'%'
union all
select '师生安全案件' as title ,CaseName,SSCaseID as Id ,'Info_StudentSafetyCase' as tableName from dbo.Info_StudentSafetyCase where CaseName like '%'+@name+'%'
union all
select '护路护线案件' as title ,CaseName,PropeCaseID as Id ,'Info_RoadLineProtectiveCase' as tableName from dbo.Info_RoadLineProtectiveCase where CaseName like '%'+@name+'%'
) as tableUnion;
select @PageCount=ceiling(@Count*1.0/@PageSize);
select title,CaseName,Id,tableName from
(select row_number() over (order by tableUnion.Id) as indexNumber, title,CaseName,Id,tableName from
(
select '重特大案件' as title ,CaseName,SeriousCaseID as Id,'Info_SeriousCase' as tableName from dbo.Info_SeriousCase where CaseName like '%'+@name+'%'
union all
select '命案防控' as title ,CaseName,PreventionID as Id,'Info_MurderPrevention' as tableName from dbo.Info_MurderPrevention where CaseName like '%'+@name+'%'
union all
select '矛盾纠纷案件' as title ,CaseName,DCaseID as Id,'Info_DisputesCase' as tableName from dbo.Info_DisputesCase where CaseName like '%'+@name+'%'
union all
select '师生安全案件' as title ,CaseName,SSCaseID as Id ,'Info_StudentSafetyCase' as tableName from dbo.Info_StudentSafetyCase where CaseName like '%'+@name+'%'
union all
select '护路护线案件' as title ,CaseName,PropeCaseID as Id ,'Info_RoadLineProtectiveCase' as tableName from dbo.Info_RoadLineProtectiveCase where CaseName like '%'+@name+'%'
) as tableUnion
)as tableUnionLast
where indexNumber between (@PageIndex-1)*@PageSize+1 and @PageIndex*@PageSize;
