34,588
社区成员
发帖
与我相关
我的任务
分享
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)
END
IF 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,14
IF 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,401
EXEC 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
*
select 1,2 from 表