passing multiple parameters with table valued parameter to stored procedure

• nikku

  Ten Centuries

  Points: 1103

  More actions

  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

  🙂

• david.gugg

  SSCertifiable

  Points: 6193

  More actions

  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 INT ,

  @strjng VARCHAR(255)

  )

  AS

  BEGIN

  SELECT *

  FROM @tableparam

  SELECT @j

  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 = 45, @strjng = 'teststring'

  Be still, and know that I am God - Psalm 46:10