Post reply

passing multiple parameters with table valued parameter to stored procedure

  • May 21, 2014 at 11:43 pm

    #308368

    Hi All,

    Can we Pass table valued parameters and normal params like integer,varchar etc..to a single stored procedure?

    Thanks in advance

    🙂

  • May 22, 2014 at 7:55 am

    #1715702

    Yes you can:

    --Create table parameter type

    CREATE TYPE dbo.TableParamTest AS TABLE

    (i INT, string VARCHAR(255));

    --Create stored procedure

    CREATE PROCEDURE dbo.test

    (

    @tableparam TableParamTest READONLY ,

    @j-2 INT ,

    @strjng VARCHAR(255)

    )

    AS

    BEGIN

    SELECT *

    FROM @tableparam

    SELECT @j-2

    SELECT @strjng

    END

    go

    --test Execution of SP

    DECLARE @testtable TableParamTest

    INSERT INTO @testtable

    VALUES ( 1, 'test' ),

    ( 2, 'test2' )

    EXEC dbo.test @tableparam = @testtable, @j-2 = 45, @strjng = 'teststring'


    [font="Tahoma"]Personal blog relating fishing to database administration:[/font]
    [font="Comic Sans MS"]https://davegugg.wordpress.com[/url]/[/font]

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

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