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