Posts Tagged ‘parameter’

In this example we will be using a Tablix as the objects but really it could be a Matrix, Graphs, Textbox etc.

Assume a scenario. In Tablix A (on the left hand side) we have a list of fruits :
Melons and Apples

If we click on Melons then on a second Tablix B (on the right hand side) we will display the colours Yellow and Green. If apples is selected then we will show Red and Green.

Create a Parameter called DrillThrough of type Text and make the visibility Hidden.

Goto the textbox property of the cell holding Tablix A’s values.
This is where the magic happens:
Select Action.
From there select “Go to report”
Under the Specify a Report select the name of the report you are currently on (this is so that it refer’s back to itself).
Then click the “Add” parameter button.
Select the parameter DrillThrough and under value select the field from Tablix A.

You can now test if you wanted using a text box by creating a text box and in the expression put in =Parameters!DrillThrough.Value
Delete the text box if yout testing is complete.

Finally create a Dataset where the parameter DrillThrough is used so that depending on what is clicked the list of Fruits in Tablix A that will be sent through to display the list of relevant colours in Tablix B.

Read Full Post »

-- I did not write this function and I do not take the credit for it.
--This function is used to feed in values from the parameter seperated with  commas. Example:

where @week in (select data from split(@WeekDayNumbers, ','))

@WeekDayNumbers = '1,2,3,4,5,6,7'

& nbsp;



ALTER FUNCTION [dbo].[Split] (
	@RowData NVARCHAR(max)
	,@SplitOn NVARCHAR(5)
RETURNS @RtnValue TABLE -- if the variable to be returned is mentioned then it does not need to mentioned again after the return key word
	(Data NVARCHAR(100))
	WHILE (Charindex(@SplitOn, @RowData) & gt;0)
		INSERT INTO @RtnValue (data)
		SELECT Data = ltrim(rtrim(Substring(@RowData, 1, Charindex(@SplitOn, @RowData) - 1)))

		SET @RowData = Substring(@RowData, Charindex(@SplitOn, @RowData) + 1, len(@RowData))

	INSERT INTO @RtnValue (data)
	SELECT Data = ltrim(rtrim(@RowData))

	RETURN -- this returns the current content of what is mentioned above (i.e. RETURNS @RtnValue table)

Read Full Post »


Table parameter in SQL 2008

You just can't create a table input/output parameter like the below:

create proc dbo.usp_ReturnTable
@WeekDays as table
WeekDayNumber int,
WeekDayName varchar(12)

To get this to work a user defined (table) type needs to be declared.
(ID as int)

There are two way to call an output paramer createed lke this: create proc usp_test @calledProcParam int
1) declare @holder int
exec mySP @holder OUTPUT

