How to determine if stor proc parameter is output or input

I know that you can retrieve whether a parameter is for output buy way
of the "isoutparam" field, but is there anything that tells you whether
a parameter is input/output?
thanks(jw56578@.gmail.com) writes:
> I know that you can retrieve whether a parameter is for output buy way
> of the "isoutparam" field, but is there anything that tells you whether
> a parameter is input/output?

The only true output-only value is the return value. All parameters are
input/output. This is T-SQL, not Ada. :-)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I am using a .net Parameter builder, to build paramter objects based
on system information. When it sees a parameter used for input and
output, it "isoutparam" indicates that it is an output, so its
"Direction" attribute is assigned a value of output. But if i want to
use it as an input, it doesn't work.|||(jw56578@.gmail.com) writes:
> I am using a .net Parameter builder, to build paramter objects based
> on system information. When it sees a parameter used for input and
> output, it "isoutparam" indicates that it is an output, so its
> "Direction" attribute is assigned a value of output. But if i want to
> use it as an input, it doesn't work.

That builder seems to have a bug. :-)

The only "parameter" that should have Direction.IsOutput is the return
value.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

How to define multi-valued 'default value' parameters for a report on the ReportServer

Can someone please explain how i would define a multi-valued default parameter within the report Properties -> Parameters. I have an OLAP based report with multi-value parameters. I do not want to set the default values from within BIDS. Instead, I'd like to do this from the ReportServer (after report deployment). I have no problem when i enter a single value as a 'default value', for example:

ReportParm1 String [Deal Dim].[Shelf].&[AAM]

But, how would i define it with multiple values as a 'default value' ?, for example:

ReportParm1 String [Deal Dim].[Shelf].&[ABC] , [Deal Dim].[Shelf].&[DEF]

NOTE: It appears that you cannot use expressions, such as the 'split' function in the 'default value' space.

Any help would be greatly appreciated.

thank you.

Once you publish the report, you cannot edit the expressions that define the defaut or valid values. If you have a set of expressions in the RDL, you'll be able to choose from the evaluated values of the expressions, but you won't be able to modify the expressions themselves.

If you just want to choose from a list of values derived from expressions, put the expressions in the RDL and mark the parameter as MultiValue:

<ReportParameter Name="IntegerParam">
<DataType>Integer</DataType>
<DefaultValue>
<Values>
<Value>=(1+1)</Value>
<Value>123456</Value>
<Value>-123456</Value>
<Value>=CInt(123456/123456)</Value>
</Values>
</DefaultValue>
<MultiValue>true</MultiValue>
<Prompt>Integer</Prompt>
</ReportParameter>

If you're meaning to emit the strings representing the expressions instead of the evaluated expressions, then just make the parameter a multivalued string parameter:

<ReportParameter Name="IntegerParam">
<DataType>Integer</DataType>
<DefaultValue>
<Values>
<Value>="(1+1)"</Value>
<Value>="123456"</Value>
<Value>="-123456"</Value>
<Value>="CInt(123456/123456)"</Value>
</Values>
</DefaultValue>
<MultiValue>true</MultiValue>
<Prompt>Integer</Prompt>
</ReportParameter>

Now you can use the string value as an expression.

How to define a user variable on Execute Sql Task?

Hi everyone,

How to define a Input variable in a Execute Sql Task?

I've defined a User::Inicio variable which contains 4 as value.

In Parameter Mappins it has been defined. Then, I've gone to General->Sql Statement and allocated the following SQL Statement:

UPDATE CARGAPROCESOS SET FECHAULTIMACARGA = [Inicio]

or

UPDATE CARGAPROCESOS SET FECHAULTIMACARGA = [User::Inicio]

Anyway, I'm stuck, both did not work

Thanks in advance for your comments

Enric,

Use an expression in SQlStatementSource property of your Execute SQL task to build your SQL statement:

"UPDATE CARGAPROCESOS SET FECHAULTIMACARGA = " @.[User::Inicio]

Rafael Salas

|||

Hi Rafael,

Thanks for your quick answer but it doesn't work.

