Sql-server – Create a table and default column value to a received variable parameter

sql-server, stored-procedures

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 READONLYASBEGIN    -- 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        @accountPropsTempENDGO

Maybe there's a simpler way of doing this?

Best Solution

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    @accountPropsTempSELECT     @AccountID    ,ValueTypeID    ,ValueFROM     @accountPropsTVP