dynamic procedure creation - possible?

Hi Gurus
I am trying to develop an application around SQL server. The app allows
the user to input a where clause at runtime, which the application runs
against. The only way the app can use the input where clause is by
building an SQL statement including the where clause, and then calling
sp_executeSQL against it. This is inefficient; ideally I would like to
compile the SQL statement (as it will be used multiple times) - but how
can I do this? I cannot call CREATE PROCEDURE, as that cannot be done
from inside another statement (it must be done explicitly with a
hardcoded where statement).
Is there any wat I can improve on building the statement and calling
sp_executeSQL every time I need to run it?"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:1169494423.824216.35130@.38g2000cwa.googlegroups.com...
> Hi Gurus
> I am trying to develop an application around SQL server. The app allows
> the user to input a where clause at runtime, which the application runs
> against. The only way the app can use the input where clause is by
> building an SQL statement including the where clause, and then calling
> sp_executeSQL against it. This is inefficient;
Why do you think that? SQL Server will cache and reuse SQL queries sent
using sp_executesql, especially if you use parameter markers in the query.
See
Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
Execution Plan Caching and Reuse
http://msdn2.microsoft.com/en-us/library/aa175244(SQL.80).aspx
> ideally I would like to
> compile the SQL statement (as it will be used multiple times) - but how
> can I do this? I cannot call CREATE PROCEDURE, as that cannot be done
> from inside another statement (it must be done explicitly with a
> hardcoded where statement).
> Is there any wat I can improve on building the statement and calling
> sp_executeSQL every time I need to run it?
>
No. That is really a perfectly fine thing to do. Consider using parameter
markers in sp_executesql to get query plan reuse and reduce compilations.
However if the volume of these dynamic queries is not great, or the cost of
each query to execute is large, then plan reuse may not be important or even
desirable.
David|||Thanks very much for that David.
The queries don't have parameters - they are always exactly the same
every time they are run (the user may change the where clause, but this
will be a very infrequent event). However, there could be up to 255 of
these queries (and where clauses), which could potentially all get
called (in series) every time a row is inserted into a certain key
table. A row is inserted in the key table possibly several times a
second (worst case).|||"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:1169495992.357049.50780@.m58g2000cwm.googlegroups.com...
> Thanks very much for that David.
> The queries don't have parameters - they are always exactly the same
> every time they are run (the user may change the where clause, but this
> will be a very infrequent event). However, there could be up to 255 of
> these queries (and where clauses), which could potentially all get
> called (in series) every time a row is inserted into a certain key
> table. A row is inserted in the key table possibly several times a
> second (worst case).
>
Ok, 255 different queries isn't much at all. I would just let SQL Server
cache them.
David

Dynamic MDX Query

I am working on SRS 2005 report and need to calculate measures based on parameters. The new great feature of SRS2005 which allows to use parameters in MDX query unfortunately does not work in inner query. For example I am building 4 time sets based on specified date ranges and then calculate summary for each time set. So query looks like this:

With

Set [Time1] AS '@.ParameterTime1'

...

Set [Time4] AS '@.ParameterTime4'

member [Measures].[Count1] as 'Sum ( { [Time1] } ,[Measures].[Count]) '

...

member [Measures].[Count4] as 'Sum ( { [Time4] } ,[Measures].[Count]) '

Select

