sql from the fly to rs... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

sql from the fly to rs...

Author

Topic

jhermiz

3564 Posts

Posted - 2004-11-04 : 08:49:41

Im not too sure how much familiar people are with access reporting but one of the benefits of this was I was able to use the on open event to generate some SQL strings to set the report source.

What I would do is on a form I'd have combo boxes used for criteria. The user had the option to select criteria and when they did I'd do something like this:


Dim strSQL as String

If Not IsNull(Me.cboCustomer.Value) then
  strSQL = SELECT blah FROM TABLE WHERE Customer=blah
end if

if not isnull(Me.cboSite.Value) then
   strSQL = strSQL & " AND Site=blah2"
end if

....etc etc etc..

Me.ReportSource=strSQL

Then I simply set the reports record source to that string. Our reporting in the company I work for requires a lot of criteria, sometimes users select it sometimes they dont. I really really would like to know if I could do something similiar in RS.

I know RS doesn't have events, but is it possible to generate some SQL syntax and pass it to a report or something? My criteria is a bunch of drop downs and lists on a web page...I'd like to be able to click a [Go] button and be able to generate the string, then pass the string to the report, and just open the report.

What are my possibilities here ?

Thanks / Jon

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-11-04 : 09:22:46

It would be much better to create a stored procedure that can accept all of the necessary parameters so that you don't HAVE to generate any SQL. Much easier and much more secure as well (no SQL injection). You also don't have to modify your data source at all.

jhermiz

3564 Posts

Posted - 2004-11-04 : 09:24:09

hmm

That one may be a toughie...I just hate the fact that these can be optional...err plus so much criteria...errrrrrrrrr

ok will think about it...

Thanks.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-11-04 : 09:40:28

It's a lot easier than you think:

CREATE PROCEDURE myProc @p1 varchar(10)=null, @p2 varchar(20)=null, @p3 int=null AS
SET NOCOUNT ON
SELECT * FROM myTable
WHERE (@p1 IS NULL OR col1=@p1)
AND (@p2 IS NULL OR col2=@p2)
AND (@p3 IS NULL OR col3=@p3)

I've got a few at work that have 10 parameters set up like this. Works like a charm.

jhermiz

3564 Posts

Posted - 2004-11-04 : 09:41:53

ok ya I think you are right,
makes a lot of sense, cleaner, more secure, and easier (actually much easier) to change in the future.

as always thank you rob.

jon

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-04 : 09:48:15

After reading this thread, why is that song "Someone Saved My Life Tonight" by Elton John in my head ?

You can also use LIKE -- I have found it is more efficient in many cases, because you can avoid lots of OR's in your criteria. Such as:

WHERE Col1 LIKE ISNULL(@Col1,'%') AND
Col2 LIKE ISNULL(@Col2,'%') AND
Col3 LIKE ISNULL(@Col3,'%')

That works really well for varchar() columns. For datetime and numeric, you are usually better off using the logic Rob indicated. Check the execution plans either way.

- Jeff

jhermiz

3564 Posts

Posted - 2004-11-04 : 09:49:33

ewww elton john =)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-11-04 : 14:18:44

Aha! I tricked you Jeff! Col3 is an int column!

Anyway, yeah, you can use LIKE, except for those pesky non-text columns.

Funny you brought up the IsNull() thing, I used to do it that way until you posted the (@var IS NULL or Col=@var) syntax one time, I switched to that one and never looked back.

You're gonna make me dizzy!

Kristen
Test

22859 Posts

Posted - 2004-11-04 : 14:41:27

Beware that

WHERE (@p1 IS NULL OR col1=@p1)

matches "col1 IS NULL" when "@P1 IS NULL" - i.e. "give me everything".

but

WHERE Col1 LIKE ISNULL(@Col1,'%')

doesn't match "Col1 IS NULL" ever.

Kristen

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-04 : 15:02:16

Kristen -- YES! But, of course, if Null values are possible in those columns, then you will never be able to return JUST them since if you pass NULL as the parameter, it will return ALL values and not just the Null ones! so you will need to use an alternate "don't use this column as a filter" value other than Null in that case.

Rob -- you know i like to confuse people, mix things up!

The way I see it, there are 3 basic methods:

