SQL Server Table Valued Parameters

Question

I am attempting to create a table valued parameter for input into an MS SQL Server stored proc. My create statement:

CREATE TYPE dbo.tvt_AbusedBy AS TABLE  
( Assessment_Behavorial_AbusedID int, Assessment_BehavorialID int,
  Ref_Abuse_TypeID int, Abuser_Name varchar(50), GenderID int,
  Relationship_TypeID int, Age int)

When attempting to add as a parameter into the proc:

CREATE PROCEDURE [dbo].[qryAddUpdateMultipletblAssessment_Behavorial_Abused] 
@prm_Assessment_Abused AS dbo.tvt_AbusedBy READONLY,

I get an error reading "The parameter @prm_Assessment_Abused cannot be declared READONLY since it is not a table valued parameter".

It does not seem to recognize it as a table valued parameter.

If I remote the READONLY stipulation, it gives me an error "Parameter or variable @prm_Assessment_Abused has an invalid type.

Must be an issue with how I am attempting to create the table valued parameter type.

Any ideas?

Try taking out AS in the parameter declaration.

Tab Alleman
– Tab Alleman

2018-11-28 14:47:06 +00:00
Commented Nov 28, 2018 at 14:47 — Tab Alleman
– Tab Alleman, Commented Nov 28, 2018 at 14:47
remove dbo. from table value paramneter name

Ravi
– Ravi

2018-11-28 15:32:55 +00:00
Commented Nov 28, 2018 at 15:32 — Ravi
– Ravi, Commented Nov 28, 2018 at 15:32

Tab Alleman · Accepted Answer · 2018-11-28 14:49:12Z

2

Don't use AS when adding parameters in a stored procedure declaration:

CREATE PROCEDURE [dbo].[qryAddUpdateMultipletblAssessment_Behavorial_Abused] 
  @prm_Assessment_Abused dbo.tvt_AbusedBy READONLY

answered Nov 28, 2018 at 14:49

Tab Alleman

31.8k7 gold badges41 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kiryl Khadanovich · Accepted Answer · 2018-11-28 14:51:38Z

0

From MS documentation - https://learn.microsoft.com/en-us/sql/t-sql/statements/create-procedure-transact-sql?view=sql-server-2017 You don't need AS before parameters, use it afters parameters declaration.

Example from link (a little edited):

CREATE PROCEDURE dbo.usp_add_kitchen 
@dept_id int, @kitchen_count int NOT NULL  
AS  
BEGIN
  UPDATE dbo.Departments  
  SET kitchen_count = ISNULL(kitchen_count, 0) + @kitchen_count  
  WHERE id = @dept_id  
END;  
GO

answered Nov 28, 2018 at 14:51

Kiryl Khadanovich

4207 silver badges11 bronze badges

Collectives™ on Stack Overflow

SQL Server Table Valued Parameters

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related