Automating record selection parameters

Hi,

I need some advise on how to automate record selection on a stored
proc. Here is my situation. I have a stored proc that I used on
Crystal reports with two parameters - Acctcode and Subacct. When a
user enters ' *' on these parameter, it means to report on all
accounts otherwise, report only on specific account.

Here is my select statement with line numbers:

Create proc rb_SubledgerRpt
@.Acctcode varchar(4), @.SubAcct varchar(3)

As

3 Select AcctCode, SubAcct
4 From GLDetails
5 Where SubAcct <> ' '
6 and AcctCode = @.Acctcode -- for specific acctcode
7 and SubAcct = @.SubAcct -- for specific subacct

8 Go

-- If a user wants to see all Acctcode, and all Subacct, how do I
disable lines 6 and 7?

Thank you in advance for your help.

EdgarHi Edgar,

How about:

Create proc rb_SubledgerRpt
@.Acctcode varchar(4), @.SubAcct varchar(3)
As
Select AcctCode, SubAcct
From GLDetails
Where SubAcct <> ' '
and ((@.AcctCode = '*' ) or (AcctCode = @.Acctcode))
and ((@.SubAcct = '*' ) or (SubAcct = @.SubAcct))

-Dick Christoph
"Edgar" <edgarjtan@.yahoo.com> wrote in message
news:1141058639.691125.36720@.v46g2000cwv.googlegro ups.com...
> Hi,
> I need some advise on how to automate record selection on a stored
> proc. Here is my situation. I have a stored proc that I used on
> Crystal reports with two parameters - Acctcode and Subacct. When a
> user enters ' *' on these parameter, it means to report on all
> accounts otherwise, report only on specific account.
> Here is my select statement with line numbers:
> Create proc rb_SubledgerRpt
> @.Acctcode varchar(4), @.SubAcct varchar(3)
> As
> 3 Select AcctCode, SubAcct
> 4 From GLDetails
> 5 Where SubAcct <> ' '
> 6 and AcctCode = @.Acctcode -- for specific acctcode
> 7 and SubAcct = @.SubAcct -- for specific subacct
> 8 Go
> -- If a user wants to see all Acctcode, and all Subacct, how do I
> disable lines 6 and 7?
> Thank you in advance for your help.
> Edgar|||One way would be this:

Declare @.sQry nvarchar(500)
Declare @.sWhere nvarchar(500)
Set @.sWhere = ' '
set @.AcctCode = 'ABC'
Set @.sQry =
'Select AcctCode, SubAcct ' +
'From GLDetails ' +
'Where SubAcct <> '' '''

