34,594
社区成员
发帖
与我相关
我的任务
分享
string sql = "SET ROWCOUNT 1;" + sql ; //sql 为原来的客户的sql .
要不然, 一个表有 1 亿行, 不是得把你的服务器内存撑爆?
WPF 没用过, 不好意思没那么多才。USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(
id INT PRIMARY KEY,
[name] NVARCHAR(10),
addTime DATETIME DEFAULT(GETDATE())
)
GO
INSERT INTO t(id,[name]) VALUES(1,'a')
INSERT INTO t(id,[name]) VALUES(2,'b')
INSERT INTO t(id,[name]) VALUES(3,'c')
GO
DECLARE @sql NVARCHAR(MAX)
DECLARE @tmp TABLE (colInfo NVARCHAR(MAX))
--客户原sql
SET @sql='SELECT id,[name] FROM t'
--------- 加工sql, 得到用户的列 --------------
SET @sql=
--限制只输出一行,避免数据集过大
'SET ROWCOUNT 1; '
+
--将 from 替换为 into #tmp FROM
STUFF(@sql,CHARINDEX('FROM',@sql),5,' into #tmp FROM ')
+
--查询得到的临时表的相关列
';
SELECT STUFF(
(SELECT '',''+[name] FROM tempdb.sys.[columns] AS c WHERE c.[object_id]=OBJECT_ID(''tempdb..#tmp'') FOR XML PATH(''''))
,1,1,'''');
drop table #tmp;
'
PRINT @sql;
INSERT INTO @tmp
EXEC(@sql)
SELECT * FROM @tmp;
/*
colInfo
---------
id,name
*/
USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(
id INT PRIMARY KEY,
[name] NVARCHAR(10),
addTime DATETIME DEFAULT(GETDATE())
)
GO
INSERT INTO t(id,[name]) VALUES(1,'a')
INSERT INTO t(id,[name]) VALUES(2,'b')
INSERT INTO t(id,[name]) VALUES(3,'c')
GO
SELECT id,[name] FROM t
WHERE id IN (1,2)
/*
id name
----------- ----------
1 a
2 b
*/
不需要倒行逆施, in 写后面不就好了?SELECT name
FROM syscolumns
WHERE ID=object_id('表名')