locked
Table Valued Parameters (TVP) RRS feed

  • Question

  • hi all;

    this is new to me but i want to have a TVP to accept list of records as a parameter of a stored procedure. the following is the code i have written.

    CREATE TABLE dbo.GroupRole
    (
    userRole VARCHAR(100) NOT NULL,
    CONSTRAINT GroupRole_Pk PRIMARY KEY (userRole)
    );


    CREATE TYPE usrRoleArr AS TABLE (usrRole VARCHAR(100) PRIMARY KEY);

    CREATE PROCEDURE grpRoleRecorder (@userRole usrRoleArr READONLY)
    AS
    BEGIN
     INSERT INTO dbo.GroupRole VALUES (@userRole);
    END

    when i execute the code it gives the folling error.

    Msg 137, Level 16, State 1, Procedure grpRoleRecorder, Line 4
    Must declare the scalar variable "@userRole".

    Please help me on this.

    Thank You All
     

    Saturday, September 25, 2010 1:57 PM

Answers

  • insert statement should be like this... u need to treat it as a normal table and needs to select from that..

    INSERT INTO dbo.GroupRole 
     select * from @userRole
    

    Saturday, September 25, 2010 2:10 PM
  • You were very close, but in the INSERT statement you can not use VALUES when you insert from a table.

    insert into dbo.GroupRole (usrRole) select * from @UserRole


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Sunday, September 26, 2010 1:36 AM

All replies

  • insert statement should be like this... u need to treat it as a normal table and needs to select from that..

    INSERT INTO dbo.GroupRole 
     select * from @userRole
    

    Saturday, September 25, 2010 2:10 PM
  • You were very close, but in the INSERT statement you can not use VALUES when you insert from a table.

    insert into dbo.GroupRole (usrRole) select * from @UserRole


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Sunday, September 26, 2010 1:36 AM
  • THANK YOU ALL FOR YOUR FEED BACK. Now its ok

    Sunday, September 26, 2010 3:25 AM