Create a table and default column value to a received variable parameter

Question

I'm working on a MSSQL stored procedure.

I receive a table valued parameter (@accountPropsTVP) and a single variable (@accountID) from the c# server.

@accountPropsTVP has 2 columns:

• valueTypeID int
• value varchar(max)
• note: i'm never sure how many rows will be in this table

@accountID is an int

I need to merge everything received into one table, so that it ends up looking like so:

@temporaryTable:

• @accountID (always the same for all rows)
• valueTypeID
• value

Below is what I have tried, but I get an error:

Msg 112, Level 15, State 4, Procedure insertAccountProps, Line 20 Variables are not allowed in the CREATE TABLE statement.

    CREATE PROCEDURE insertAccountProps 
    -- Received parameters
    @accountID int,
    @accountPropsTVP accountPropsTVP READONLY

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    -- declare new table
    DECLARE @accountPropsTemp TABLE
    (
        accountID int not null DEFAULT (@accountID),
        valueTypeID int not null, 
        value varchar(max) not null
    )

    -- insert received TVP into new temp table, so that we can manipulate it (tvp's are read only :( )
    INSERT INTO
        @accountPropsTemp
    SELECT 
        *
    FROM 
        @accountPropsTVP


    -- select all from TVP and add it into temp table created above
    INSERT INTO
        dbo.accountsProps
    SELECT
        *
    FROM
        @accountPropsTemp
END
GO

Maybe there's a simpler way of doing this?

Answer 1

Your issue is here:

DECLARE @accountPropsTemp TABLE
    (
        accountID int not null DEFAULT (@accountID),
        valueTypeID int not null, 
        value varchar(max) not null
    )

You're assigning a variable as a default value which as the error message clearly states is not allowed.

The best option for this is to change your syntax to:

DECLARE @accountPropsTemp TABLE
(
    accountID int not null,
    valueTypeID int not null, 
    value varchar(max) not null
)


INSERT INTO
    @accountPropsTemp
SELECT 
    @AccountID
    ,ValueTypeID
    ,Value
FROM 
    @accountPropsTVP