November 7, 2012 at 3:30 pm
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....
November 7, 2012 at 5:54 pm
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....
November 7, 2012 at 6:13 pm
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);November 7, 2012 at 6:29 pm
Thank you mister.magoo
Below is working code....
I'll follow back up when/if i can get this working in my ssrs report.
I'll create a datset set called cities in my report setting its source to
DECLARE @ParametersTVP AS testType
INSERT INTO @ParametersTVP (Parameter,ParameterType,ParameterValue)
SELECT 'param2','Main','param1' UNION
SELECT 'param2','Country',@Country --@Country will refer to a previously set parameter.
EXEC dbo.uspMasterParameters @ParametersTVP
I'll create a ssrs parameter called @Cities setting its source to the dataset cities.
Ideally I'll have consolidated logic for ssrs parameter reuse.
--sample data
CREATE TABLE Cities
(
City varchar(20),
Country varchar(20)
)
INSERT INTO Cities
SELECT 'Mexico City', 'Mexico' UNION
SELECT 'Juarez', 'Mexico' UNION
SELECT 'Vancouver', 'Canada'
CREATE TYPE testType AS TABLE
(
Parameter varchar(40),
ParameterType varchar(40),
ParameterValue varchar(100)
);
ALTER 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 @TVP WHERE ParameterType = 'Main')
PRINT @Parameter
IF @Parameter = 'param1'
BEGIN
SELECT 'True' AS TrueFalse
UNION
SELECT 'False' AS ParameterValue
END
IF @Parameter = 'param2'
DECLARE @Country varchar(20)
SET @Country = (SELECT ParameterValue FROM @TVP WHERE ParameterType = 'Country')
BEGIN
SELECT City FROM Cities WHERE Country = @Country
END
DECLARE @ParametersTVP AS testType
INSERT INTO @ParametersTVP (Parameter,ParameterType,ParameterValue)
SELECT 'param1','Main','param1'
EXEC dbo.uspMasterParameters @ParametersTVP
DECLARE @ParametersTVP AS testType
INSERT INTO @ParametersTVP (Parameter,ParameterType,ParameterValue)
SELECT 'param2','Main','param1' UNION
SELECT 'param2','Country','Mexico'
EXEC dbo.uspMasterParameters @ParametersTVP
November 9, 2012 at 8:43 am
This works as expected. I can set the ssrs dataset query properties by declaring the table valued parameter, populating it with previously set ssrs parameters if needed, and then passing it to my master parameter procedure.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply