Multiple Database hits vs Bulk Data parameters

I was curious to know if it the amount of data sent to the sql server mattered.I am working on a web application and I have three stored procedures that most likely will be called one after the other. Each procedure accepts at least 4 parameters. Instead if I create one stored procedure, then I will be passing at least 12 parameters. Some of the parameters could be quite bulky(at least 1000 characters).So which one is better, 1 stored procedure with 12 parameters or 3 stored procedures with 4 parameters each called one after the other.Thanks

For SQL Server, the cost of parsing parameters can always be ingonred compared with the cost of compiling a store procedure and producing a optimized execution plan for it.

From network I/O aspect, each execution of a stored procedure is considered as a 'batch' (batch in SQL means all data will be processed and returned at one time, which can greatly improve network performance). So if you use 3 stored procedures, you need 3 rounds; while 1 stored procedure need only 1 round.

So generally I suggest you use 1 stored procedure with 12 parameters.

Multiple Columns in Parameter Field

Hi

I have a report that has a number of parameters in it that the user must select prior to running the report. One of these parameters is the site_ref that they wish to use against the dataset. All my books point to the fact that if you wish, you can select what appears in the parameter field so the user does not get confused. So rather than the user having a list of site_ref which maybe useless to them, they can have a description next to each entry, e.g.

site_Ref Estate_Name
AL Aladdin's Cave
TH Bug's Bunnies Home
XN Aliens Home

Then when the user selects what he wants from list, the actual value that is passed to the parameter is 'TH' for example. In Access I believe this was called Bound to column(?). I can find no where in the 'Report Parameters' dialog box that allows for multiple columns to be displayed. I have written the SQL to create the right data;

Select site_ref, estate_name
From dbo_src_centre_list
Order By site_ref

How can I get the parameter box in Reporting Services to allow the display of both columns but bind to the site ref when it comes to passing the info into the report. None of my books actually tell me how to do it though. I have found how to get one or the other to display and the right one to pass the actually value through, but I would like to see both in list at the same time

Regards

A report parameter valid values list contains a set of value/label pairs. When the report is run, the user sees the label. When the user selects a label, the corresponding value is used as the parameter value.

In your case, you just need to set the label of the report parameter to be bound to the Estate_Name field, while the value is bound to the Site_Ref field.

Additional information on parameters in reports can be found here: http://msdn2.microsoft.com/en-us/library/ms155917.aspx

-- Robert

|||

Thank you Robert. That is exactly what I did, what I was wondering though, is it possible to have two values showing in the parameter field e.g. AC - Aladdin's Cave and tie the value to just the AC.

I will read that link you have sent through and see what direction that pushes me in.

Regards

|||

One way of doing this is to construct the desired label directly in the query:

Select site_ref, site_ref + ' - ' + estate_name AS Label
From dbo_src_centre_list
Order By site_ref

-- Robert

|||

Thank you so much Robert, that is exactly what I wanted. Works perfectly.

Kindest Regards

Multiple Calls to ServerReport.SetParameters() With Varying Numbers Of Parameters

I'm new to programming with the ReportViewer object and this issue has me stumped: it appears if you have some optional parameters in your report, and a way to refresh that report with different parameter values, the report "remembers" parameter values from previous calls to SetParameters() on subsequent renderings of the report. If a parameter is included in a call to ServerReport.SetParameters() on the first rendering, but not included in a subsequent call and the report is re-rendered, the previous value of the parameter (rather than the default value) appears to be used.

Here's a snippet of some test code I wrote within an ASP.NET 2.0 test application:

protected void Page_Load(object sender, EventArgs e)

{

if (!Page.IsPostBack)

{

this.rptViewer.ServerReport.ReportServerUrl = new Uri(this.txtReportServerUrl.Text);

this.rptViewer.ServerReport.ReportPath = this.txtReportPath.Text;

}

}

protected void btnViewRpt_Click(object sender, EventArgs e)

{

ReportParameter[] rptParams = GetReportParameters();

this.rptViewer.ServerReport.SetParameters(rptParams);

this.rptViewer.ServerReport.Refresh();

}

private ReportParameter[] GetReportParameters()

{

int paramCount = 0;

ReportParameter[] retVal;

string emptyVal = null;

if (txtName.Text != "") paramCount++;

if (txtAddress.Text != "") paramCount++;

if (txtZip.Text != "") paramCount++;

retVal = new ReportParameter[paramCount];

paramCount = 0;

if (txtName.Text != "")

retVal[paramCount++] = new ReportParameter("Name", txtName.Text);

if (txtAddress.Text != "")

retVal[paramCount++] = new ReportParameter("Address", txtAddress.Text);

if (txtZip.Text != "")

retVal[paramCount++] = new ReportParameter("Zip", txtZip.Text);

return retVal;

}

The test report was written to simply echo back the values of the parameters that are specified. The report definition allows NULL to be specified for the parameters.

The test app was written so if I enter a blank value for Name, Address or Zip, the corresponding parameter does not get created in C# and does not get sent to the report server. If I view the report with all three values (parameters) filled in, I see the parameters echoed back to me in my simple report as expected. If I clear the parameter values the first time the report is rendered, none are sent to the report server and I get no values echoed back in my report, also as expected. I can change the values and click on the View Report button and see the new values for the parameters as expected. However, if I clear any previously-specified parameters and click on View Report, the previously-specified values for the ones that are now cleared are still displayed by the report.

