也就是说语句可能是任何语句,比如Select * from A, Select * from B这两条语句表A和表B的结构完全不样,所以用列名去查应该是不行的, 但是我只想要其中的前两列的数据
看上去没什么好办法,用syscolumns表查吧
CREATE PROC P_PartClumsQuery(@TableName SYSNAME,@ColumnCount INT = 200,@Condition VARCHAR(500) = NULL) AS BEGIN DECLARE @SQL NVARCHAR(MAX) SELECT @SQL = ISNULL(@SQL+N',',N'SELECT ')+ QUOTENAME(name) FROM sys.columns WHERE OBJECT_ID = OBJECT_ID(@TableName) AND column_id <=@ColumnCount SELECT @SQL = @SQL+ N'FROM '+@TableName+ISNULL(N' WHERE '+@Condition,'') PRINT @SQL EXEC (@SQL) ENDIF OBJECT_ID('DBO.TB1') IS NOT NULL DROP TABLE DBO.TB1 SELECT 1 A,2 B,3 C,4 D INTO TB1 UNION SELECT 11,12,13,14IF OBJECT_ID('DBO.TB2') IS NOT NULL DROP TABLE DBO.TB2 SELECT 100 A,200 B,300 C,400 D INTO TB2 UNION SELECT 101,201,301,401EXEC P_PartClumsQuery N'DBO.TB1',2 --查询TB1 前2列 /* A B 1 2 11 12 */EXEC P_PartClumsQuery N'DBO.TB2',3 --查询TB2 前3列 /* 1 2 3 2 3 4 */EXEC P_PartClumsQuery N'DBO.TB1',2,N'A = 1' --指定条件查询 /* A B 1 2 *
看上去没什么好办法,用syscolumns表查吧
AS
BEGIN
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = ISNULL(@SQL+N',',N'SELECT ')+ QUOTENAME(name) FROM sys.columns
WHERE OBJECT_ID = OBJECT_ID(@TableName) AND column_id <=@ColumnCount
SELECT @SQL = @SQL+ N'FROM '+@TableName+ISNULL(N' WHERE '+@Condition,'')
PRINT @SQL
EXEC (@SQL)
ENDIF OBJECT_ID('DBO.TB1') IS NOT NULL
DROP TABLE DBO.TB1
SELECT 1 A,2 B,3 C,4 D INTO TB1
UNION SELECT 11,12,13,14IF OBJECT_ID('DBO.TB2') IS NOT NULL
DROP TABLE DBO.TB2
SELECT 100 A,200 B,300 C,400 D INTO TB2
UNION SELECT 101,201,301,401EXEC P_PartClumsQuery N'DBO.TB1',2 --查询TB1 前2列
/*
A B
1 2
11 12
*/EXEC P_PartClumsQuery N'DBO.TB2',3 --查询TB2 前3列
/*
1 2 3
2 3 4
*/EXEC P_PartClumsQuery N'DBO.TB1',2,N'A = 1' --指定条件查询
/*
A B
1 2
*