exec sp_executesql N'SELECT DISTINCT workID FROM dbo.IdlePersonInfo INNER JOIN (SELECT pid, min(AddDate) AS AddDate FROM dbo.IdlePersonInfo WHERE pid in (@Pids) GROUP BY pid) AS T ON dbo.IdlePersonInfo.pid=T.pid AND T.AddDate<@EventStartDate', N'@Pids varchar(5),@EventStartDate smalldatetime',N'@Pids varchar(100),@EventStartDate datetime', @Pids='6,1,3',@EventStartDate='2010-11-16 00:00:00:000'
DECLARE @s NVARCHAR(4000),@Pids NVARCHAR(200) SET @Pids='6,1,3' SET @s=N'SELECT DISTINCT workID FROM dbo.IdlePersonInfo INNER JOIN (SELECT pid, min(AddDate) AS AddDate FROM dbo.IdlePersonInfo WHERE pid in ('+@Pids+') GROUP BY pid) AS T ON dbo.IdlePersonInfo.pid=T.pid AND T.AddDate<@EventStartDate'exec sp_executesql @s,N'@EventStartDate smalldatetime',@EventStartDate='2010-11-16 00:00:00:000'這樣用
不好意思,手误,改一下:exec sp_executesql N'SELECT DISTINCT workID FROM dbo.IdlePersonInfo INNER JOIN (SELECT pid, min(AddDate) AS AddDate FROM dbo.IdlePersonInfo WHERE charindex('',''+ltrim(pid)+'','','',''+@pids+'','')>0 GROUP BY pid) AS T ON dbo.IdlePersonInfo.pid=T.pid AND T.AddDate<@EventStartDate', N'@Pids varchar(5),@EventStartDate smalldatetime', @Pids='6,1,3',@EventStartDate='2010-11-16 00:00:00:000'
exec sp_executesql N'SELECT DISTINCT workID FROM dbo.IdlePersonInfo INNER JOIN (SELECT pid, min(AddDate) AS AddDate FROM dbo.IdlePersonInfo WHERE pid in (@Pids) GROUP BY pid) AS T ON dbo.IdlePersonInfo.pid=T.pid AND T.AddDate<@EventStartDate', N'@Pids varchar(5),@EventStartDate smalldatetime',N'@Pids varchar(100),@EventStartDate datetime', @Pids='select 6,1,3',@EventStartDate='2010-11-16 00:00:00:000'
--测试 create table #tb(s varchar(10),t int) insert #tb select 'a', 1 union all select 'a', 2 union all select 'b',3 union all select 'b',4 union all select 'b', 5 union all select 'a', 6 union all select 'a', 7 union all select 'c', 8declare @s varchar(50) set @s='1,2,3' print 'select * from #tb where t in(0,'+@s+')' --方法1 exec('select * from #tb where t in('+@s+')') --方法2 select * from #tb where charindex(','+ltrim(t)+',',','+@s+',')>0 /** select * from #tb where t in(0,1,2,3) s t ---------- ----------- a 1 a 2 b 3(3 行受影响) **/
exec sp_executesql N'SELECT DISTINCT workID FROM dbo.IdlePersonInfo INNER JOIN (SELECT pid, min(AddDate) AS AddDate FROM dbo.IdlePersonInfo WHERE pid in (@Pids) GROUP BY pid) AS T ON dbo.IdlePersonInfo.pid=T.pid AND T.AddDate<@EventStartDate ' N'@Pids varchar(5),@EventStartDate smalldatetime Set @Pids='+QuoteName('6,1,3','''') +' Set @EventStartDate='+ QuoteName('2010-11-16 00:00:00:000','''')
N'SELECT DISTINCT workID FROM dbo.IdlePersonInfo INNER JOIN (SELECT pid, min(AddDate) AS AddDate
FROM dbo.IdlePersonInfo WHERE pid in (@Pids) GROUP BY pid) AS T ON
dbo.IdlePersonInfo.pid=T.pid AND T.AddDate<@EventStartDate',
N'@Pids varchar(5),@EventStartDate smalldatetime',N'@Pids varchar(100),@EventStartDate datetime',
@Pids='6,1,3',@EventStartDate='2010-11-16 00:00:00:000'
SET @Pids='6,1,3'
SET @s=N'SELECT DISTINCT workID FROM dbo.IdlePersonInfo INNER JOIN (SELECT pid, min(AddDate) AS AddDate
FROM dbo.IdlePersonInfo WHERE pid in ('+@Pids+') GROUP BY pid) AS T ON
dbo.IdlePersonInfo.pid=T.pid AND T.AddDate<@EventStartDate'exec sp_executesql @s,N'@EventStartDate smalldatetime',@EventStartDate='2010-11-16 00:00:00:000'這樣用
N'SELECT DISTINCT workID FROM dbo.IdlePersonInfo INNER JOIN (SELECT pid, min(AddDate) AS AddDate
FROM dbo.IdlePersonInfo WHERE charindex('',''+ltrim(pid)+'','','',''+@pids+'','')>0 GROUP BY pid) AS T ON
dbo.IdlePersonInfo.pid=T.pid AND T.AddDate<@EventStartDate',
N'@Pids varchar(5),@EventStartDate smalldatetime',
@Pids='6,1,3',@EventStartDate='2010-11-16 00:00:00:000'
N'SELECT DISTINCT workID FROM dbo.IdlePersonInfo INNER JOIN (SELECT pid, min(AddDate) AS AddDate
FROM dbo.IdlePersonInfo WHERE pid in (@Pids) GROUP BY pid) AS T ON
dbo.IdlePersonInfo.pid=T.pid AND T.AddDate<@EventStartDate',
N'@Pids varchar(5),@EventStartDate smalldatetime',N'@Pids varchar(100),@EventStartDate datetime',
@Pids='select 6,1,3',@EventStartDate='2010-11-16 00:00:00:000'
create table #tb(s varchar(10),t int)
insert #tb
select 'a', 1
union all select 'a', 2
union all select 'b',3
union all select 'b',4
union all select 'b', 5
union all select 'a', 6
union all select 'a', 7
union all select 'c', 8declare @s varchar(50)
set @s='1,2,3'
print 'select * from #tb where t in(0,'+@s+')'
--方法1
exec('select * from #tb where t in('+@s+')')
--方法2
select * from #tb where charindex(','+ltrim(t)+',',','+@s+',')>0
/**
select * from #tb where t in(0,1,2,3)
s t
---------- -----------
a 1
a 2
b 3(3 行受影响)
**/
N'SELECT DISTINCT workID FROM dbo.IdlePersonInfo INNER JOIN (SELECT pid, min(AddDate) AS AddDate
FROM dbo.IdlePersonInfo WHERE pid in (@Pids) GROUP BY pid) AS T ON
dbo.IdlePersonInfo.pid=T.pid AND T.AddDate<@EventStartDate '
N'@Pids varchar(5),@EventStartDate smalldatetime Set @Pids='+QuoteName('6,1,3','''')
+' Set @EventStartDate='+ QuoteName('2010-11-16 00:00:00:000','''')