Dynamic Query

I need to write a stored proceed that has 15 parameters that returns a
recordset. Any one of these parameters may contain values.
EX: @.Lname = ''
@.Phone = '1234567890'
@.Fname = 'JANE'
@.City = 'LA'
@.State = ''
The main part of the proc is a dynamically created SELECT statement
where the parameters are used in the WHERE clause. EX: @.SQL = 'SELECT
* FROM Table WHERE '. Only parameters with values must be included in
the WHERE clause. And any parameter after the first one should have
'AND'. So the query should look like this:
@.SQL = 'SELECT * FROM Table WHERE '
@.SQL = @.SQL + ' phone = ' + @.phone
@.SQL = @.SQL + ' AND Fname = ' + @.Fname
How can I figure out which is the first parameter that contains a
value so not to include an AND condition and then add the AND for the
rest of the parameters?
Thanks,
NinelYou cud write
set @.SQL = 'SELECT * FROM Table WHERE 1=1'
If isnull(@.phone ,'') <> ''
select @.sql = @.sql + '
AND Phone = @.phone'
If isnull(@.Fname ,'')<> ''
select @.sql = @.sql + '
AND FName = @.Fname'
exec (@.sql)
Untested, shud work
Prad
"ninel" <ngorbunov@.onetouchdirect-dot-com.no-spam.invalid> wrote in message
news:t4CdnXjfGpsWtvLfRVn_vA@.giganews.com...
>I need to write a stored proceed that has 15 parameters that returns a
> recordset. Any one of these parameters may contain values.
> EX: @.Lname = ''
> @.Phone = '1234567890'
> @.Fname = 'JANE'
> @.City = 'LA'
> @.State = ''
> The main part of the proc is a dynamically created SELECT statement
> where the parameters are used in the WHERE clause. EX: @.SQL = 'SELECT
> * FROM Table WHERE '. Only parameters with values must be included in
> the WHERE clause. And any parameter after the first one should have
> 'AND'. So the query should look like this:
> @.SQL = 'SELECT * FROM Table WHERE '
> @.SQL = @.SQL + ' phone = ' + @.phone
> @.SQL = @.SQL + ' AND Fname = ' + @.Fname
> How can I figure out which is the first parameter that contains a
> value so not to include an AND condition and then add the AND for the
> rest of the parameters?
> Thanks,
> Ninel
>|||Hi
This will not work as @.phone or @.Fname will not be in scope. Check out
http://www.sommarskog.se/dyn-search.html for working examples.
John
"Pradeep Kutty" wrote:

> You cud write
> set @.SQL = 'SELECT * FROM Table WHERE 1=1'
> If isnull(@.phone ,'') <> ''
> select @.sql = @.sql + '
> AND Phone = @.phone'
> If isnull(@.Fname ,'')<> ''
> select @.sql = @.sql + '
> AND FName = @.Fname'
> exec (@.sql)
> Untested, shud work
> Prad
> "ninel" <ngorbunov@.onetouchdirect-dot-com.no-spam.invalid> wrote in messag
e
> news:t4CdnXjfGpsWtvLfRVn_vA@.giganews.com...
>
>|||Avoid dynamic SQL and procedures with more than five parameters.
SELECT *
FROM Foobar
WHERE first_name = COALESCE(@.my_first_name, first_name)
AND last_name = COALESCE(@.my_last_name, last_name)
AND ... ;|||On 27 Apr 2005 07:46:33 -0700, --CELKO-- wrote:

> Avoid dynamic SQL and procedures with more than five parameters.
> SELECT *
> FROM Foobar
> WHERE first_name = COALESCE(@.my_first_name, first_name)
> AND last_name = COALESCE(@.my_last_name, last_name)
> AND ... ;
Is "five parameters" an arbitrary limit based on experience?
I can vouch for the fact that when there are too many parameters, the
optimizer has a really hard time figuring out a good plan. It will do crazy
things like a table scan to compare NULLs with every row, when it could
just get the desired answer from a primary key.
In one instance I "unrolled" the query into a set of the three most often
used queries, choosing the correct one to use based on IF statements.
(Programmer insisted on a single stored procedure for looking up customer
records, when the operator would sometimes only know the last name and
state, sometimes would have member ID, sometimes would have last name,
state and some other data ...)|||>> Is "five parameters" an arbitrary limit based on experience? <<
In 1956 by a psychologist named Miller published a short article
entitled "The Magical Number Seven Plus or Minus Two: Some Limits on
Our Capacity for Processing Information" that collected a lot of data
together in one place and this has been confirmed over and over again.
It is a classic paper and it ought to be out there.
The idea is that you can juggle five things fairly well, seven is when
it gets to hard and nine requires that you train for it and it is just
about impossible to get to ten things without being a savant. What you
have to do is "chunking" things to reduce the number of distinct
elements -- so (longtitude, latitude) becomes "location" rather than
two data elements.
the optimizer has a really hard time figuring out a good plan. <<
That is another "Law of Five". There are 3 ways to squence two tables
for processing, 6 ways to squence three tables, 24 ways to squence
four tables, and 120 ways to arrange five tables. Big jump at five!
And the optimizer starts to choke.
queries, choosing the correct one to use based on IF statements. <<
While I like to avoid IF-THEN control flow, it sounds like a good way
to do it in this case.sql

Dynamic Parameter List

