SQL动态执行存储过程
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Kenshin Cui -- Create date: 2010.11.20 -- Description: Based on the specified table and column returns the query data -- ============================================= CREATE PROCEDURE GetQueryDataByTableNameAndColumns @tableName NVARCHAR(20), @columns NVARCHAR(200) AS BEGIN DECLARE @sql NVARCHAR(500) SET @sql='SELECT [email protected]+' FROM [email protected] EXEC (@sql) END GO
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Kenshin Cui -- Create date: 2010.11.20 -- Description: Based on the specified table and column returns the query data -- ============================================= CREATE PROCEDURE GetQueryDataByTableNameAndColumns2 @tableName NVARCHAR(20), @columns NVARCHAR(200) AS BEGIN DECLARE @sql NVARCHAR(500) SET @sql='SELECT [email protected]+' FROM [email protected] EXEC sys.sp_executesql @sql END GO
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Kenshin Cui -- Create date: 2010.11.20 -- Description: By specifying the table name and column names to return the maximum value -- ============================================= CREATE PROCEDURE GetMaxValueByTableNameAndColumns @tableName NVARCHAR(20), @columnName NVARCHAR(200) AS BEGIN DECLARE @sql NVARCHAR(500) DECLARE @outputParamDefine NVARCHAR(100) DECLARE @maxValue NVARCHAR(50) SET @sql='SELECT @maxValue=MAX([email protected]+') FROM [email protected] SET @[email protected] NVARCHAR(50) OUTPUT' EXEC sys.sp_executesql @sql,@outputParamDefine,@maxValue OUTPUT SELECT @maxValue END GO
DECLARE @tableName NVARCHAR(20) DECLARE @columns NVARCHAR(200) DECLARE @sql NVARCHAR(500)--当使用sp_executesql执行时不能是VARCHAR(500) SET @tableName='Products' SET @columns='ProductName,UnitPrice' SET @sql=N'SELECT [email protected]+' FROM [email protected] EXEC (@sql) --正确！！！ EXEC ('SELECT [email protected]+' FROM [email protected]) --正确！！！ EXEC sys.sp_executesql @sql --正确！！！[email protected] EXEC sys.sp_executesql N'SELECT ProductName,UnitPrice FROM Products'--正确！！！直接执行语句，语句前面必须加“N” EXEC sys.sp_executesql N'SELECT [email protected]+' FROM [email protected] --错误！！！不能执行sql拼接，即使前面加N