[Execute SQL Task] Error: Executing the query "UPDATE CARGAPROCESOS SET FECHAULTIMACARGA = [@.User::Inicio]" failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Let me know, I can imagine that's a silly thing..

|||

Well, the error talks abour the ResulSet porperty; what is your value for that? what is you set that to None. Also i think you do not need anything in your parameter tab since the SQL statement is being created by the expression

RAfael Salas

|||Rafael is telling you to set an Expression for the SQLStatementSource property and not set the property value directly. Looks like you set the SQLStatementSource directly to "UPDATE CARGAPROCESOS SET FECHAULTIMACARGA = " + @.[User::Inicio]. To set an expression for the SQLStatementSource property click on the Expressions node on the left hand side of the Execute SQL Task Editor dialog.|||

Hi,

You mean you want to use the user variable in your query right?

Refer this:

http://msdn2.microsoft.com/en-us/library/ms141003.aspx

|||Thanks to all of you

How to define a parameter to show the true values for any of 35 different products?

My boss database is in a way that customers might have baught any of the 35 products of the company. Say if they have bought product# 16, and 27, these two values are true for that customer and the rest are false (the table has 36 columns: 35 for products and 1 for customer ID). How can I show if a customer has bought anything at all (some of them has not bought anything), and if so which numbers as a parameter? I mean I want to have a parameter that user defines to see the result for customers who have number 10, 17 as true? or number 6 as false? I probably need two parameters, one for true and false and one for the number. Although I think if I have just one parameter, that might work too. Let's say the user checks numbers 5,9, and 14 and see the customers who have bought those. So my drop down should have 35 rows for the user to choose from. But how can I make this parameter to work (define wise or query wise)?

Thanks.

I am still stuck on this. Can you people (lots of experts here) help me on this?

Lots of thanks,

Alexan

|||

Its really confusing your question anyway can u explain clearly

You need to Hide some records right?

|||

Thanks for answering Kiran.

Ok. Here is how it goes. We have a table in our database that holds the records for different products. I am going to scale down from 35 to 3 just for making the question easier. Here is how it is:

I have a table with these columns:

- Customer (Holds customer name string)

- Bought Product 1? (Holds Yes or No, indicating if this customer has bought Product 1)

- Bought Product 2? (Holds Yes or No, indicating if this customer has bought Product 2)

- Bought Product 3? (Holds Yes or No, indicating if this customer has bought Product 3)

Now I want to have a parameter so the user can choose to see only the customers who have bought product numbers passed by the parameter.

e.g. The user only wants to see which customers have bought Product 1 & 3, Or which customers have not bought product 1.

I hope this clarifies a little. Please if you still need some more clarification, send a post and I'll be explain more with details.

Waiting to hear from you guys.

I am new in databases and MS SQL, so Could you please help me out?

Lots of thanks,

|||Help please|||

Hi, ok assuming that you have SQL Server 2005 which holds the multivalue parameter and under the assumption your table structure is simplified as follows:

OrderDetail
========
Orderid
PosId
ProductId

Order
====
OrderId
CustomerID

You would have to do a Query like the following:

SELECT CustomerID From Order O
INNER JOIN OderDetail Od
ON O.OrderID = Od.OrderID
WHERE ProductID IN (@.YourParameter)

For the customername you would have to join additionally the Customer table to retrieve this data.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

how to define a parameter in reporting services to choose to show/hide not null/null values?

Hi All,

I am using SQL Server Reporting Services 2005 and in my report I want to create a parameter called "hasEmail" with 3 possible values (Yes, No, Both) to show/ hide the customers who have/don't have emails or both.

Can anyone please help?

Try Add Parameter-Choose Available Values-Non queried- there you can put in the label and values that you want to return (Yes,No,Both) Then you will have to use these parameters to filter your dataset. Hope that helps.|||

You said : "Then you will have to use these parameters to filter your dataset."

