动态sql 无变量的简单例子

作用是在指定的表中，查找指定的列（主键和描述，用作dropdownlist等控件的数据源），其实就是将表明和列明参数化

USE [TKInfDB]
GO
/****** Object:  StoredProcedure [MSG].[P_GetAllClasses]    Script Date: 05/22/2012 11:51:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [MSG].[P_GetAllClasses]
@tbname AS NVARCHAR(50),
@id   AS nvarchar(50),
@desc AS  NVARCHAR(255)
AS
BEGIN
DECLARE @sql AS NVARCHAR(max);
SET @sql='select '+@id+','+@desc+' from '+@tbname+' where state=0';
--EXEC (@sql);
EXEC sys.sp_executesql
@sql;
END

调用代码
USE [TKInfDB]
GO

DECLARE @return_value int

EXEC @return_value = [MSG].[P_GetAllClasses]
@tbname = N'MSG.Dic_POPClassDef',
@id = N'cid',
@desc = N'cdesc'

SELECT 'Return Value' = @return_value

GO