So my question is: once a parameter has been sent to the report, how does one "unsend" it on subsequent refreshes? I know I can create the parameter and set its value to null...but I have a situation here where that can cause errors. It'd be better if I could simply leave out the unspecified parameters and have the report refresh and render as if I were rendering it for the first time.

Any suggestions?

>>

I know I can create the parameter and set its value to null...but I have a situation here where that can cause errors. It'd be better if I could simply leave out the unspecified parameters and have the report refresh and render as if I were rendering it for the first time.

<<

Not meaning to be argumentative... just trying to figure out your situation...

What is the situation in which sending an explicit null "can cause errors"?

IAC, if you want to "render as though for the first time" (wasn't this a Madonna song <g>?) , you should probably use ReportViewer.Reset. You may need to re-specify the URL and path *after* that, though.

>L<

|||

Thanks so much for your reply! That's exactly what I was looking for. After applying ReportViewer.Reset() in our code, I got the behavior I was looking for.

Now, to address your other question (and to perhaps move away from "just get it working" toward "do it the right way"): I have a report that accepts a DateTime parameter (called ReportDate, surprisingly enough):

The parameter allows NULL values. But it also gets its set of available values from a query written for that purpose. It's also set to get the default value from the same query that provides the available values.|||

Well, that's just cr*ppy, if true. Can you share exactly the line you are using to pass the null, just in case this is supposed to work but you need to use a slightly different syntax?

>L<

|||

The code is a bit involved because we're scraping parameter values from screen controls that are automatically generated. However, I think you're on to something. I was just looking at how our parameters collection is constructed:

Code Snippet

for(int rowCount = 1; rowCount < parameterTable.Rows.Count; rowCount++)

{

TableRow tr = parameterTable.Rows[rowCount];

Control parameter = tr.Cells[1].Controls[0];

string parameterName = ((WebControl)parameter).Attributes["ParameterName"];

string parameterValue = "";

if (parameter isParameterDatePicker)

parameterValue = ((ParameterDatePicker)parameter).Text == string.Empty ? null : ((ParameterDatePicker)parameter).Text;

elseif (parameter isParameterDropDownList)

parameterValue = ((ParameterDropDownList)parameter).Value == string.Empty ? null : ((ParameterDropDownList)parameter).Value;

elseif (parameter isParameterTextBox)

parameterValue = ((ParameterTextBox)parameter).Text == string.Empty ? null : ((ParameterTextBox)parameter).Text;

else

thrownewException("Unable to determine parameter type.");

// Create the report parameter

ReportParameter reportParameter = newReportParameter();

reportParameter.Name = parameterName;

reportParameter.Values.Add(parameterValue);

reportParameters.Add(reportParameter);

}

Notice how every parameter is created with a string-type value, regardless of which type of value it represents. I'm wondering if, when actual values are specified, the code under the covers is able to successfully convert the strings into the DateTime type the report is expecting, but in the case of NULLs, the conversion/casting fails? I wonder if casting the nulls as specific data types would work?

greg

|||

Scratch that previous thought about data types. I realize now that the value(s) assigned to a ReportParameter are in fact arrays of strings. I discovered in a separate posting by Lisa that the way to create a ReportParameter with a NULL value is to simply create the parameter with the name only and not assign a value at all, like so:

Code Snippet

ReportParameter rptParam = new ReportParameter("ParamName");

or

Code Snippet

ReportParameter rptParam = new ReportParameter();

rptParam.Name = "ParamName";

But after some testing, it appears to me now that there's no difference between creating a ReportParameter object with no value assigned and simply not creating the ReportParameter object at all when you're putting together the collection you'll pass to the SetParameters() method.

So what I've determined works best here when viewing a report multiple times with optional parameters that may or may not be specified from one viewing to the next, is to simply not create any ReportParameters that don't have values specified and to use the ReportViewer.Reset() method between report renderings, as Lisa suggested, and I get the result I want. Optional parameters assume their default values as assigned in the report, and the ReportViewer object is induced to "forget" any previously-specified ReportParameter values in the current rendering of the report. If there's any downside to that I haven't discovered it yet. :-)

|||

>>

But after some testing, it appears to me now that there's no difference between creating a ReportParameter object with no value assigned and simply not creating the ReportParameter object at all when you're putting together the collection you'll pass to the SetParameters() method.

<<

Thank you for taking the time to check this out. I really thought this would be a better thing to do than Reset, if it worked. Why?

Simply because it goes against the inner grain, in terms of efficiency, to totally re-initialize if you don't have to. There are degrees of initialization in most processes that can be re-run, and one would like to use the right one <sigh>. Of course it has to *work* to be the right one. <double sigh>. There may be no downside, as you suggest, to doing extra initialization, but in some cases there is and you don't discover it until much, much later... Oh well...

Thanks again for checking this out,

>L<

Multi-parameters issue in data flow task SSIS

Hi all,

I met a problem when trying to pass values to a SQL statement through parameters. It's a data flow task. I used the OLE DB connection. My statement is like the statement below(the real statement is little complex):

Select * from myTable where mydate>? and mydate<?

I used the "set query parameter" dialogbox to bulid two parameters varStartTime and varEndTime, the values for the two parameters were set to "1/1/2005" and "12/30/2006" respectively. But when I click the "Parse query" button, I got errors

Parameter Information cannot be derived from SQL statements. Set parameter information before preparing command.

I have referred to the posts, but the problem still exists. Any help will be highly appreciated. Thanks a lot!

The "Parse Query" never works when you have ? in your SQL command. Just ignore the error.