Is it possible to have a dynamic number of parameters? Basically I have a
vertical table that will contain a number of records. As records are added
to this table, they will need to be passed to a stored procedure. So say I
have this structure:
SP_Update_Contact @.first_name, @.last_name, @.city, @.state, @.dynamic_param1,
@.dynamic_param2, etc. etc.
Tbl_Dynamic_Params
ID
1 Nationality
2 Net Worth
3 Hair Color
4 Shoe Size
So I want to pass:
SP_Update_Contact @.first_name, @.last_name, @.city, @.state, @.Nationaltiy,
@.Net_Worth, @.HairColor, @.ShoeSize.
Is this possible without using dynamic SQL?"James" <neg@.tory.com> wrote in message
news:Ol%23pUYC5HHA.3684@.TK2MSFTNGP02.phx.gbl...
> Is it possible to have a dynamic number of parameters? Basically I have a
> vertical table that will contain a number of records. As records are
> added to this table, they will need to be passed to a stored procedure.
> So say I have this structure:
> SP_Update_Contact @.first_name, @.last_name, @.city, @.state, @.dynamic_param1,
> @.dynamic_param2, etc. etc.
> Tbl_Dynamic_Params
> ID
> 1 Nationality
> 2 Net Worth
> 3 Hair Color
> 4 Shoe Size
> So I want to pass:
> SP_Update_Contact @.first_name, @.last_name, @.city, @.state, @.Nationaltiy,
> @.Net_Worth, @.HairColor, @.ShoeSize.
> Is this possible without using dynamic SQL?
>
You can have *optional* parameters in a proc, yes. Just specify a default
value for those parameters. For example:
CREATE PROC usp_proc
@.p1 INT = NULL,
@.p2 INT = NULL
AS ...
Both @.p1 and @.p2 are optional and will default to null if they are not
specified. Obviously your parameter *names* must be known at runtime
otherwise you couldn't write any code that used those parameters could you?
Important: Do not use "sp_" as a prefix for your procs. "sp_" is the name
used for system procs and has a special meaning that will cause unwanted
side-effects to your code.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||I don't actually use sp as a prefix, it was merely a visual cue. I
understand optional parameters, but I want the list of parameters to be
dynamic, not just the arguments...that's the crux of the problem. I don't
think there's a really good solution to this.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:uX5l3nC5HHA.5724@.TK2MSFTNGP05.phx.gbl...
> "James" <neg@.tory.com> wrote in message
> news:Ol%23pUYC5HHA.3684@.TK2MSFTNGP02.phx.gbl...
>> Is it possible to have a dynamic number of parameters? Basically I have
>> a vertical table that will contain a number of records. As records are
>> added to this table, they will need to be passed to a stored procedure.
>> So say I have this structure:
>> SP_Update_Contact @.first_name, @.last_name, @.city, @.state,
>> @.dynamic_param1, @.dynamic_param2, etc. etc.
>> Tbl_Dynamic_Params
>> ID
>> 1 Nationality
>> 2 Net Worth
>> 3 Hair Color
>> 4 Shoe Size
>> So I want to pass:
>> SP_Update_Contact @.first_name, @.last_name, @.city, @.state, @.Nationaltiy,
>> @.Net_Worth, @.HairColor, @.ShoeSize.
>> Is this possible without using dynamic SQL?
> You can have *optional* parameters in a proc, yes. Just specify a default
> value for those parameters. For example:
>
> CREATE PROC usp_proc
> @.p1 INT = NULL,
> @.p2 INT = NULL
> AS ...
> Both @.p1 and @.p2 are optional and will default to null if they are not
> specified. Obviously your parameter *names* must be known at runtime
> otherwise you couldn't write any code that used those parameters could
> you?
> Important: Do not use "sp_" as a prefix for your procs. "sp_" is the name
> used for system procs and has a special meaning that will cause unwanted
> side-effects to your code.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
>|||"James" <neg@.tory.com> wrote in message
news:eK3xtqC5HHA.5212@.TK2MSFTNGP04.phx.gbl...
>I don't actually use sp as a prefix, it was merely a visual cue. I
>understand optional parameters, but I want the list of parameters to be
>dynamic, not just the arguments...that's the crux of the problem. I don't
>think there's a really good solution to this.
>
I don't understand what you mean when you say you want the "list of
parameters to be dynamic". Parameters have names and types. If you don't
know the names and types in advance then how could you possibly write a proc
that made use of them - even assuming it was possible to pass them as
parameters?
Maybe what you want is an array. SQL Server has a data structure that is
much more powerful than an array: a table! :-)
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||>> ... but I want the list of parameters to be dynamic, not just the
>> arguments...that's the crux of the problem.
The crux of the problem has to do with the poor design scheme you use to
represent various attributes as values. If you have a static set of
properties that you have to manage, treat them as columns rather than values
in a column. The right answer is that you should reconsider the logical
design. Mixing up data with metadata is always a recipe for messy and
complex solutions.
--
Anith|||You're preaching to the choir. This isn't my design. Let me rewind. A
colleague approach me with this problem.
Contacts Table:
ID First_Name Last_Name ... ...
Pretty basic. Now there is additional data that needs to be stored. I.E.
Shoe Size, Hat Size, and various other data. However, not every record will
have all of these fields. Also, fields need to be added on the fly with the
lowest amount of maintenance possible.
Performance is a huge consideration. He has approximately 2 million records
in the Contacts table and say, 10 fields +/-. He believes that performance
will be better if he has a flat table that is a 1-to-1 with this table that
stores the extra columns...as opposed to a more normalized alternative that
they have in place now which CRAWLS.
Should I talk him out of this redesign, encourage it, or suggest an
alternative? If so, that begs the question...what alternative?
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23rOPW9C5HHA.4964@.TK2MSFTNGP06.phx.gbl...
>> ... but I want the list of parameters to be dynamic, not just the
>> arguments...that's the crux of the problem.
> The crux of the problem has to do with the poor design scheme you use to
> represent various attributes as values. If you have a static set of
> properties that you have to manage, treat them as columns rather than
> values in a column. The right answer is that you should reconsider the
> logical design. Mixing up data with metadata is always a recipe for messy
> and complex solutions.
> --
> Anith
>|||"James" <neg@.tory.com> wrote in message
news:eLGgUPD5HHA.5316@.TK2MSFTNGP04.phx.gbl...
> You're preaching to the choir. This isn't my design. Let me rewind. A
> colleague approach me with this problem.
> Contacts Table:
> ID First_Name Last_Name ... ...
> Pretty basic. Now there is additional data that needs to be stored. I.E.
> Shoe Size, Hat Size, and various other data. However, not every record
> will have all of these fields. Also, fields need to be added on the fly
> with the lowest amount of maintenance possible.
> Performance is a huge consideration. He has approximately 2 million
> records in the Contacts table and say, 10 fields +/-. He believes that
> performance will be better if he has a flat table that is a 1-to-1 with
> this table that stores the extra columns...as opposed to a more normalized
> alternative that they have in place now which CRAWLS.
> Should I talk him out of this redesign, encourage it, or suggest an
> alternative? If so, that begs the question...what alternative?
>
Two million rows is not large and should be well within the capabilities of
even an entry level server. If performance is inadequate then start looking
at indexing, query plans, statistics and procedure design.
As for the logical model, a normalized design is the right place to start.
Nothing you have said suggests any need for a change from that.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:elfSovC5HHA.3400@.TK2MSFTNGP03.phx.gbl...
>.
> Maybe what you want is an array. SQL Server has a data structure that is
> much more powerful than an array: a table! :-)
>.
This is a disturbing statement from someone who purports to
understand a strong typed system. I assume its rationale
is not intellectual. Shall I pull out the sql couch and
start the examination? :-)
www.beyondsql.blogspot.com|||You mean like printf. I seriously doubt it considering the hoops C has to go
through to make it work.
How about name/value pairs passed into one large varchar?
set @.paramerters = 'first_name='+@.first_name+';last_name='+@.last_name
create procedure sp_update_contact @.parameters
as
declare @.parameters varchar(max)
loop through the string looking for the ; delimiter, then split the
name/value pair and put it in a table variable:
declare @.MyArray TABLE (
name varchar(20)
value varchar(20)
)
Then do as you please.
Since the name portion had to corospond to a column in the table, you better
have some decent error checking an support for an error return.
"James" <neg@.tory.com> wrote in message
news:Ol%23pUYC5HHA.3684@.TK2MSFTNGP02.phx.gbl...
> Is it possible to have a dynamic number of parameters? Basically I have a
> vertical table that will contain a number of records. As records are
> added to this table, they will need to be passed to a stored procedure.
> So say I have this structure:
> SP_Update_Contact @.first_name, @.last_name, @.city, @.state, @.dynamic_param1,
> @.dynamic_param2, etc. etc.
> Tbl_Dynamic_Params
> ID
> 1 Nationality
> 2 Net Worth
> 3 Hair Color
> 4 Shoe Size
> So I want to pass:
> SP_Update_Contact @.first_name, @.last_name, @.city, @.state, @.Nationaltiy,
> @.Net_Worth, @.HairColor, @.ShoeSize.
> Is this possible without using dynamic SQL?
>sql

