CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
user471849
user471849

Reputation:

Insert User Defined Data Table in a stored procedure

Below is the stored proc i have so far I keep getting this error when executed: Msg 137, Level 16, State 1, Procedure db_recession_band_dates_save, Line 18 Must declare the scalar variable "@dates". 

ALTER PROCEDURE [dbo].[Dates_Save]
@Loc VARCHAR(75),
@dates StartEndDateType READONLY  
AS
BEGIN
DECLARE @id int
SELECT @id = MYINTFIELD FROM date_locations
IF @id IS NULL
BEGIN
    INSERT INTO db_recession_bands VALUES (@loc)
    SET @id = @@IDENTITY
END
INSERT INTO db_recession_band_dates VALUES (@id,@dates)      
END

Upvotes: 1

Views: 3983

Answers (1)

Transact Charlie
Transact Charlie

Reputation: 2203

if StartEndDateType is a user defined table type then you treat it as if it were a table.

Change this:

INSERT INTO db_recession_band_dates VALUES (@id,@dates)

Into something like

INSERT INTO db_recession_band_dates (<COLUMN LIST>) -- don't do blind inserts it will hurt you at some point in the future
SELECT @id, <COLUMN LIST>
FROM @dates

Upvotes: 1

Related Questions