Insert results from a stored procedure into a table variable

Question

I have a stored procedure that stores values in a table variable. I select these values and return them when the procedure is called.

I am trying to set these return values in another table variable but I can't figure it out.

Stored procedure

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

Calling code

I call the procedure using:

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')

How can I do this?

• Jon of All Trades: Incidentally, @@IDENTITY can give unexpected values, when triggers are involved. SCOPE_IDENTITY() is generally best. More

Answer 1

To insert the results into a table, you just want INSERT....EXEC... not the VALUES part of the query.

In your case, this would look like the following:

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