Sql-Server

將儲存過程的結果插入表變數

  • July 1, 2019

我有一個將值儲存在表變數中的儲存過程。我選擇這些值並在呼叫過程時返回它們。

我試圖將這些返回值設置在另一個表變數中,但我無法弄清楚。

儲存過程

ALTER PROCEDURE [dbo].[GetOrSetDomainId]
@DomainName varchar(50),
@DomainUrl varchar(50)
AS
BEGIN
   DECLARE @DomainId bigint;
   DECLARE @NumberOfRwos bigint;

   DECLARE @DomainHistory TABLE
   (
       DomainId bigint, 
       HasHistory bit,
       ServerOnline bit,
       DatabaseOnline bit, 
       ServerPerformance bigint,
       DatabasePerformance bigint, 
       SoldTickets bigint
   )


   SELECT  @NumberOfRwos =  COUNT(Id) 
   FROM DomainData
   WHERE DomainName = @DomainName OR DomainUrl = @DomainUrl

   IF(@NumberOfRwos = 0)
   BEGIN
       INSERT INTO DomainData (DomainName, DomainUrl) VALUES (@DomainName, @DomainUrl)

        SELECT @DomainId =  @@IDENTITY

        INSERT INTO @DomainHistory(DomainId,HasHistory)VALUES(@DomainId, 0)

        SELECT * FROM @DomainHistory
   END

   ELSE
   BEGIN
   ---not important here----
   END

END

呼叫程式碼

我使用以下方法呼叫該過程:

DECLARE @DomainHistory TABLE
(
   DomainId bigint, 
   HasHistory bit,
   ServerOnline bit,
   DatabaseOnline bit, 
   ServerPerformance bigint,
   DatabasePerformance bigint, 
   SoldTickets bigint
)

SET @DomainHistory = EXEC GetOrSetDomainId 'test', 'test2'
---Other alternatives:---
INSERT INTO @DomainHistory(DomainId,HasHistory)
VALUES(EXEC GetOrSetDomainId 'test', 'test2')

我怎樣才能做到這一點?

要將結果插入表中,您只需要查詢INSERT....EXEC...VALUES部分。

在您的情況下,這將如下所示:

INSERT INTO @DomainHistory(DomainId,HasHistory)
EXEC GetOrSetDomainId 'test', 'test2';

引用自:https://dba.stackexchange.com/questions/132504