none
Table valued parameter

    Question

  • Hi,

    I am trying to create a stored procedure that takes a table valued parameter. I have followed some example which i though was quite easy...but am having an issue. I'll write in small steps what i did ...and can anyone tell me wat is wrong...

    step : i created a table type as follows

    CREATE TYPE [dbo].[orderlinesTableType] AS TABLE(
        [prodId] [bigint] NULL,
        [orlQuantity] [int] NULL,
        [ordId] [bigint] NULL
    )

     

    step 2: i am writing a procedure that will take a table valued parameter...and will insert data into orders table...

    CREATE PROCEDURE insert (@tvp orderlinesTableType READONLY)

    AS

    BEGIN

    INSERT INTO orderlines(prodid,orlquantity,ordId) values

    SELECT prodId,orlQuantity,ordId

    from @tvp

    END

     

    Issues: So the issue am having is that the first line (which i have underlined)...is being underlined in red...and as error it says :Parameter or variable @tvp has an invalid type.

    Can anyone tell me where i went wrong with the syntax ?

    Regards,

    Shaimaa

    Saturday, October 08, 2011 6:43 PM

Answers

  • The IntelliSense code is not always completely accurate.  For example, what I believe is going on here is that IntelliSense does not notice that you are defining a new tabletype, so it thinks you will get an error because the type doesn't exist.  But if you run the code, the type is created and then the procedure should be created without error.  If you get a red line in SSMS and can't find the error,. you can try running the code and see if you get an error. 

    If you do that here, using Dan's syntax, it should work.  If it doesn't let us know what error(s) you get and the actual code you ran.

    Tom

    • Marked as answer by KJian_ Monday, October 17, 2011 8:21 AM
    Saturday, October 08, 2011 7:29 PM

All replies

  • There are a couple of issues with the CREATE PROCEDURE script.  One is that "insert" is a keyword that shouldn't be used as the proc name and the other is that the VALUES clause should not be used with the INSERT...SELECT syntax.  Try the script below:

     

    CREATE TYPE [dbo].[orderlinesTableType] AS TABLE(
         [prodId] [bigint] NULL,
         [orlQuantity] [int] NULL,
         [ordId] [bigint] NULL
     )
    GO
     
    CREATE PROCEDURE dbo.usp_insert_orderlines (@tvp orderlinesTableType READONLY)
    AS
    BEGIN
     
    INSERT INTO dbo.orderlines(prodid,orlquantity,ordId)
    SELECT prodId,orlQuantity,ordId
    FROM @tvp;
     
    END
    GO


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Saturday, October 08, 2011 6:51 PM
  • Hi Dan,

    Thanks for the quick reply...In fact my procedure name was not Insert...it was spInsertOrderlines...just to cut short that i wrote Insert here.

    and i have used ur syntax...but still same issue.

    when i mouse over on READONLY keyword, am having another error msg which might help to give a solution to this,

    "the parameter @tvp cannot be declared as readonly since it is not a table valued parameter"

     

    I really cn't understand wats wrong since every example i'm seeing has this syntax.

    Any suggestion plz?

    Saturday, October 08, 2011 7:06 PM
  • Did you actually try running the script is it only an intellesense error?  I succesfully ran the script on SQL 2008 R2 but I would expect it to run on SQL 2008 as well.

     

     

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Saturday, October 08, 2011 7:26 PM
  • The IntelliSense code is not always completely accurate.  For example, what I believe is going on here is that IntelliSense does not notice that you are defining a new tabletype, so it thinks you will get an error because the type doesn't exist.  But if you run the code, the type is created and then the procedure should be created without error.  If you get a red line in SSMS and can't find the error,. you can try running the code and see if you get an error. 

    If you do that here, using Dan's syntax, it should work.  If it doesn't let us know what error(s) you get and the actual code you ran.

    Tom

    • Marked as answer by KJian_ Monday, October 17, 2011 8:21 AM
    Saturday, October 08, 2011 7:29 PM
  • What is your SQL Server version and what is the compatibility level of the database?
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Sunday, October 09, 2011 3:20 AM
    Moderator
  • In addition to Tom's post, Under the Edit menu there is an Intellisense submenu, and there is one command Refresh Local Cache which I believe resolves this issue.

    Personally, I always have Intellisense turned off.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, October 09, 2011 4:33 PM