Post reply

using table type as input parameter to stored procedure

  • February 24, 2010 at 4:05 pm

    #217111

    I have created a table data type in my database. Next, I want to use the data type as an input parameter in a stored procedure.

    create procedure MyProc (

    @myTable myTableType)

    AS

    ...

    In the above example the @myTable input parameter is a required parameter. If I change the TSQL to:

    create procedure MyProc (

    @myTable myTableType = NULL)

    AS

    ...

    I get an orphan type clash error. I am assuming this is due to NULL is not an acceptable value for a table data type.

    Sooo, how do you declare @myTable as an optional input parameter in a stored procedure?

  • February 24, 2010 at 9:26 pm

    #1124739

    you have to Use That as Read Only Parameter

    Read the Below Article

    http://www.sqlservercentral.com/articles/SQL+Server+2008/67550/

    Thanks

    Deepak.A

  • February 25, 2010 at 8:29 am

    #1125031

    Thanks Grasshopper,

    The READONLY implies the parameter is optional and in the BOL it isn't stated directly. So now I have the following:

    create procedure MyProc (

    @myTable myTableType READONLY)

    AS

    ...

    Now I have an optional parameter of type TABLE in my stored procedure, how do I test to see if the parameter is passed to the stored procedure. In that....

    My procedure can be called two ways:

    1. exec MyProc

    2. exec MyProc [@table]

    Normally in a procedure if the execution path splits based on the optional input parameter I would insert a test such as this in the TSQL of the stored procedure:

    If (@optionalInputParam is null)

    -- do something

    else

    -- do something else

    (begin and end keywords left out for brevity)

    This does not work when the parameter is a table data type.

    So, how would you test for a null table?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply