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:
@accountID is an int
I need to merge everything received into one table, so that it ends up looking like so:
@temporaryTable:
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?
When using CREATE TABLE , you can specify default values for columns by typing DEFAULT and then the desired value after it. If a row is inserted that does not specify a value for that column, the database will fill it in with the default value instead.
A column's default value is part of its definition, but can be modified separately from other aspects of the definition. To change a default value, use ALTER col_name SET DEFAULT : ALTER TABLE mytbl ALTER j SET DEFAULT 1000; Default values must be constants.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With