Passing a table to a stored procedure as a parameter

• Chrissy321

  SSCoach

  Points: 15788

  More actions

  November 7, 2012 at 3:30 pm

  #269970

  Hello All,

  I have a series of reports, many of the reports have identical parameters lists. I am using the phrase 'parameters lists' here as a series of choices presented to a report user from which the user can make a selection. Yes or No, True or False, a list of countries, a list of cities.

  Right now the parameter list logic is embedded in a reports related stored procedure. This means duplicate logic/code.

  I'd like to create a master parameter procedure that consolidates all my parameters in one place. Then each of my report specific procedures can call the master procedure insuring consistent parameter functionality among all the reports.

  I can create a procedure.

  CREATE PROCEDURE dbo.uspParameters

  @Parameter varchar(40)

  AS

  IF @Parameter = 'param1'

  BEGIN

  SELECT 'True' AS ParameterValue

  UNION

  SELECT 'False' AS ParameterValue

  END

  I can execute this procedure like EXEC dbo.uspParameters 'param1'

  So far so good.

  Some of the parameters may have need additional parameters. Say the user selects a country and then wants to select a city in that country.

  I could alter the procedure

  ALTER PROCEDURE dbo.uspParameters

  @Parameter varchar(40),

  @Country varchar(40)

  then....

  IF @Parameter = 'param2'

  BEGIN

  SELECT City FROM Cities WHERE Country = @Country

  END

  EXEC dbo.uspParameters 'param1','Mexico'

  Could work but I would continually need to add parameters to my procedure breaking any existing calls to my procedure.

  My thought would be to pass a table variable to the procedure. Is this possible or advisable?

  The table structure could be somewhat EAV so I wouldn't have to change the structure as parameters are added.

  DECLARE @Parameters TABLE

  (

  ParameterName varchar(40),

  ParameterType varchar(40),

  ParameterValue varchar(40)

  )

  INSERT INTO @Parameters VALUES ('param1','Main','param1')

  INSERT INTO @Parameters VALUES ('param2','Main','param1')

  INSERT INTO @Parameters VALUES ('param2','Country','Mexico')

  SELECT * FROM @Parameters

  Then I would pass the table variable to my procedure, parse out and assign the parameters and call the relevant part of the master procedure and have the necessary parameters needed for that section.

  Anyone been down this road before....

• Chrissy321

  SSCoach

  Points: 15788

  More actions

  November 7, 2012 at 5:54 pm

  #1556977

  I think I need a table valued parameter (TVP)

  --Declare the type

  CREATE TYPE testType AS TABLE

  (

  Parameter varchar(40),

  ParameterType varchar(40),

  ParameterValue varchar(100)

  );

  --Create a procedure that accepts a TVP

  CREATE PROCEDURE dbo.uspMasterParameters

  @TVP testType READONLY

  AS

  DECLARE @ParametersTVP AS testType

  DECLARE @Parameter varchar(40)

  DECLARE @ParameterType varchar(40)

  DECLARE @ParameterValue varchar(100)

  SET @Parameter = (SELECT Parameter FROM @ParametersTVP WHERE ParameterType = 'Main')

  PRINT @Parameter

  IF @Parameter = 'param1'

  BEGIN

  SELECT 'True' AS ParameterValue

  UNION

  SELECT 'False' AS ParameterValue

  END

  --Populate the TVP and pass it to the procedure

  DECLARE @ParametersTVP AS testType

  INSERT INTO @ParametersTVP (Parameter,ParameterType,ParameterValue)

  SELECT 'param1','Main','param1'

  EXEC dbo.uspMasterParameters @ParametersTVP

  When I execute I expect to get true or false but no luck....

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  November 7, 2012 at 6:13 pm

  #1556980

  Your parameter is called @TVP but you are using the local variable instead in the code....

  @TVP testType READONLY

  AS

  DECLARE @ParametersTVP AS testType

  ...

  SET @Parameter = (SELECT Parameter FROM @ParametersTVP WHERE ParameterType = 'Main')

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]