If @.AcctCode <> '*'
Set @.sWhere = @.sWhere + 'and AcctCode = ''' + @.Acctcode + ''''

If @.SubAcct <> '*'
Set @.sWhere = @.sWhere + 'and SubAcct = ''' + @.SubAcct + ''''

Set @.sQry = @.sQry + @.sWhere
EXEC sp_executesql @.sQry|||Edgar (edgarjtan@.yahoo.com) writes:
> I need some advise on how to automate record selection on a stored
> proc. Here is my situation. I have a stored proc that I used on
> Crystal reports with two parameters - Acctcode and Subacct. When a
> user enters ' *' on these parameter, it means to report on all
> accounts otherwise, report only on specific account.
> Here is my select statement with line numbers:
> Create proc rb_SubledgerRpt
> @.Acctcode varchar(4), @.SubAcct varchar(3)
> As
> 3 Select AcctCode, SubAcct
> 4 From GLDetails
> 5 Where SubAcct <> ' '
> 6 and AcctCode = @.Acctcode -- for specific acctcode
> 7 and SubAcct = @.SubAcct -- for specific subacct
> 8 Go
> -- If a user wants to see all Acctcode, and all Subacct, how do I
> disable lines 6 and 7?

Dick and Jennifer suggested two methods. Dick's method is cleaner, but
Jennifer's solution can give better performance. If there are no
indexes on AcctCode or SubAcct (or the table is small), then there is
on performance issue. But if there are indexes, it is not likely that
Dick's solution will make use of them.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hmmmm.

I guess I don't understand your comments Mr. Sommarskog. Dick's
solution to me is better. It is simpler, and doesn't use dynamic SQL.

Further, I've had the best success with GL's using a composite index on
Account+Subaccount.
If you don't know the account, you will end up doing a table scan
pretty much no matter what. If you know the account and the subaccount,
retrievals can be VERY fast.

Finally, because Dick's solution doesn't use dynamic SQL, the optimizer
can key in on the best way to do the search relatively easily.

I look forward to hearing why you don't think the optimizer would
indexes. They have in my experience.

Thank you,
Doug|||Doug (drmiller100@.hotmail.com) writes:
> I guess I don't understand your comments Mr. Sommarskog. Dick's
> solution to me is better. It is simpler, and doesn't use dynamic SQL.
> Further, I've had the best success with GL's using a composite index on
> Account+Subaccount.
> If you don't know the account, you will end up doing a table scan
> pretty much no matter what. If you know the account and the subaccount,
> retrievals can be VERY fast.

I will have admit that I did not consider the particular business problem,
but more considered the general case. Let's say that you have something
like:

SELECT ...
FROM tbl
WHERE (indexedcol1 = @.vall OR @.val1 = '*')
AND (indexedcol2 = @.val2 OR @.val2 = '*')
AND (nonindexedcol = @.val3 OR @.val3 = '*')

This will most certainly table scan, even if @.val1 has a distinct value and
@.val3 is '*', because when the optimizer builds the plan it has no
knowledge what value the parameter will have. It can sniff parameter
values, but it cannot build plans that produce wrong results. So it must
be a table scan. (The exception in SQL 2005 is when you add the query hint
OPTION (RECOMPILE).)

> Finally, because Dick's solution doesn't use dynamic SQL, the optimizer
> can key in on the best way to do the search relatively easily.

If you build a dynamic SQL string and include only the the search
parameters that were actually given, you will give the optimizer the
exact right amount of information to work with. If you only supply
@.val1, the optimizer will use that index (if it is selective enough).
If you only supply @.val2, it will use thar index, and if you supply
only @.val3, the query will table scan.

For a longer discsussion on the topic, see my article
http://www.sommarskog.se/dyn-search.html.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hello,

Thanks for your comments.

An interesting subtlty is involved here. I wonder if the difference is
significant.
Dick's code had and ((@.AcctCode = '*' ) or (AcctCode = @.Acctcode))

your code had
(indexedcol1 = @.vall OR @.val1 = '*')

this sounds weird, but I can easily see where your code would require a
table scan, yet Dick's code could be done without one.
Does this make any sense to anyone else?
I don't know how to explain my logic.

Automatically Generating PDFs With Reports Requiring Parameters

Hi,
I'm working on many reports that are generated using SQL Server
Reporting Services SP 1 from an ASP.NET 1.1 web application. One of the
things the users would like to do is have the report automatically
generate a PDF when they click on the View Report button after entering
the parameters for the report. Parameters could be a date, a city, a
state, etcetera. However, they would like to avoid having to choose the
format and click the Export link. Is there a way to manipulate the
functionality of the View Report button such that it does this
automatically? Thank you for any insights you can provide.
JabooHow are you accessing the reports?
1) Report Manager
2) URL parameters
3) Web Service
Kulgan.sql

automaticall render as pdf after parameter selection

I am using reporting services and have a report that requires three
parameters. After the parameters are selected and the report is generated by
clicking the View Report button, I want the report to default to pdf view
instead of having to select that format and export after running it. Is this
possible? This way users can print or save from the pdf view without having
to take the extra step of exporting.
Thank you for your help!
SharlynI'm also interested in it.
tnx.
Tom.sql

Autofill Date Parameters do not work when deployed to server

Good morning all,

I have a report which measures supplier performance for the previous month. It takes an age to generate so I am trying to cache a copy to a null location first thing in the morning to speed up the process. The problem I'm having is in getting the report to select the first day and last day of the month for the two parameters that the report needs to run.

In BIDS the report runs perfectly when previewed, however, when it is deployed to the report server I get the following error: 'Error during processing of ‘RP2’ report parameter. (rsReportParameterProcessingError)'

I have used the following two statements for the default values of the parameters, which work in BIDS, so I can't understand why they don't when it's deployed. I'm also sure there is an easier way of doing this, but after about an hour searching yesterday and not finding anything it only took me about half that time to use these statements:

for opening date:

=IIf(Month(Now()) = 1, CDate("01/12/" & CInt(Year(Now())-1)), IIf(Month(Now()) = 2, CDate("01/01/" & Year(Now())), IIf(Month(Now()) = 3, CDate("01/02/" & Year(Now())), IIf(Month(NOw()) = 4, CDate("01/03/" & Year(Now())), IIf(Month(Now()) = 5, CDate("01/04/" & Year(Now())), IIf(Month(Now()) = 6, CDate("01/05/" & Year(Now())), IIf(Month(Now()) = 7, CDate("01/06/" & Year(Now())), IIf(Month(Now()) = 8, CDate("01/07/" & Year(Now())), IIf(Month(Now()) = 9, CDate("01/08/" & Year(Now())), IIf(Month(Now()) = 10, CDate("01/09/" & Year(Now())), IIf(Month(Now()) = 11, Cdate("01/10/" & Year(Now())), IIf(Month(Now()) = 12, CDate("01/11/" & Year(Now())), CDate("01/12/1900")))))))))))))

for closing date (pretty similar really, this is the parameter with which the report server finds an error):

=IIf(Month(Now()) = 1, CDate("31/12/" & CInt(Year(Now())-1)), IIf(Month(Now()) = 2, CDate("31/01/" & Year(Now())), IIf(Month(Now()) = 3, CDate("28/02/" & Year(Now())), IIf(Month(Now()) = 4, CDate("31/03/" & Year(Now())), IIf(Month(Now()) = 5, CDate("30/04/" & Year(Now())), IIf(Month(Now()) = 6, CDate("31/05/" & Year(Now())), IIf(Month(Now()) = 7, CDate("30/06/" & Year(Now())), IIf(Month(Now()) = 8, CDate("31/07/" & Year(Now())), IIf(Month(Now()) = 9, CDate("31/08/" & Year(Now())), IIf(Month(Now()) = 10, CDate("30/09/" & Year(Now())), IIf(Month(Now()) = 11, CDate("31/10/" & Year(Now())), IIf(Month(Now()) = 12, CDate("30/11/" & Year(Now())), CDate("31/12/1900")))))))))))))

The only caviate to using these statements is that it wont recognise when a leap year occurs, other than that, if it would work when deployed to the report server it would work perfectly for the purposes of what we need.

If anyone can see the flaw, or knows of a better and easier way of doing this please let me know.

Humble thanks,

Paul

I think I've found out what is going on, just not sure how to fix it yet.

The problem is unique to me, everyone else can run the report from the report server and the auto date parameters work just fine.

When I built the report I used English United Kingdom as the default language, the report server's language is set as default to English United Kingdom, as is my machine. For some reason when I try to view the report it changes the date format to English US, or some other equivalent mm/dd/yyyy format and as there are only 12 months in a year the end date parameter falls over because it sees the day as being a month. However, no one else in our organisation has this problem and the report runs perfectly with the parameters being filled automatically as they should.

If I find the reason why my system default date format is being bypassed I'll post it on the forum in case anyone else encounters a similar problem.

Paul

|||

Okay, I've been a dumb schmuck, the language in Internet Explorer was set as English US, and as Reporting Services is a Web-based service it was using this information as the default language setting.

Since I've changed it to English UK everything works as it should.

Paul

AUTO_CREATE_STATISTICS & AUTO_UPDATE_STATISTICS are Null

What is the real net affect of these 2 parameters set to NULL?

How can I tell if stats have been generated for all tables in a db?

I am guessing that stats don't exist for the db and this accounts for the high degree of scans I am seeing on a 1.5gb db running on a 4 gb memory machine.

Any thoughts?
MikeThe net effect is that you have to create the stats your self and also update then on a timely basis.

Personnaly would turn them both on. Some people would argue to turn on the create but do the update on a scheduled basis, the thought is that you can get by with slightly out of date stats rather than take the performace hit to auto update.|||so the bottom line is the optimizer will not have any information on the tables and therefore do scans to find the data?

Is this an accurate description?

Mike|||basically, Yes.|||and what is the most expedient mthod of generating the stats for all objects?|||BTW, once you create statistics, you should recompile all views, triggers, stored procs and re-build all indexes starting with any clustered indexes.|||turn AUTO_CREATE_STASTISTICS & AUTO_UPDATE_STATISTICS on.|||Originally posted by Paul Young
turn AUTO_CREATE_STASTISTICS & AUTO_UPDATE_STATISTICS on.

I just found out each weekend the indexes get rebuilt. Dropped and recreated. I read that whenever an index is created on a table containing data the optimizer collects stats and stores them. Does that still occur when these options are not on?

Mike|||Still recommend to turn those two options on. The major purpose of rebuilding index is not for updating statistics. Even if rebuild index will
update statistics, but the statistics may not be updated during the week.

BTW, never recreate index by drop-and-create strategy. Try to use DBCC DBREINDEX, or CREATE INDEX with DROP_EXISTING, or DBCC INDEXDEFAG.

auto generate code for sp parameters?

Is there an easy way to generate the parameters code for calling stored procedures?

Is there an easy way to generate stored procedure code without having to manually type it in? I've seen 3rd party tools, but I was wondering if there's anything within Sql Server to do it.

I'm using c#, visual studio 2005, sql server 2005.

Thanks,

John

Using Visual Studio, Server Explorer, if I recall correctly, there 'may' be a procedure wizard.|||

I'm unable to locate it, please elaborate on how to find or get it. Do I need an sdk?

John

|||

For the T-SQL code, open SQL Server 2005 Management Studio and go to the Object explorer. Find your stored procedure, right click, and select Script Stored Procedure As > EXECUTE to > New Query Window Editor Window

Make sure to set the values of your variables before running.

For C# in Visual Studio, you might try asking the question again in one of the programming forums.