locked
Using User Defined Table Types... RRS feed

  • Question

  • Hello,

    In this link it states that "You can use a user-defined table type to declare table-valued parameters for stored procedures or functions, or to declare table variables that you want to use in a batch or in the body of a stored procedure or function". I'm trying to create a dummy procedure like the one in the following;

    USE [MYDB]
    
    GO
    
    /****** Object: StoredProcedure [dbo].[sp_DummyProc] Script Date: 03/25/2011 09:13:28 ******/
    
    SET ANSI_NULLS ON
    
    GO
    
    SET QUOTED_IDENTIFIER ON
    
    GO
    
    
    
    CREATE PROCEDURE [dbo].[sp_DummyProc]
    
    	
    
    AS
    
    BEGIN
    
    	DECLARE @MyTable MyTableType
    
    END
    
    
    
    
     
    
    
    But this gives me the error ;

    "Msg 2715, Level 16, State 3, Procedure sp_DummyProc, Line 2

    Column, parameter, or variable #1: Cannot find data type MyTableType.

    Parameter or variable '@MyTable' has an invalid data type.

    "

    So what's the problem here? First of all MyTableType is not a "data type". Also this link gives an example for declaring a parameter of type user defined table type with only one exception: the example uses the "declare" statement in batch query I suppose; not in the body of a procedure; however, msdn documentation states that we can declare user defined table type in the body of the procedure:

    But How? Can anyone help me about this problem?


    Friday, March 25, 2011 8:20 AM

Answers

  • Check also compatibility mode of the database. Alexey's code worked fine for me. Are you sure you defined this type in the right database?


    For every expert, there is an equal and opposite expert. - Becker's Law

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog

    • Marked as answer by Kalman Toth Friday, March 25, 2011 5:46 PM
    Friday, March 25, 2011 2:03 PM
  • you're fine for the version.

    for the DB version, get the properties of the DB then "option" and check the "compatibility level"

    • Marked as answer by rebulanyum Friday, March 25, 2011 3:14 PM
    Friday, March 25, 2011 2:43 PM

All replies

  • Hi,

    Though i don't think i understood ur question fully I see some problems in ur sp. U declared a parameter "@MyTabl" but didn't specify it's datatype.

    CREATE PROCEDURE [dbo].[sp_DummyProc]
    AS
    BEGIN
        DECLARE @MyTable table
        (id int);
    END

    Above statement works fine. Here "@MyTable" is the parameter and "table" is the datatype. A table "id" is one column of the table.
    Friday, March 25, 2011 8:51 AM
  • create type MyTableType as table (val1 int, val2 int)
    go
    
    CREATE PROCEDURE [dbo].[sp_DummyProc]
    as
    BEGIN
    
    	DECLARE @MyTable MyTableType
    
    END
    
    

    http://www.t-sql.ru
    Friday, March 25, 2011 8:52 AM
  • This didn't worked for me; and I'm sure that I'm using SQL Server 2008 R2.

    To SanandanBK: I've already created my table type "MyTableType" with this script:

    USE [MYDB]
    GO
    
    /****** Object: UserDefinedTableType [dbo].[MyTableType]  Script Date: 03/25/2011 11:04:33 ******/
    CREATE TYPE [dbo].[MyTableType] AS TABLE(
    	[ItemCode] [int] NULL,
    	[View_Right] [bit] NULL,
    	[Insert_Right] [bit] NULL,
    	[Update_Right] [bit] NULL,
    	[Delete_Right] [bit] NULL,
    	[PerformanceResult_Right] [bit] NULL
    )
    GO
    
    
    
    Friday, March 25, 2011 9:07 AM
  • CREATE PROCEDURE [dbo].[sp_DummyProc]
    AS
    BEGIN
    	DECLARE @MyTable dbo.MyTableType
    END
    
    and show: select @@version
    http://www.t-sql.ru
    Friday, March 25, 2011 9:25 AM
  • Result of "select @@version":

    Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86) Apr 2 2010 15:53:02 Copyright (c) Microsoft Corporation Developer Edition on Windows NT 6.1 (Build 7600: )

    Friday, March 25, 2011 9:29 AM
  • Check also compatibility mode of the database. Alexey's code worked fine for me. Are you sure you defined this type in the right database?


    For every expert, there is an equal and opposite expert. - Becker's Law

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog

    • Marked as answer by Kalman Toth Friday, March 25, 2011 5:46 PM
    Friday, March 25, 2011 2:03 PM
  • Works for me too
    If you want it as an input parameter:

    CREATE PROCEDURE [dbo].[sp_DummyProc]
    (
    @MyTable MyTableType READONLY
    )AS
    BEGIN
    	-- Code
    END
    
    Friday, March 25, 2011 2:15 PM
  • User defined table data type only works for SQL 2008 or later. Check if your SQL server version is compatible? Use:

     

    Select @@version

    Friday, March 25, 2011 2:26 PM
  • User defined table data type only works for SQL 2008 or later. Check if your SQL server version is compatible? Use:

     

    Select @@version


    Result of "select @@version":

    Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86) Apr 2 2010 15:53:02 Copyright (c) Microsoft Corporation Developer Edition on Windows NT 6.1 (Build 7600: )

    Is SQL SERVER 2008 R2 (RTM) 's version less then SQL SERVER 2008 ?

    To Naomi N: I don't know the compability mode of the database? How can I learn it?

    The "DECLARE @MyTable dbo.MyTableType" statement is definately not working.

    Friday, March 25, 2011 2:39 PM
  • you're fine for the version.

    for the DB version, get the properties of the DB then "option" and check the "compatibility level"

    • Marked as answer by rebulanyum Friday, March 25, 2011 3:14 PM
    Friday, March 25, 2011 2:43 PM
  • To get the compatibility mode of the database 

    select name, compatibility_level from sys.databases

    You can also find it this way: right click on the Database, Properties, Options and look at the very top of this screen right below the Recovery Model.

    The version you're using is the latest version of SQL Server (SQL Server 2008 R2)

    My build is

    Microsoft SQL Server 2008 R2 (RTM) - 10.50.1746.0 (X64)   Oct  5 2010 12:32:09   Copyright (c) Microsoft Corporation  Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1), so you probably are using RTM and I installed CU4 (I believe). Right now CU6 is available, which you may want to install, or wait a bit for the next CU for SQL Server 2008 R2 which is supposed to fix Intellisense problem with VS 2010 SP1.


    For every expert, there is an equal and opposite expert. - Becker's Law

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog



    Friday, March 25, 2011 2:45 PM
  • you're fine for the version.

    for the DB version, get the properties of the DB then "option" and check the "compatibility level"

    thank you: that was the problem ; the database version is fine but compatibility level is SQL SERVER 2005. that' s probably why it's not working
    Friday, March 25, 2011 3:15 PM