{

[Measures].[Count1, ... , [Measures].[Count4]

} on Columns,

{

...

} on Rows

From [Cube]

It would be great if we still had dynamic queries supported with MDX which was available in SRS 2000. In that case I could build a query string and incorporate paramters in SET section. For example this type of query would not work in SRS 2005. I'm getting an error.

="With Set [Time1] AS ' " & Parameter!ParameterTime1 &"' ....."

If someone found any solution of using complex MDX queries with parameters, please respond. I am searching the web for a few days and no luck.

Thanks

Olga

Hi,

You can use parameters in the calculated members or sets, but you need to remove the character '.

With
Set [Time1] AS @.ParameterTime1
...
Set [Time4] AS @.ParameterTime4
member [Measures].[Count1] as 'Sum ( { [Time1] } ,[Measures].[Count]) '
...
member [Measures].[Count4] as 'Sum ( { [Time4] } ,[Measures].[Count]) '
Select
{
[Measures].[Count1, ... , [Measures].[Count4]
} on Columns,
{
...
} on Rows
From [Cube]

Regards,
Telmo Moreira|||

Telmo,

Thanks a lot ! You saved my day.

The query with parameters works fine.

Olga

|||

Hi,

I have a similar requirement but the number of sets i need to create is not fixed and needs to be dynamic. I will know the number of date ranges i need to filter on only at run time and i dont want to build an MDX query at runtime. Is there a simpler way of achieveing this by passing all the date ranges I require as a single parameter?

Thanks in Advance,

Arun

sql

Dynamic MDX Query

I am working on SRS 2005 report and need to calculate measures based on parameters. The new great feature of SRS2005 which allows to use parameters in MDX query unfortunately does not work in inner query. For example I am building 4 time sets based on specified date ranges and then calculate summary for each time set. So query looks like this:

With

Set [Time1] AS '@.ParameterTime1'

...

Set [Time4] AS '@.ParameterTime4'

member [Measures].[Count1] as 'Sum ( { [Time1] } ,[Measures].[Count]) '

...

member [Measures].[Count4] as 'Sum ( { [Time4] } ,[Measures].[Count]) '

Select

{

[Measures].[Count1, ... , [Measures].[Count4]

} on Columns,

{

...

} on Rows

From [Cube]

It would be great if we still had dynamic queries supported with MDX which was available in SRS 2000. In that case I could build a query string and incorporate paramters in SET section. For example this type of query would not work in SRS 2005. I'm getting an error.

="With Set [Time1] AS ' " & Parameter!ParameterTime1 &"' ....."

If someone found any solution of using complex MDX queries with parameters, please respond. I am searching the web for a few days and no luck.

Thanks

Olga

Hi,

You can use parameters in the calculated members or sets, but you need to remove the character '.

With
Set [Time1] AS @.ParameterTime1
...
Set [Time4] AS @.ParameterTime4
member [Measures].[Count1] as 'Sum ( { [Time1] } ,[Measures].[Count]) '
...
member [Measures].[Count4] as 'Sum ( { [Time4] } ,[Measures].[Count]) '
Select
{
[Measures].[Count1, ... , [Measures].[Count4]
} on Columns,
{
...
} on Rows
From [Cube]

Regards,
Telmo Moreira

|||

Telmo,

Thanks a lot ! You saved my day.

The query with parameters works fine.

Olga

|||

Hi,

I have a similar requirement but the number of sets i need to create is not fixed and needs to be dynamic. I will know the number of date ranges i need to filter on only at run time and i dont want to build an MDX query at runtime. Is there a simpler way of achieveing this by passing all the date ranges I require as a single parameter?

Thanks in Advance,

Arun

|||Hi,
is it possible to use a parameter as mentioned below?

select [Measures].[Internet Sales Amount] on 0,
@.ProductCategories on 1
from [Adventure Works]
And the MDX it would actually run against Analysis Services would be:
select [Measures].[Internet Sales Amount] on 0,
{[Product].[Category].&[1],[Product].[Category].&[2]} on 1
from [Adventure Works]

dynamic matrix reports

Hello,
Is it possible to create a report which allows users to select the
fields
for the matrix in a report. To explain in detail, can we allow the
users to
select the X and Y axis for a matrix in a report?
For ex., there is a report containing a matrix which shows the total
sales(
data cell) by month (X axis) and by Rep( Y axis). Can we have some
option
so that if the users select Year as the X axis and Company as the Y
axis
then they can view the same report but by company and Year instead of
Month and Rep?
Regards
Jaideepcertainly.
Just use a switch statement or iif statement for the values for the row
and column groups:
switch(parameters!group.value = 'x', fields!month.value,
parameters!group.value = 'y', fields!year.value)
or
iif(parameters!group.value = 'x', fields!month.value,
fields!year.value)
jai wrote:
> Hello,
> Is it possible to create a report which allows users to select the
> fields
> for the matrix in a report. To explain in detail, can we allow the
> users to
> select the X and Y axis for a matrix in a report?
>
> For ex., there is a report containing a matrix which shows the total
> sales(
> data cell) by month (X axis) and by Rep( Y axis). Can we have some
> option
> so that if the users select Year as the X axis and Company as the Y
> axis
> then they can view the same report but by company and Year instead of
> Month and Rep?
>
> Regards
> Jaideep