The problem is the parameter doesn't exactly match the field. For example, it's not like the case that: OK the parameter chosen by user is "Bicycles" so only show me the data (WHERE the param is Bicycle). The problem I have is that some of the customers have provided their emails in the database and some haven't. I want to be able to show (or not show) the customers that have (or have not) email address. Maybe I should use "EXISTS" or something, because like I said it's not a matter of exactly matching the string (bicycle for example) with the field; It's a matter of true/false if the email exists or not.

I don't know.

Any thoughts?

|||

Yes, right click table or list- if thats what you are using - properties- filters- I don't know about your particular case but here is one that I used a parameter to filter in- same concept, but how to apply to your case I'm not sure- you'll have to play with it.

(Expression)=Fields!CONT_FREQ_CODE.Value

(Operand) =

(Value) =iif(Format(Parameters!Report_Parameter_0.value,"MM")=3 or Format(Parameters!Report_Parameter_0.value,"MM")=6 or Format(Parameters!Report_Parameter_0.value,"MM")=9 or Format(Parameters!Report_Parameter_0.value,"MM")=12,Fields!CONT_FREQ_CODE.Value,"M")

|||

Thanks Kimberly,

I found my answer at

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=494455&SiteID=1

Thank you anyway.

how to define a parameter in reporting services to choose to show/hide not null/null values?

Hi All,

I am using SQL Server Reporting Services 2005 and in my report I want to create a parameter called "hasEmail" with 3 possible values (Yes, No, Both) to show/hide the customers who have/don't have emails or both.

Can anyone please help?

Try using a filter with the following entries. This assumes that there is an "Email" field of type string in the data set you are using, and the parameter name with the three values is ShowOnlyWithEmail. We use Len instead of IIF and a check for Nothing, because =IIF(IsNothing(Fields!Email.Value), 0, Fields!Email.Value.Length) will throw an exception if the field value is Nothing (IIF is a function and all arguments are evaluated before the function call), and Len will return 0 if the field value is Nothing.

ExpressionOperatorValue=Len(Fields!Email.Value)
>=IIF(Parameters!ShowOnlyWithEmail.Value="Yes", 0, -1)=Len(Fields!Email.Value)

<=IIF(Parameters!ShowOnlyWithEmail.Value="No", 1, Int32.MaxValue)

If the parameter is set to "Yes", then the first filter entry will

remove any rows that do not have an email address and the second will

not filter any rows.

If the parameter is set to "No", the the first filter entry will not

filter any rows, and the second will filter all rows with a length less

than 1.

If the parameter is set to "Both", then both filter entries will not filter any rows.

Ian|||It works fine. Thank you. What does LEN stand for? (Like REM is remove).|||Len is a function from VB to determine the length of an string (or size of any object) that does not blow up if a null value is passed in.

how to define a parameter in reporting services to choose to show/hide not null/null values?

Hi All,

I am using SQL Server Reporting Services 2005 and in my report I want to create a parameter called "hasEmail" with 3 possible values (Yes, No, Both) to show/hide the customers who have/don't have emails or both.

Can anyone please help?

Try using a filter with the following entries. This assumes that there is an "Email" field of type string in the data set you are using, and the parameter name with the three values is ShowOnlyWithEmail. We use Len instead of IIF and a check for Nothing, because =IIF(IsNothing(Fields!Email.Value), 0, Fields!Email.Value.Length) will throw an exception if the field value is Nothing (IIF is a function and all arguments are evaluated before the function call), and Len will return 0 if the field value is Nothing.

ExpressionOperatorValue=Len(Fields!Email.Value)
>=IIF(Parameters!ShowOnlyWithEmail.Value="Yes", 0, -1)=Len(Fields!Email.Value)
<=IIF(Parameters!ShowOnlyWithEmail.Value="No", 1, Int32.MaxValue)

If the parameter is set to "Yes", then the first filter entry will remove any rows that do not have an email address and the second will not filter any rows.
If the parameter is set to "No", the the first filter entry will not filter any rows, and the second will filter all rows with a length less than 1.
If the parameter is set to "Both", then both filter entries will not filter any rows.

Ian|||It works fine. Thank you. What does LEN stand for? (Like REM is remove).|||Len is a function from VB to determine the length of an string (or size of any object) that does not blow up if a null value is passed in.