SQL动态执行存储过程

最新推荐文章于 2024-08-09 15:02:39 发布

原创最新推荐文章于 2024-08-09 15:02:39 发布 · 4.3k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#动态SQL #SQL存储过程 #SQL动态存储过程 #数据库

C#日记ASP.net（vs2008平台下）专栏收录该内容

32 篇文章

订阅专栏

本文介绍了一个基于指定表名和列名返回查询数据的动态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