代码之家 › 专栏 › 技术社区 › SE1986

sp\u执行SQL为原始查询提供不同的结果

ado.net sql-server-2008-r2 sql-server

SE1986 · 技术社区 · 7 年前

我有一个。NET应用程序,该应用程序使用SqlConnection和SqlCommand类连接到SQL数据库。当我运行应用程序并使用SQL Server Profiler监视数据库查询时,我可以看到。NET app正在生成以下代码:

exec sp_executesql N'IF object_id(''tempdb..#ParmsInProc'') IS NOT NULL DROP TABLE #ParmsInProc 
                     SELECT o.name AS ProcName, o.object_id, p.name AS ParamName, p.system_type_id, p.user_type_id
                     INTO   #ParmsInProc
                     FROM   sys.objects o
                            JOIN sys.parameters p
                                ON o.object_id = p.object_id
                            JOIN sys.types t
                                ON t.system_type_id = p.system_type_id AND t.user_type_id = p.user_type_id
                    WHERE   type_desc = ''SQL_STORED_PROCEDURE'' AND o.name = ''@sp''

                    SELECT  * 
                    FROM    #ParmsInProc p        
                            LEFT JOIN ( 
                                            SELECT  * 
                                            FROM    #ParmsInProc 
                                            WHERE   system_type_id = 56 AND user_type_id = 56 
                                        ) ints 
                                ON p.system_type_id = ints.system_type_id AND p.user_type_id = ints.user_type_id',N'@sp nvarchar(14)',@sp=N'sp_myProcedure'

如果在SSMS中运行该代码,则不会得到任何结果。但是,如果复制查询并用实际值替换参数占位符,则会得到

IF  object_id('tempdb..#ParmsInProc') IS NOT NULL DROP TABLE #ParmsInProc 
SELECT  o.name AS ProcName, o.object_id, p.name AS ParamName, p.system_type_id, p.user_type_id
INTO    #ParmsInProc
FROM    sys.objects o
        JOIN sys.parameters p
            ON o.object_id = p.object_id
        JOIN sys.types t
            ON t.system_type_id = p.system_type_id AND t.user_type_id = p.user_type_id
WHERE   type_desc = 'SQL_STORED_PROCEDURE' AND o.name = 'sp_myProcedure'

SELECT  * 
FROM    #ParmsInProc p        
        LEFT JOIN ( 
                        SELECT  * 
                        FROM    #ParmsInProc 
                        WHERE   system_type_id = 56 AND user_type_id = 56 
                    ) ints 
            ON p.system_type_id = ints.system_type_id AND p.user_type_id = ints.user_type_id

如果我运行它,我会得到一个结果(这是我预期的结果)

1 回复 | 直到 7 年前

Rigerta 7 年前

如果您尝试“调试”初始查询,您将看到 @sp 正在作为过程名称而不是参数的实际值传递。

您可以通过以下内容看到这一点:

declare @spname nvarchar(100) = 'my_storedproc';
declare @sql nvarchar(max) = '   IF object_id(''tempdb..#ParmsInProc'') IS NOT NULL DROP TABLE #ParmsInProc 
                                 SELECT o.name AS ProcName, o.object_id, p.name AS ParamName, p.system_type_id, p.user_type_id
                                 INTO   #ParmsInProc
                                 FROM   sys.objects o
                                        JOIN sys.parameters p
                                            ON o.object_id = p.object_id
                                        JOIN sys.types t
                                            ON t.system_type_id = p.system_type_id AND t.user_type_id = p.user_type_id
                                WHERE   type_desc = ''SQL_STORED_PROCEDURE'' AND o.name = ''@sp'';

                                SELECT  * 
                                FROM    #ParmsInProc p        
                                        LEFT JOIN ( 
                                                        SELECT  * 
                                                        FROM    #ParmsInProc 
                                                        WHERE   system_type_id = 56 AND user_type_id = 56 
                                                    ) ints 
                                            ON p.system_type_id = ints.system_type_id AND p.user_type_id = ints.user_type_id;'
print @sql

正在生成以下内容:

通过将sql查询存储到变量中,我对您的查询进行了一些修改,只是为了能够打印出来,但您当然可以按原样内联执行它:

declare @spname nvarchar(100) = 'my_storedproc';
declare @sql nvarchar(max) = '   IF object_id(''tempdb..#ParmsInProc'') IS NOT NULL DROP TABLE #ParmsInProc 
                                 SELECT o.name AS ProcName, o.object_id, p.name AS ParamName, p.system_type_id, p.user_type_id
                                 INTO   #ParmsInProc
                                 FROM   sys.objects o
                                        JOIN sys.parameters p
                                            ON o.object_id = p.object_id
                                        JOIN sys.types t
                                            ON t.system_type_id = p.system_type_id AND t.user_type_id = p.user_type_id
                                WHERE   type_desc = ''SQL_STORED_PROCEDURE'' AND o.name = @sp;

                                SELECT  * 
                                FROM    #ParmsInProc p        
                                        LEFT JOIN ( 
                                                        SELECT  * 
                                                        FROM    #ParmsInProc 
                                                        WHERE   system_type_id = 56 AND user_type_id = 56 
                                                    ) ints 
                                            ON p.system_type_id = ints.system_type_id AND p.user_type_id = ints.user_type_id;'
print @sql
exec sp_executesql @sql, N'@sp nvarchar(100)', @sp = @spname

这将成功执行,您可以在“消息”选项卡下看到打印的sql查询:)