作用是在指定的表中,查找指定的列(主键和描述,用作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
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