在MS SQL Server中使用存储过程的输出初始化变量

I have created the following stored procedure..

我创建了以下存储过程..

CREATE PROCEDURE [dbo].[UDSPRBHPRIMBUSTYPESTARTUP]
(
  @CODE CHAR(5)
  , @DESC VARCHAR(255) OUTPUT
)
AS
DECLARE @SERVERNAME nvarchar(30)
DECLARE @DBASE nvarchar(30)
DECLARE @SQL nvarchar(2000)

SET @SERVERNAME = 
  Convert(nvarchar,
  (SELECT spData FROM dbSpecificData WHERE spLookup = 'CMSSERVER'))
SET @DBASE = 
  Convert(nvarchar,
  (SELECT spData FROM dbSpecificData WHERE spLookup = 'CMSDBNAME'))

SET @SQL = 
  'SELECT clnt_cat_desc FROM ' + @SERVERNAME + 
  '.' + @DBASE + '.dbo.hbl_clnt_cat WHERE inactive = ''N''
  AND clnt_cat_code = ''' + @CODE + ''''

EXECUTE sp_executeSQL @SQL

RETURN

This procedure is used in many different databases and many different servers and is written as dynamic SQL to simplify maintenance. The procedure also runs on a different server than the one the procedure points to.

此过程用于许多不同的数据库和许多不同的服务器,并编写为动态SQL以简化维护。该过程还在与过程指向的服务器不同的服务器上运行。

I want to use the output of this procedure as a value in a table...

我想使用此过程的输出作为表中的值...

DECLARE @clid BIGINT
DECLARE @fileid BIGINT
DECLARE @myCode CHAR(5)
DECLARE @myDesc VARCHAR(255)
DECLARE @@tempDesc VARCHAR(255)

SET @clid = 1831400022
SET @fileid = 2072551358
SET @myCode = 
  (SELECT _clientPrimBusinessType FROM udbhextclient WHERE clid = @clid)

SET @myDesc = 
  EXEC UDSPRBHPRIMBUSTYPESTARTUP @CODE = @myCode, @DESC = @@tempDesc OUTPUT
----------------------------------------------------------------------------
SELECT
  a.clid
  , b.fileid
  , c.usrfullname AS ClientPartner
  , e.usrfullname AS ClientFeeEarner
  , @myDesc AS ClientPrimaryBusinessType
FROM 
  dbclient a
    INNER JOIN
  dbFile b
    ON
  a.clid = b.clid
    INNER JOIN
  dbuser c
    ON 
  a.feeusrid = c.usrid
    INNER JOIN
  udbhextclient d
    ON
  a.clid = d.clid
    INNER JOIN
  dbuser e
    ON
  d._ClientFeeEarner = e.usrid
WHERE 
  a.clid = @clid
  AND b.fileid = @fileid

I know this is the incorrect syntax, but you can see what I am trying to achieve this without resorting to temporary tables as this would mean maintenance across 30 different servers with 3 to 5 databases on each.

我知道这是不正确的语法,但是你可以看到我想要实现的目的而不需要使用临时表,因为这意味着需要在30个不同的服务器上进行维护,每个服务器上有3到5个数据库。

Smink - Tried your solution and got the following results...

Smink - 试过你的解决方案并得到以下结果......

4 个解决方案

#1

Change the line:

换行:

SET @myDesc = 
  EXEC UDSPRBHPRIMBUSTYPESTARTUP @CODE = @myCode, @DESC = @@tempDesc OUTPUT

to

EXEC UDSPRBHPRIMBUSTYPESTARTUP @CODE = @myCode, @DESC = @tempDesc OUTPUT

And you have missed assigning @DESC in the stored procedure.

而且你错过了在存储过程中分配@DESC。

SET @SQL = 
  'SELECT @DESC = clnt_cat_desc FROM ' + @SERVERNAME + 
  '.' + @DBASE + '.dbo.hbl_clnt_cat WHERE inactive = ''N''
  AND clnt_cat_code = ''' + @CODE + ''''

EXECUTE sp_executeSQL @SQL, N'@DESC varchar(255) output', @DESC output

You should then use @tempDesc in the next select:

然后,您应该在下一个选择中使用@tempDesc:

SELECT
  a.clid
  , b.fileid
  , c.usrfullname AS ClientPartner
  , e.usrfullname AS ClientFeeEarner
  , @tempDesc AS ClientPrimaryBusinessType

Also your stored procedure allows for SQL injection around:

您的存储过程也允许SQL注入:

SET @SQL = 
  'SELECT clnt_cat_desc 
     FROM ' + QUOTENAME(@SERVERNAME) + '.' + QUOTENAME(@DBASE) + '.dbo.hbl_clnt_cat
    WHERE inactive = ''N''
      AND clnt_cat_code = @CODE'

EXECUTE sp_executeSQL @SQL, N'@CODE CHAR(5)', @CODE

(Update: Fixed SQL Injection issues.)

(更新:修复了SQL注入问题。)

#2

Pheww, I was going crazy on how to do this, I needed to make a result from a stored procedure part of a current query and I was having the hardest time doing this. What I did was wrap Procedure with a function and then return the value and that was it.

Pheww,我对如何做到这一点感到疯狂,我需要从当前查询的存储过程部分做出结果,并且我最难做到这一点。我所做的是使用函数包装过程,然后返回值,就是这样。

#3

You can create a function (instead of a procedure) that returns a table.

您可以创建一个返回表的函数(而不是过程)。

CREATE FUNCTION [dbo].[my_function]
(
   @par2     UNIQUEIDENTIFIER, 
   @par2     UNIQUEIDENTIFIER,
   @par3     UNIQUEIDENTIFIER
)
RETURNS @returntable TABLE 
(
   col1 UNIQUEIDENTIFIER,
   col2 NVARCHAR(50),
   col3 NVARCHAR(50)
)
AS
BEGIN
...
END

#4

If you don't want to touch your PROCEDURE, you can create a FUNCTION that wraps it and use that wrapper function in queries.

如果您不想触摸PROCEDURE,可以创建一个包装它的FUNCTION并在查询中使用该包装函数。