Блог

Я создал следующую хранимую процедуру..

 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
 

Эта процедура используется во многих различных базах данных и на многих различных серверах и написана как динамический SQL для упрощения обслуживания. Процедура также выполняется на сервере, отличном от того, на который указывает процедура.

Я хочу использовать выходные данные этой процедуры в качестве значения в таблице…

 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
 

Я знаю, что это неверный синтаксис, но вы можете видеть, чего я пытаюсь добиться, не прибегая к временным таблицам, так как это означало бы обслуживание на 30 различных серверах с 3-5 базами данных на каждом.

Сминк — Попробовал ваше решение и получил следующие результаты…

Измените строку:

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

Для

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

И вы пропустили назначение @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
 

Затем вы должны использовать @tempDesc в следующем выборе:

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

Кроме того, ваша хранимая процедура позволяет выполнять 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
 

(Обновление: Исправлены проблемы с внедрением SQL.)

Фу, я сходил с ума от того, как это сделать, мне нужно было сделать результат хранимой процедуры частью текущего запроса, и мне было труднее всего это сделать. То, что я сделал, — это обернул процедуру функцией, а затем вернул значение, и все.

Вы можете создать функцию (вместо процедуры), которая возвращает таблицу.

 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
 

Если вы не хотите касаться своей ПРОЦЕДУРЫ, вы можете создать ФУНКЦИЮ, которая ее обертывает, и использовать эту функцию-оболочку в запросах.