Error when trying to pass TVP into stored procedure

Transact-SQL
1

I am basically trying to delete rows related to certain IDs from many tables by creating a stored procedure and pass a list of parameters into the stored procedure

CREATE TYPE [dbo].[LicenseList] AS TABLE(
    [License] [uniqueidentifier] NULL
)
GO

Create PROCEDURE [dbo].[DeleteLicense]
    /* Add the parameters for the stored procedure here */
    (@LicenseId LicenseList READONLY)
AS
BEGIN
    /* SET NOCOUNT ON added to prevent extra result sets from
       interfering with SELECT statements. */
    SET NOCOUNT ON;
    
    /* get client id */
    DECLARE @ClientId uniqueidentifier,
            @UserId uniqueidentifier,
            @ApplicationName varchar(256),
            @UserName varchar(256),
            @NumTablesDeletedFrom int,
            @Message varchar(500),
            @Result int
    
    SELECT @ClientId = ClientId FROM License WHERE LicenseId = @LicenseId

    /* DELETING entities: */
    
    /* licensed modules */
    DELETE FROM LicenseModule WHERE LicenseId = @LicenseId

    /* license features */
    DELETE FROM LicenseFeature WHERE LicenseId = @LicenseId
    
    /* team users */
    DELETE FROM dbo.TeamUser WHERE TeamID IN (SELECT TeamId FROM Teams WHERE ClientID = @ClientId)
    
    /* teams */
    DELETE FROM Teams WHERE ClientID = @ClientId
    
    /
    /* user infoes */
    DECLARE UserInfoCursor CURSOR FOR
    SELECT UserId FROM UserInfo WHERE ClientId = @ClientId
    OPEN UserInfoCursor
    FETCH NEXT FROM UserInfoCursor INTO @UserId
    WHILE @@FETCH_STATUS = 0
       BEGIN
          SELECT @UserName = aspnet_Users.UserName, @ApplicationName = aspnet_Applications.ApplicationName FROM aspnet_Users
          JOIN aspnet_Applications ON aspnet_Users.ApplicationId = aspnet_Applications.ApplicationId WHERE aspnet_Users.UserId = @UserId
          
          DELETE FROM UserInfo WHERE UserId = @UserId
          
          EXEC @Result = dbo.aspnet_Users_DeleteUser 
                 @ApplicationName = @ApplicationName,
                 @UserName = @UserName,
                 @TablesToDeleteFrom = 15,
                 @NumTablesDeletedFrom = @NumTablesDeletedFrom OUTPUT
          
                                  
          FETCH NEXT FROM UserInfoCursor INTO @UserId;
       END;
    CLOSE UserInfoCursor;
    DEALLOCATE UserInfoCursor;
    
    /* Transaction Counters */
    DELETE FROM TransactionCounter WHERE ClientID = @ClientId
        
    /* client */
    DELETE FROM Client WHERE ClientID = @ClientId
    
    /* License */
    DELETE FROM License WHERE LicenseId = @LicenseId
    
END;

GO

DECLARE @LicenseId LicenseList;

/*Capture the licenseID as a table*/
INSERT INTO @LicenseId (License)
SELECT DISTINCT LicenseId
FROM License
WHERE EmailId NOT LIKE '%healthplus.org%' or EmailId NOT LIKE '%burgessgroup.com%';


/*Execute*/
EXEC [dbo].[DeleteLicenseAndModelAndEWR] @LicenseId = @LicenseId;
GO

but i get the below error

Msg 137, Level 16, State 1, Procedure DeleteLicense, Line
Must declare the scalar variable "@LicenseId".
Msg 137, Level 16, State 1, Procedure DeleteLicense, Line
Must declare the scalar variable "@LicenseId".
Msg 137, Level 16, State 1, Procedure DeleteLicense, Line
Must declare the scalar variable "@LicenseId".
Msg 137, Level 16, State 1, Procedure DeleteLicense, Line
Must declare the scalar variable "@LicenseId".

(25 row(s) affected)
Msg 2812, Level 16, State 62, Line 12
Could not find stored procedure 'dbo.DeleteLicense'.

Is it because I am trying to pass the @licenseID as a variable but i have declared it prior to that?

2

remove the parentheses around the parameter(s). Syntax differs between functions and procs

3

You are using @LicenseID as a scalar variable. But it is not, it is a table variable, so you have to use it just like you would use any other table. For example, you cannot do this:

DELETE FROM LicenseModule WHERE LicenseId = @LicenseId

Instead, perhaps you should do something like this

DELETE FROM LicenseModule WHERE LicenseId in (SELECT License FROM @LicenseId);

Also, statements such as the following would just not be correct even if you fixed the WHERE clause as in the example I showed above.

SELECT @ClientId = ClientId FROM License WHERE LicenseId = @LicenseId

The reason is that you are assigning results to a scalar variable (@ClientID), but there may be several ClientID's that are returned based on the data in the @LicenseID table.