Sql-Server
將儲存過程的結果插入表變數
我有一個將值儲存在表變數中的儲存過程。我選擇這些值並在呼叫過程時返回它們。
我試圖將這些返回值設置在另一個表變數中,但我無法弄清楚。
儲存過程
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';