Stored Procedure Fill Variable with a ResultSet of a Query

I have a little Problem but don't know the Solution!

I have wrote a Stored Procedure. The function of this isn't necessary.

I want to declare a Variable from Type int.

This Variable must get the Value of a SQL Query.

My attempt:

DECLARE @ParentServiceProviderId int = null     
SET @ParentServiceProviderId  = (SELECT ParentServiceProviderId 
                                 FROM ServiceProvider
                                 WHERE ServiceProviderId = @ServiceProviderId)

It didn't work! The ResultSet of the Query have one Row every Time!

I don't know how to solve this Problem!

Here is the complete Stored Procedure:

ALTER PROCEDURE [dbo].[InsertCarmakerPartnership_ChildToParent]

    @ServiceProviderId int,
    @CarmakerId int,
    @ValidFrom datetime,
    @ValidTo datetime
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @parentSPPId int, @parentSPPParentId int, @superParentId int, @ParentServiceProviderId int = null       

    SET @ParentServiceProviderId  = (SELECT ParentServiceProviderId 
                                     FROM ServiceProvider
                                     WHERE ServiceProviderId = @ServiceProviderId)      

    DECLARE ServiceProviderParent_Cursor CURSOR FOR

    SELECT ServiceProviderId, ParentServiceProviderId
    FROM ServiceProvider 
    WHERE ServiceProviderId = @ParentServiceProviderId

    OPEN ServiceProviderParent_Cursor;

    FETCH NEXT FROM ServiceProviderParent_Cursor INTO @parentSPPId, @parentSPPParentId 

    IF (@ParentServiceProviderId is NULL)
    BEGIN
        SET @superParentId = @ServiceProviderId
        EXEC InsertCarmakerPartnership_ParentToChild @superParentId, @CarmakerId, @ValidFrom, @ValidTo;
    END

    WHILE @@FETCH_STATUS = 0
BEGIN
    IF @ServiceProviderId > 0
    BEGIN
        EXEC InsertCarmakerPartnership_ChildToParent @parentSPPId, @CarmakerId, @parentSPPParentId, @ValidFrom, @ValidTo  ;

        IF (SELECT COUNT(*) FROM dbo.CarmakerPartnership WHERE ServiceProviderId = @parentSPPId AND CarmakerId = @CarmakerId AND IsDeleted = 0) = 0
        BEGIN                           
            INSERT INTO dbo.CarmakerPartnership (CarmakerId, ServiceProviderId, CreatedBy, ChangedBy, ValidityPeriodFrom, ValidityPeriodTo) VALUES (@CarmakerId, @parentSPPId, SYSTEM_USER, SYSTEM_USER, @ValidFrom, @ValidTo)
       开发者_运维问答 END             
    END 
    FETCH NEXT FROM ServiceProviderParent_Cursor INTO @parentSPPId, @parentSPPParentId 
END;
    CLOSE ServiceProviderParent_Cursor;
    DEALLOCATE ServiceProviderParent_Cursor;
END

Thanks for your help and sorry for my bad english!

Best regards.

Anyway, use next code to populate a variable:

DECLARE @ParentServiceProviderId int

SELECT @ParentServiceProviderId = ParentServiceProviderId 
FROM ServiceProvider
WHERE ServiceProviderId = @ServiceProviderId

do not assign default value to this variable and the rest of the code is:

DECLARE @ParentServiceProviderId int

SELECT @ParentServiceProviderId = ParentServiceProviderId 
                                FROM ServiceProvider
                                WHERE ServiceProviderId = @ServiceProviderId