Dynamic MDX Query

Hi,
I want to build Dynamic MDX query in SQL Server 2005. Is it possible?
ex. I have one SQL Server Report parameter which contain following value:
1. Station
2. Free Test
I have three Dimension Station, Free Test, Overall Result and one measure
Total Test.
If I select Station in report parameter then following report format i need:
Station | Pass | Fail | Total Test
x1 60 40 100
x2 ... ... ...
If I select Free Test in parameter then following report format i need:
Free Test | Pass | Fail | Total Test
yes 60 40 100
no ... ... ...
I tryed using following query but not sucessful:
WITH SET [Groupby] AS STRTOSet(@.Query1)
SELECT NON EMPTY ([Measures].[Total Test Count]) ON 0,
NON EMPTY [Groupby] * [Dim OverallResult].[Overall Result].[
Overall
Result].AllMembers ON 1
FROM [OLAP Test Cube]
let me know if you have any solution?
Dinesh Patelproblem was solved.
First execute following query and design the report.
with member [Measures].[MyMeasure] as '[Free Test].currentmember
.name'
member [Measures].[MyMeasure2] as '[Free Test].currentmember.Lev
el.Ordinal'
member [Measures].[MyMeasure3] as '[Free Test].currentmember.Uni
queName'
member [Measures].[MyMeasure4] as '[Measures].[Total Test Co
unt]'
select {[Measures].[MyMeasure], [Measures].[MyMeasure2]
,
[Measures].[MyMeasure3], [Measures].[MyMeasure4]} on columns
, {[Free
Test].members} on
rows from [OLAP Test Cube]
and then Edit Dataset using (…) button and paste Dynamic query on Query
String Textbox and click ok.
="with member [Measures].[MyMeasure] as '" & Parameters!Dimension.Va
lue &
".currentmember.name'
member [Measures].[MyMeasure2] as '" & Parameters!Dimension.Value &
".currentmember.Level.Ordinal'
member [Measures].[MyMeasure3] as '" & Parameters!Dimension.Value &
".currentmember.UniqueName'
member [Measures].[MyMeasure4] as '[Measures].[Total Test Co
unt]'
select {[Measures].[MyMeasure], [Measures].[MyMeasure2]
,
[Measures].[MyMeasure3], [Measures].[MyMeasure4]}
on columns, {" & Parameters!Dimension.Value & ".members} on
rows from [OLAP Test Cube]"
Regards,
Dinesh Patel
"Dinesh Patel" wrote:

> Hi,
> I want to build Dynamic MDX query in SQL Server 2005. Is it possible?
> ex. I have one SQL Server Report parameter which contain following value:
> 1. Station
> 2. Free Test
> I have three Dimension Station, Free Test, Overall Result and one measure
> Total Test.
> If I select Station in report parameter then following report format i nee
d:
> Station | Pass | Fail | Total Test
> x1 60 40 100
> x2 ... ... ...
> If I select Free Test in parameter then following report format i need:
> Free Test | Pass | Fail | Total Test
> yes 60 40 100
> no ... ... ...
> I tryed using following query but not sucessful:
> WITH SET [Groupby] AS STRTOSet(@.Query1)
> SELECT NON EMPTY ([Measures].[Total Test Count]) ON 0,
> NON EMPTY [Groupby] * [Dim OverallResult].[Overall Result].
1;Overall
> Result].AllMembers ON 1
> FROM [OLAP Test Cube]
> let me know if you have any solution?
> Dinesh Patel

Dynamic MDX Query

Hi,
I want to build Dynamic MDX query in SQL Server 2005. Is it possible?
ex. I have one SQL Server Report parameter which contain following value:
1. Station
2. Free Test
I have three Dimension Station, Free Test, Overall Result and one measure
Total Test.
If I select Station in report parameter then following report format i need:
Station | Pass | Fail | Total Test
x1 60 40 100
x2 ... ... ...
If I select Free Test in parameter then following report format i need:
Free Test | Pass | Fail | Total Test
yes 60 40 100
no ... ... ...
I tryed using following query but not sucessful:
WITH SET [Groupby] AS STRTOSet(@.Query1)
SELECT NON EMPTY ([Measures].[Total Test Count]) ON 0,
NON EMPTY [Groupby] * [Dim OverallResult].[Overall Result].[Overall
Result].AllMembers ON 1
FROM [OLAP Test Cube]
let me know if you have any solution?
Dinesh Patelproblem was solved.
First execute following query and design the report.
with member [Measures].[MyMeasure] as '[Free Test].currentmember.name'
member [Measures].[MyMeasure2] as '[Free Test].currentmember.Level.Ordinal'
member [Measures].[MyMeasure3] as '[Free Test].currentmember.UniqueName'
member [Measures].[MyMeasure4] as '[Measures].[Total Test Count]'
select {[Measures].[MyMeasure], [Measures].[MyMeasure2],
[Measures].[MyMeasure3], [Measures].[MyMeasure4]} on columns, {[Free
Test].members} on
rows from [OLAP Test Cube]
and then Edit Dataset using (â?¦) button and paste Dynamic query on Query
String Textbox and click ok.
="with member [Measures].[MyMeasure] as '" & Parameters!Dimension.Value &
".currentmember.name'
member [Measures].[MyMeasure2] as '" & Parameters!Dimension.Value &
".currentmember.Level.Ordinal'
member [Measures].[MyMeasure3] as '" & Parameters!Dimension.Value &
".currentmember.UniqueName'
member [Measures].[MyMeasure4] as '[Measures].[Total Test Count]'
select {[Measures].[MyMeasure], [Measures].[MyMeasure2],
[Measures].[MyMeasure3], [Measures].[MyMeasure4]}
on columns, {" & Parameters!Dimension.Value & ".members} on
rows from [OLAP Test Cube]"
Regards,
Dinesh Patel
"Dinesh Patel" wrote:
> Hi,
> I want to build Dynamic MDX query in SQL Server 2005. Is it possible?
> ex. I have one SQL Server Report parameter which contain following value:
> 1. Station
> 2. Free Test
> I have three Dimension Station, Free Test, Overall Result and one measure
> Total Test.
> If I select Station in report parameter then following report format i need:
> Station | Pass | Fail | Total Test
> x1 60 40 100
> x2 ... ... ...
> If I select Free Test in parameter then following report format i need:
> Free Test | Pass | Fail | Total Test
> yes 60 40 100
> no ... ... ...
> I tryed using following query but not sucessful:
> WITH SET [Groupby] AS STRTOSet(@.Query1)
> SELECT NON EMPTY ([Measures].[Total Test Count]) ON 0,
> NON EMPTY [Groupby] * [Dim OverallResult].[Overall Result].[Overall
> Result].AllMembers ON 1
> FROM [OLAP Test Cube]
> let me know if you have any solution?
> Dinesh Patel
>