2) declare @param int
exec mySP @calledProcParam = @holder OUTPUT -- @parameter = @variable
CREATE type MyTableParam AS TABLE (
	WeekDayNumber INT
	,WeekDayName VARCHAR(12)

CREATE PROCEDURE dbo.usp_ReturnTable @ResultSet MyTableParam OUTPUT
SET DATEFIRST 1 -- Monday is set to be the first day of the week


SET @c = 1

WHILE (@c & lt;= 7)
	INSERT @ResultSet
	SELECT datepart(weekday, dateadd(day, @c, getdate())) WeekDayNumber
		,datename(weekday, dateadd(day, @c, getdate())) WeekDayName

	SET @c = @c + 1

--insert into @ResultSet
--select * from @WeekDays order by 1


declare @FinalOutput MyTableParam
exec usp_ReturnTable @FinalOutput OUTPUT

drop type MyTableParam
drop proc usp_ReturnTable


Since TVP OUTPUT is not supported (becuase it is READONLY)
global temporary tables can be created and distributed.

--link: http://social.technet.microsoft.com/Forums/en/transactsql/thread/45dc071e-e067-45de-b5a6-d907eac1455f


As per your requirement in your other thread try the following:
USE tempdb;

--creating a sproc for a sample result set
SELECT TOP 10 [name]
FROM sys.objects

--reuse the result set from another sproc
	,[id] INT

EXEC SomeSproc;


--run the sproc
EXEC ReuseOtherSPResultSetTest

Please also note that the TVP canot be used with the OUTPUT as others wrote in the other thread.

I hope it helps.

Thanks "Janos Berke [MNI]"

That is a great idea but the only prblem is that the each of the different stored procedure returns different number of columns.

I will still try to incorporate the idea by creating a table in tempdb each time the SPs are run.

e.g. of creation of the temp table inthe SP from where you want to extract columns/resultset:
		FROM tempdb.dbo.sysobjects
		WHERE type = 'u'
			AND NAME = '##myTempTable'
	DROP TABLE ##myTempTable

CREATE TABLE ##myTempTable (
	Dimension1 VARCHAR(100)
	,Measure1 FLOAT

INSERT INTO ##myTempTable
SELECT EmergencyType
FROM @FirstDataset

FROM ##myTempTable

Read Full Post »



When a stored procedure is already in production and some part of your application needs to send additional parameters
to enhance the result from the procedure, it is always advisable to use default parameters, so that other part of the
application that is happily using this procedure with the existing parameters will not be affected by this change.
You are saved from the headache of finding out all the code that uses this procedure and sending dummy values when
it is not necessary.

Create Procedure DefaultParamProcedure ( @Param1 int,
@Param2 varchar(20),
@Param3 int = NULL)

Calling the above procedure from another procedure
ExeDefaultParamProcedure 1,’xyz’c
It is always advisable to use the default parameters at tail of the parameter list so that the actual parameters supplied are passed to the relevant formal parameters. In case if you want to send values to the trailing default parameters and let the leading parameters to use the default values use formal parameter names to assign values to them.

Create Procedure DefaultParamProcedure ( @Param1 int,
@Param2 varchar(20) = ‘xyz’,
@Param3 int = NULL)

Calling the above procedure from another procedure
Exec DefaultParamProcedure 1, @Param3 = 5
This will assign ‘1’ to @Param1 and ‘5’ to @Param3 and @Param2 will use the default value ‘xyz’ assigned to it.
An interesting point is, even the “Output” parameters can have default values. In some cases you may use output parameters to get some values from the stored procedure apart from the result set. For example, the number of records returned by the procedure. Not all the scenarios will need that data. In that case you can simply omit the use of that output parameter by using default values.


Read Full Post »

SSRS does not allow parameters to dynamically hide and unhide (unless asp scripting/xml/url changing/other scripting outside BIDS is implemented).

A workaround around could be to keep all the parameters visible and instead of trying to hide the ones not needed just make a single value available.

Let me elaborate:

1) @Par [text type] > the parameter you want the the dynamic effect applied to it

2) “usp_dp” > if the parameter is a multi valued then lets say this is the name of the SP it generates the values from. This SP also needs to have a parameter of its own that will take an integer value input. Lets call it @ID.

3) @Switch [text type] > if this is equal “A” then you want to use @Par else not.

You will need:
1) Another paramter (either existing or a new one) that accepts integer. I usually use an existing parameter that accepts a certain ID number. For this example lets say we call it @ID (this parameters is of same tyep and name as in usp_dp).

2) On BIDS 2008 on the “Report Parameter Properties” goto the “Advanced” tab and select “Always refresh” or do the same for any other versions. – This step is optional but recommended.

We will first consider that its a multi-valued parameter:
1) Goto the “Dataset Properties” of “usp_dp” and then to “Parameters” and on for @ID Parameter Name put the below “Parameter Value:


What the above does is:
Depending on the value of @ID it will generate the list available for @Par to display. So if you don’t want to display any values you have to say that @ID = 0.

Finally what you need to do is write your “usp_dp” script in such a way that when ever @ID =0 the script should return “N/A” else it will return the normal values for the parameters.

I am sure if you have worked out the multivalued parameter then working out how to apply a similar effect to a textbox parameter (where you need to type in your value) will not be a problem. My suggestion is to use the “Default Values” option in the “Report Parameter Properties”.

Read Full Post »