SQL动态执行存储过程
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO www.2cto.com
CREATE PROCEDURE GetQueryDataByTableNameAndColumns
@tableName NVARCHAR(20),
@columns NVARCHAR(200)
AS
BEGIN
DECLARE @sql NVARCHAR(500)
SET @sql='SELECT '+@columns+' FROM '+@tableName
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 '+@columns+' FROM '+@tableName
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('+@columnName+') FROM '+@tableName
SET @outputParamDefine='@maxValue 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 '+@columns+' FROM '+@tableName
EXEC (@sql) --正确！！！
EXEC ('SELECT '+@columns+' FROM '+@tableName) --正确！！！
EXEC sys.sp_executesql @sql --正确！！！但是如果@sql定义成varchar类型此句就会出错
EXEC sys.sp_executesql N'SELECT ProductName,UnitPrice FROM Products'--正确！！！直接执行语句，语句前面必须加“N”
EXEC sys.sp_executesql N'SELECT '+@columns+' FROM '+@tableName --错误！！！不能执行sql拼接，即使前面加N