sql

[yuan105yong]

alter PROCEDURE [dbo].[sp_DAL_BrushCardInfo3]
@dtime1 nvarchar(10),--開始時間
@dtime2 nvarchar(10),--結束時間
@part nvarchar(30),--部門代碼
@per001 nvarchar(30)--員工工號

AS
declare @strBasic nvarchar(1000)--基本字符串1
declare @strBasic2 nvarchar(1000)--基本字符串2
declare @strSql nvarchar(1500)--最終執行語句
BEGIN
SET NOCOUNT ON;
if(month(@dtime1)<10)
begin
set @strBasic=' select empid,cardid,Per005,machinesnum,STATE, cast(dtime as datetime) as dtime from WF_BS_PersonInfo right join '
set @strBasic2 =@strBasic+'[MT-FTP-NB].[KS-EXCHANGE-DB].[dbo].[coll'+cast(year(@dtime1)as nvarchar(4))+'0'+cast(month(@dtime2)as nvarchar(1))+'] '
set @strSql =@strBasic2+'on empid =Per001 where empid ='''+@per001+''' and (Per005 ='''+@part+''' )and cast(dtime as datetime) between '''+ @dtime1 +''' and '''+ @dtime2+''''
end--convert(nvarchar(12),cast(dtime as datetime),120 )
else
begin
set @strBasic=' select empid,cardid,Per005,machinesnum,STATE, cast(dtime as datetime) as dtime from WF_BS_PersonInfo right join '
set @strBasic2 =@strBasic+'[MT-FTP-NB].[KS-EXCHANGE-DB].[dbo].[coll'+cast(year(@dtime1)as nvarchar(4))+cast(month(@dtime2)as nvarchar(2))+'] '
set @strSql =@strBasic2+'on empid =Per001 where empid like'''+@per001+'%'' and (Per005 like'''+@part+'%'' )and cast(dtime as datetime) between '''+ @dtime1 +''' and '''+ @dtime2+''''
end
exec @strSql
END

select empid,cardid,Per005,machinesnum,STATE, convert(nvarchar(19),cast(dtime as datetime),120 ) from WF_BS_PersonInfo right join [MT-FTP-NB].[KS-EXCHANGE-DB].[dbo].[coll200904] on empid =Per001 where empid like'534929%' and (Per005 like'%' )and convert(nvarchar(12),cast(dtime as datetime),120 ) between '2009-4-1' and '2009-4-10'

[claro]

帮顶。

[hehaiyi2008]

up!

[sdhdy]

楼主什么意思？

[ljhcy99]

不解哦

[ws_hgo]

接分走人

[htl258_Tony]

好像没什么问题吧。

[让你望见影子的墙]

楼主有什么问题？

[yuan105yong]

对于
select empid,cardid,Per005,machinesnum,STATE, convert(nvarchar(19),cast(dtime as datetime),120 ) from WF_BS_PersonInfo right join [MT-FTP-NB].[KS-EXCHANGE-DB].[dbo].[coll200904] on empid =Per001 where empid like'534929%' and (Per005 like'%' )and convert(nvarchar(12),cast(dtime as datetime),120 ) between '2009-4-1' and '2009-4-10'

这段代码单独运行的时候可以运行,而通过上面方法调用的时候不能运行,不知道问题出在什么地方,求各位朋友帮忙解答,谢谢!

如我 运行exec dbo.sp_DAL_BrushCardInfo3 '2009-1-1','2009-1-20','','534929'


错误提示是 :The name ' select empid,cardid,Per005,machinesnum,STATE, cast(dtime as datetime) as dtime from WF_BS_PersonInfo right join [MT-FTP-NB].[KS-EXCHANGE-DB].[dbo].[coll200901] on empid =Per001 where empid ='534929' and (Per005 ='' )and cast(dtime as datetime) between '2009-1-1' and '2009-1-20'' is not a valid identifier.