22,209
社区成员
发帖
与我相关
我的任务
分享
declare @indextable table(id int identity(1,1),nid int)
exec('insert into @indextable(nid) select CardID from (select top 10 ROW_NUMBER() over(order by CardID) no,DeptCode,CurrentLocation,CardID from CardInfo order by CardID)s')
DECLARE @STR VARCHAR(4000)
SELECT @STR = ISNULL(@STR,'')+id+',' FROM @indextable
DECLARE @STR VARCHAR(4000)
SELECT @STR = ISNULL(@STR,'')+CardID+',' FROM CardInfo
exec('select CardID,@STR = ISNULL(@STR,'')+CardID+',' from (select top 10 ROW_NUMBER() over(order by CardID) no,DeptCode,CurrentLocation,CardID from CardInfo order by CardID)s')
SELECT @STR
--加ltrim
SELECT @STR = STUFF((SELECT ',' +ISNULL(ltrim(ID),'') FROM @INDEXTABLE FOR XML PATH('')),1,1,'')
select CardID from (select top 10 ROW_NUMBER() over(order by CardID) no,
DeptCode,CurrentLocation,CardID from CardInfo order by CardID)
----看看上面这一段跟下面的结果是不是一样呢
select top 10 CardID from CardInfo order by CardID
DECLARE @INDEXTABLE TABLE(ID INT IDENTITY(1,1),NID INT)
EXEC('INSERT INTO @INDEXTABLE(NID) SELECT CARDID
FROM (SELECT TOP 10 ROW_NUMBER() OVER(ORDER BY CARDID) NO,
DEPTCODE,CURRENTLOCATION,CARDID FROM CARDINFO ORDER BY CARDID)S')
DECLARE @STR VARCHAR(4000)
SELECT @STR = STUFF((SELECT ',' +ISNULL(ID,'') FROM @INDEXTABLE FOR XML PATH('')),1,1,'')
declare @str varchar(4000)
exec sp_executesql
N'select top 10 @str = isnull(@str+'','','''') + rtrim(CardID) from CardInfo order by CardID',
N'@str varchar(4000) output',
@str output
select @str