Tuesday, March 20, 2012

Conditional WHERE statement?

Hi all,

I have one for all the blackbelters out there: is there a way i can
make a stored procedure where i can control the where statement with
variables? I have to do some complex transformations to get compose a
fact table for MSAS and there a a lot of similarities between the
queries and a few differences because of different account methods
etc. (booking in starting date, booking stuff on order entry dates
etc) I want to put a combination of different rules in different
members of dimensions.

An example of what i mean:

CREATE STORED PROCEDURE dbo.FILLFACT (@.PAR1, @.PAR2)
AS
INSERT INTO FactTable (blah blah)

SELECT
IF @.PAR1 = 'OrderDate'
SourceView.Orderdate
ELSE
SourceView.StartDate
,
etc etc...

FROM
SourceView

WHERE
IF @.PAR2 = 'WholeTable'
1=1
IF @.PAR2 = 'Incomplete'
EndDate IS NULL OR EXIST (SELECT * FROM Exceptions WHERE
..., etc)

This way i could fill my fact table with

EXEC dbo.FillFact 'beginDate','Wholetable'
EXEC dbo.FillFact 'begindate', 'Rulebook1'
EXEC dbo.FillFact 'BeginDate', 'Exceptions'
etcetera.

This is not an actual SQL script i use, just an example of what i'm
talking about. Or maybe i could pass the where statement entirley as a
variable? But i can't use SET @.PAR1 = 'EndDate IS NULL' and then use
WHERE @.PAR1 can I?

I hope i'm making sense. Does anyone know if this is possible? Right
now i have a procedure that is composed of a dozen of sql scripts that
are mostly the same, but i have to copy it for every combination of
situations and then, of course, new stuff has to be added on 12
different places. Again and again.

Any thoughts?

TIA,

Gert-Jan van der Kamp[posted and mailed, please reply in news]

G.J. v.d. Kamp (gjvdkamp@.hotmail.com) writes:
> I have one for all the blackbelters out there: is there a way i can
> make a stored procedure where i can control the where statement with
> variables? I have to do some complex transformations to get compose a
> fact table for MSAS and there a a lot of similarities between the
> queries and a few differences because of different account methods
> etc. (booking in starting date, booking stuff on order entry dates
> etc) I want to put a combination of different rules in different
> members of dimensions.

I believe that my article on dynamic search condition should give
you some ideas to work from. Look at
http://www.sommarskog.se/dyn-search.html.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||That's exectly what i mean, thanx!

Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns95F2F1FFAE1DEYazorman@.127.0.0.1>...
> [posted and mailed, please reply in news]
> G.J. v.d. Kamp (gjvdkamp@.hotmail.com) writes:
> > I have one for all the blackbelters out there: is there a way i can
> > make a stored procedure where i can control the where statement with
> > variables? I have to do some complex transformations to get compose a
> > fact table for MSAS and there a a lot of similarities between the
> > queries and a few differences because of different account methods
> > etc. (booking in starting date, booking stuff on order entry dates
> > etc) I want to put a combination of different rules in different
> > members of dimensions.
> I believe that my article on dynamic search condition should give
> you some ideas to work from. Look at
> http://www.sommarskog.se/dyn-search.html.sqlsql

No comments:

Post a Comment