1) WHERE (@Col is null OR Col = @Col)
Pro: works for all datatypes; very logically sound; will return NULL values properly; can use any "ignore this column" value other than NULL easily
cons: the OR can cause inefficient query plans

2) WHERE Col = ISNULL(@Col,Col)
Pro: Avoids OR's; works for all datatypes
Con: Will not use any indexes; will never return rows where COL is null

3) WHERE Col LIKE ISNULL(@Col,'%')
Pro: Uses indexes
con: only works with text strings; will NEVER return rows where COL is NULL

- Jeff

jhermiz

3564 Posts

Posted - 2004-11-04 : 15:52:06

You guys know too much...

Kristen
Test

22859 Posts

Posted - 2004-11-05 : 02:10:05

"Kristen -- YES! But, of course, if Null values are possible in those columns, then you will never be able to return JUST them since if you pass NULL as the parameter, it will return ALL values and not just the Null ones! so you will need to use an alternate "don't use this column as a filter" value other than Null in that case."

Sure, but there are three parameters in here - I'm after something in Col2 and don't care about the other two boys ...

"You guys know too much..."

Its for your own good

Kristen

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-05 : 07:24:56

quote:

Sure, but there are three parameters in here - I'm after something in Col2 and don't care about the other two boys ...

How would you set the parameters when you call the stored proc to indicate that you wish to see only rows with NULL values in Col2 ?

- Jeff

Kristen
Test

22859 Posts

Posted - 2004-11-05 : 08:15:05

I wouldn't ask that question!

Seriously though its a toughie. We don't store empty strings (SProcs/Triggers change them to NULLS - conscious [or maybe paralitic!] design decision we took. So I could probably use that side effect to allow searching for "" to match NULLs.

Kristen

jhermiz

3564 Posts

Posted - 2004-11-08 : 14:57:22

quote:
Originally posted by robvolk

It's a lot easier than you think:

CREATE PROCEDURE myProc @p1 varchar(10)=null, @p2 varchar(20)=null, @p3 int=null AS
SET NOCOUNT ON
SELECT * FROM myTable
WHERE (@p1 IS NULL OR col1=@p1)
AND (@p2 IS NULL OR col2=@p2)
AND (@p3 IS NULL OR col3=@p3)

I've got a few at work that have 10 parameters set up like this. Works like a charm.

Rob,

I guess I have a few more questions I am stuck on...

In this tool users may want to see open issues (ClosedDate IS NULL) or closed issues (ClosedDate is not null) or both (ClosedDate Is NULL and ClosedDate Is Not Null)...and in my front end I have 3 radio buttons which with they can select either:

"Open"
"Closed"
"Both"

How should I handle this in the sproc ? Should I pass in some type of number and do a if conditions ?

Finally my second question is...as I said there is a lot of criteria that may or may not be entered. A between date may or may not be selected...so sometimes in my sproc I will need to handle
BETWEEN blah1 AND blah2...

But other times they may not want a date...so is this another OR in the same procedure? What Im trying to do is keep this all in one stored procedure not dependant so much on the criteria.

Jon

Kristen
Test

22859 Posts

Posted - 2004-11-08 : 15:53:07

"Open"
"Closed"
"Both"

I would usually handle that with a bit pattern:

1=Open, 2=Closed, 1+2=3 = Both, 4=SomeThingElse, 8=ANOther ...

I find that "range dates" are more easily handled with:


    (@StartDate IS NULL OR MyStartDate >= @StartDate)
AND (@EndDate IS NULL OR MyEndDate <= @EndDate)

rather than using BETWEEN.

I find that once you get to N parameters it becoems more efficient to select into a @TempTable and then DELETE according to additional criteria, rather than put them all in one monster WHERE clause. Takes some fiddling to get the Best Case (or is that the Worst Case!!) right.

Kristen

jhermiz

3564 Posts

Posted - 2004-11-09 : 08:52:07

That makes sense Kristen, if I were just writing a procedure to get some rows into some type of application. But the problem is I dont know how to handle this in reporting services. Should this Open / Closed / Both be a drop down with values behind them as well...

Jon

Kristen
Test

22859 Posts

Posted - 2004-11-09 : 11:51:11

I aint used Reporting Services yet - but I'm following your progress with interest! SO I don't know the specific answer I'm afraid.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources