10

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?

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.