Getting error trying to create stored procedure using type table

Question

Trying to create a stored procedure like this:

CREATE PROCEDURE Students   
    @List StudentList READONLY,    
    @AdmissionId VARCHAR(max) 
AS
BEGIN   
    INSERT INTO RcordsTable (StudentId, RollNumber, AdmissionId)
    VALUES(@List.StudentId, @List.RollNumber, @AdmissionId)
END

Where List is type valued table which is already created in the database with 3 columns StudentList, RollNumber and Id.

When I try to create this procedure in SQL Server 2008, I am getting following error:

Must declare the scalar variable @List

marc_s · Accepted Answer · 2013-08-30 15:17:37Z

You're working in T-SQL - not in C# here! The @List parameter needs to be treated like a T-SQL table variable - not like a C# object....

So you need to use T-SQL style statements - like SELECT from the table-valued parameter @List - not C# style "dot-notation" (like when accessing the fields of a .NET object):

CREATE PROCEDURE Students   
    @List StudentList READONLY,    
    @AdmissionId VARCHAR(max) 
AS
BEGIN   
    INSERT INTO RcordsTable (StudentId, RollNumber, AdmissionId)
        SELECT
            StudentId, RollNumber, @AdmissionId
        FROM @List
END

Side note: why is an AdmissionId defined as a Varchar(max) type?? Id sounds numeric to me --> use an appropriate numeric type. And if it's not numeric - then you should define and use a sensible length for the VARCHAR - don't just get overly lazy and use VARCHAR(MAX) for everything - it's not a good idea!

Read this excellent article for more details on that topic: What's the Point of Using VARCHAR(n) Anymore?

Collectives™ on Stack Overflow

Getting error trying to create stored procedure using type table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related