SQL动态实施存储过程

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 '+@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