我在编写存储过程时有个小问题,当我调用存储过程时将原来的ID码转换成了char字符型,而我本来的设计是直接读取int 整形,而为了方便调用复杂的查询语句不得不见其转换成字符型+CAST ( @DLID AS VARCHAR(10) )+',这将影响数据的查询速度,请问有什么好办法吗? SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GOCREATE proc sp_DL_Date_Day
(
@b datetime,
@e datetime,
@DLID int
)
as
begin
declare @sql varchar(8000)
set @sql ='select ZLID 中类ID,CatCName'
select @sql=@sql+ ',sum(CASE CatDate when '''
+ convert(varchar(10), TrueDate,120)+
'''then Cost else 0 end)['
+ convert(varchar(10),TrueDate,120) + ']'
from (select TrueDate from SDate where TrueDate >= @b and TrueDate <= @e) as aset @sql = @sql + ', sum(Cost) 总计花费
from ((select * from AD_CBD where CatDate >= '''
+CAST ( @b AS VARCHAR(10) )+'''and CatDate <='''
+CAST ( @e AS VARCHAR(10) )+''' ) as b
inner join AD_Cat as p
on b.CatID = p.CatID)
where DLID =' +CAST ( @DLID AS VARCHAR(10) )+'
Group by p.ZLID,CatCName
order by sum(Cost) Desc'exec(@sql)
endGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
GO
SET ANSI_NULLS ON
GOCREATE proc sp_DL_Date_Day
(
@b datetime,
@e datetime,
@DLID int
)
as
begin
declare @sql varchar(8000)
set @sql ='select ZLID 中类ID,CatCName'
select @sql=@sql+ ',sum(CASE CatDate when '''
+ convert(varchar(10), TrueDate,120)+
'''then Cost else 0 end)['
+ convert(varchar(10),TrueDate,120) + ']'
from (select TrueDate from SDate where TrueDate >= @b and TrueDate <= @e) as aset @sql = @sql + ', sum(Cost) 总计花费
from ((select * from AD_CBD where CatDate >= '''
+CAST ( @b AS VARCHAR(10) )+'''and CatDate <='''
+CAST ( @e AS VARCHAR(10) )+''' ) as b
inner join AD_Cat as p
on b.CatID = p.CatID)
where DLID =' +CAST ( @DLID AS VARCHAR(10) )+'
Group by p.ZLID,CatCName
order by sum(Cost) Desc'exec(@sql)
endGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
GO
SET ANSI_NULLS ON
GOCREATE proc sp_DL_Date_Day
(
@b datetime,
@e datetime,
@DLID int
)
as
begin
declare @sql varchar(8000)
set @sql ='select ZLID 中类ID,CatCName'
select @sql=@sql+ ',sum(CASE CatDate when '''
+ TrueDate +
'''then Cost else 0 end)['
+ TrueDate + ']'
from (select convert(varchar(10),TrueDate,120) As TrueDate from SDate where TrueDate >= @b and TrueDate <= @e) as aset @sql = @sql + ', sum(Cost) 总计花费
from ((select * from AD_CBD where CatDate >= '''
+CAST ( @b AS VARCHAR(10) )+'''and CatDate <='''
+CAST ( @e AS VARCHAR(10) )+''' ) as b
inner join AD_Cat as p
on b.CatID = p.CatID)
where DLID =' +CAST ( @DLID AS VARCHAR(10) )+'
Group by p.ZLID,CatCName
order by sum(Cost) Desc'exec(@sql)
endGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
服务器: 消息 295,级别 16,状态 3,行 8
从字符串转换为 smalldatetime 数据类型时发生语法错误。
这里的内嵌的查询不要用 *,指定需要的列,比如就需要CatID
in比这个要差
如果效率确实不高,可以建临时表(带索引)或索引视图来提高效率
修改成
select convert(varchar(10),TrueDate,120) As TrueDate
from SDate
where
TrueDate between convert(varchar(10),@b,120) and convert(varchar(10),@e,120)
TrueDat是datetime型的, 如果不转会
服务器: 消息 295,级别 16,状态 3,行 8
从字符串转换为 smalldatetime 数据类型时发生语法错误。
---------
難道你測試了會報錯?我有做轉換,在這個查詢裡已經做了轉換了,這樣在外面就不用做兩次轉換了。(select convert(varchar(10),TrueDate,120) As TrueDate from SDate where TrueDate >= @b and TrueDate <= @e)