Passing parameter in MDX query

Hi,
I am facing problem during passing the parameters in MDX query. I need tp
pass two parameter in my dataset, but not sure whether I am writing correct
query or not. as I am getting error during parsing the query.
here the query
SELECT {
[Measures].[SAT ARR], [Measures].[SAT Warranty ARR]
} on columns,
NON EMPTY NonEmptyCrossJoin(
[Component Parts].[Part].Members,
NonEmptyCrossJoin([Batch].[Batch].Members, [Building Code].[Building
Code].Members)
)
on rows FROM [CUBE NAME]
WHERE (" + Parameters!ManufactureDate.Value + ", " +
Parameters!Suppliername.Value + ")
--
The two parameter I have declared in parameter box with multiselect value
option.
while executiing the query I am gettiign belwo message.
"The tuple function expects a tuplet set of expression for the argument.A
string or numeric was used"
Please help me to write the correct syntax for this mdx.
Appreciate your help
Regards
SomuMosha Pasumansky has written an article called "Writing multiselect friendly
MDX calculations":
http://www.sqljunkies.com/WebLog/mosha/archive/2005/11/18/multiselect_friendly_mdx.aspx
Maybe you'll find your answer there. :)
Have you tried your query without parameters in an MDX parser? If not, try
it and you might figure out how the query should be before trying with the
parameters.
Kaisa M. Lindahl
"Somu" <Somu@.discussions.microsoft.com> wrote in message
news:65941D3D-8CBB-494C-9E95-3F4FD983E31F@.microsoft.com...
> Hi,
> I am facing problem during passing the parameters in MDX query. I need tp
> pass two parameter in my dataset, but not sure whether I am writing
> correct
> query or not. as I am getting error during parsing the query.
> here the query
> SELECT {
> [Measures].[SAT ARR], [Measures].[SAT Warranty ARR]
> } on columns,
> NON EMPTY NonEmptyCrossJoin(
> [Component Parts].[Part].Members,
> NonEmptyCrossJoin([Batch].[Batch].Members, [Building Code].[Building
> Code].Members)
> )
> on rows FROM [CUBE NAME]
> WHERE (" + Parameters!ManufactureDate.Value + ", " +
> Parameters!Suppliername.Value + ")
> --
> The two parameter I have declared in parameter box with multiselect value
> option.
> while executiing the query I am gettiign belwo message.
> "The tuple function expects a tuplet set of expression for the argument.A
> string or numeric was used"
> Please help me to write the correct syntax for this mdx.
> Appreciate your help
> Regards
> Somu

Passing multiple record sets from one procedure to another

I am not having any luck getting this to work. I am using SQL 2000/2005
(we have both at the moment).
I am writing a stored procedure with the intent of creating a temp
table and inserting records into it. This procedure gets its records
from another sp. So SP 1 will call SP2. SP2, however, is used by other
applications directly, and it returns 3 recordsets. In my SP1, I only
want the results of the 1st recordset. Here is the flow:
SP 1:
- creates temp table
- Calls SP 2 and inserts the results of the first returned recordset
into the temp table
- selects/exports from temp table, blah blah...
I'm not sure how to get the results of the 1st recordset (only). Any
ideas?
TimModify the inner procedure, where you add one more optional parameter. When
you call this from your
outer proc, make sure that the inner proc only returns whatever it should re
turn in that particular
case.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"sdwebguy" <sdwebguy@.gmail.com> wrote in message
news:1144339633.674358.270200@.e56g2000cwe.googlegroups.com...
>I am not having any luck getting this to work. I am using SQL 2000/2005
> (we have both at the moment).
> I am writing a stored procedure with the intent of creating a temp
> table and inserting records into it. This procedure gets its records
> from another sp. So SP 1 will call SP2. SP2, however, is used by other
> applications directly, and it returns 3 recordsets. In my SP1, I only
> want the results of the 1st recordset. Here is the flow:
> SP 1:
> - creates temp table
> - Calls SP 2 and inserts the results of the first returned recordset
> into the temp table
> - selects/exports from temp table, blah blah...
> I'm not sure how to get the results of the 1st recordset (only). Any
> ideas?
> Tim
>|||In this case, I could update both procedures, so that is what I did --
and that works great!
If there a way to handle this if I was not able to update the called
procedure?
Thanks,
Tim|||Without seeing the code, it is a little hard to comment, but this
sounds like you are still writing procedural code. Your description is
the way we would have used scartch tapes in the 1950's.
table [faking a scratch tape?] and inserting records [sic] into it.<<
You talk about "records" and not sets. You talk about a sequence of
procedure calls, just like a 3GL program. SQL is declarative; we do
things in a single statement whenever possible -- or without a
statement at all (i.e. VIEWs -- very handy and always up-to-date)!
But ignoring the lack of declarative programming in your narrative,
your procedural programming is not good.
Let's get back to coupling and cohesion, basic software engineering,
etc. You have what I call a "Britany Spears, Squid and Automobiles"
procedure. Each result should be created by one well-defined, coherent
procedure. Get out your old copy of Yourdon & DeMarco. This is far
more basic than SQL programming.|||I appreciate your response and commentary. Forgive me for not being
perfect, nor following your coding style. Rehashing the original
question so I can use proper terminology for you is a waste of time for
all of us. I work with what I have inherited just like everyone else.
And by the way, she spells her name Britney.
All the best,
Tim|||>>Forgive me for not being perfect, <<
Perfect is nice, but we can aim for competent, standard, etc.
Actually, it is ISO-11179 and not me. Then there is the whoel Yourdon,
DeMarco, et al stuff and DoD-2176 rules.
NO! If you use the wrong mental model, you will NEVER really get the
fundations of RDBMS. Words are concepts. We deal in a world of
abstractions -- the wrong words mean the wrong concepts. Do you
understand the problem with no having a "zero" and a "nothing", "null"
and "empty set" concepts in your math? HONKING BIG DIFFERENCE, UNH?
No, someone created the mess in the first place. God did not make Bade
Databases on the 8-th day, or even the 9-th. The real problem is that
peopel do nto fix them later (and kill the SOB who started the mess). .
I try to web-surf porno sites with girls who are more age-appropriate
to me; too bad most fo them are dead now). Except Ann-Margaret
(google it, kid).|||> If there a way to handle this if I was not able to update the called
> procedure?
If an outer proc calls an inner proc and that inner proc returns one result
set, you can do below in
the outer proc:
INSERT INTO ...
EXEC innerProc
But you can only catch the first result set from int inner proc this way. Th
e other result sets will
be returned to the client.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sdwebguy" <sdwebguy@.gmail.com> wrote in message
news:1144689399.872944.186380@.v46g2000cwv.googlegroups.com...
> In this case, I could update both procedures, so that is what I did --
> and that works great!
> If there a way to handle this if I was not able to update the called
> procedure?
> Thanks,
> Tim
>|||I'm sorry--did you say something? All I heard was crap. You may sound
smart--even may be smart--but your tone and communication style really
makes you look like an idiot.|||ignore celko. he can't really help anyone.

passing a 'In expression' to Stored procedure

Are there a way to pass a expression as a parameter of a stored procedure?
I am writing a stored procedure,
There is a Select statement something like:
Select Name, Street, City, State from CustomerAddress where state in
('MN','CA','TN')
Are there a way passing "('MN','CA','TN')" as parameter to the stored
Procedure?
ThanksHere is a solution.
Arrays and Lists in SQL Server
http://www.sommarskog.se/arrays-in-sql.html
AMB
"M" wrote:

> Are there a way to pass a expression as a parameter of a stored procedure?
> I am writing a stored procedure,
> There is a Select statement something like:
> Select Name, Street, City, State from CustomerAddress where state in
> ('MN','CA','TN')
> Are there a way passing "('MN','CA','TN')" as parameter to the stored
> Procedure?
> Thanks
>
>|||On Thu, 11 May 2006 11:54:55 -0700, M wrote:

>Are there a way to pass a expression as a parameter of a stored procedure?
>I am writing a stored procedure,
>There is a Select statement something like:
>Select Name, Street, City, State from CustomerAddress where state in
>('MN','CA','TN')
>Are there a way passing "('MN','CA','TN')" as parameter to the stored
>Procedure?
Hi M,
http://www.sommarskog.se/arrays-in-sql.html
Hugo Kornelis, SQL Server MVP

Passing A Field Name as a Parameter (Dynamic SQL?)

I'm writing a Stored-Procedure and Id like to pass a field name as a
parameter. It seems simple enough but I can't seem to find the
solution.
Here is a shortened example of the procedure
Select Profiles.Name, Profiles.State, Profiles.City, @.ProfileColumn,
Profiles.County FROM Profiles WHERE State = 'CT'
The @.ProfileColumn is simple a field name in the DB table. The actual
select statement is 5 times as large and consists of many InnerJoins
but I shortened it here for display reasons.
Is there a simply way to do this?You will have to do this with dynamic SQL. It is generally considered a bad
idea to do this in a stored procedure, but it certainly may be fine in
yours.
I am a bit concerned as to how many columns, and if you are spending time in
the joins going to get data that might not be used (like are all of the
columns in the same table or spread around?)
If they are all in one table, and depending on the number of rows, then you
might consider using a temp table to hold the results of the non-dynamic
parts, and then join in the dynamic parts.
If they are spread around, then dynamic SQL might be the best way to go,
since you could possibly eliminate some of the joins if columns aren't
needed.
Just thoughts...
<funphxnaz@.aol.com> wrote in message
news:1137462325.499202.325310@.g44g2000cwa.googlegroups.com...
> I'm writing a Stored-Procedure and Id like to pass a field name as a
> parameter. It seems simple enough but I can't seem to find the
> solution.
> Here is a shortened example of the procedure
>
> Select Profiles.Name, Profiles.State, Profiles.City, @.ProfileColumn,
> Profiles.County FROM Profiles WHERE State = 'CT'
> The @.ProfileColumn is simple a field name in the DB table. The actual
> select statement is 5 times as large and consists of many InnerJoins
> but I shortened it here for display reasons.
> Is there a simply way to do this?
>|||<<Build a VIEW with all the columns you want to see, then filter out
what
you do not need in the front end, where display is supposed to be done.
I actually started this way but the VIEW would have thousands of rows.
<<<< I am a bit concerned as to how many columns, and if you are
spending time in
the joins going to get data that might not be used (like are all of the
columns in the same table or spread around?)>>>>
The columns are spread around many tables. A total of 30 columns + 2 I
am trying to set dynamically.|||Found this:
http://www.sommarskog.se/dyn-search.html
It was helpful.|||(funphxnaz@.aol.com) writes:
> I'm writing a Stored-Procedure and Id like to pass a field name as a
> parameter. It seems simple enough but I can't seem to find the
> solution.
> Here is a shortened example of the procedure
>
> Select Profiles.Name, Profiles.State, Profiles.City, @.ProfileColumn,
> Profiles.County FROM Profiles WHERE State = 'CT'
> The @.ProfileColumn is simple a field name in the DB table. The actual
> select statement is 5 times as large and consists of many InnerJoins
> but I shortened it here for display reasons.
> Is there a simply way to do this?
ProfileColumn = CASE @.ProfileColumn
WHEN 'thiscolumn' THEN 'thiscolumn'
WHEN 'thatcolumn' THEN 'thatcolumn'
..
END
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Passing a column into a stored proc?

I'm writing a simple voting script and have columns for each options. I need to update the data based on whichever option the user picks.

I.e...

If the user picks option 1 then execute UPDATE mytable SET option1 = option1 + 1
If the user picks option 2 then execute UPDATE mytable SET option2 = option2 + 1
Etc., etc.

What's the best way to do that without building an ad-hoc SQL statement? There could be many options so I dont want to have lots of redundant SQL statements.

Can I just use a varible in a stored proc and do something like this?

UPDATE mytable SET @.optionUserpicked=@.optionUserpicked + 1

Thanks in advance

You can't really.

The best way is to redesign your table, so that it looks like this:

VoteID / Option (or optionID) / Votes

1,1,0

1,2,0

Then you can execute something like this:

UPDATE MyTable SET votes=votes+1 WHERE VoteID=1 ANDOption=@.option

Assuming that you are going to have multiple "polls", each uses a different VoteID. Each poll can then also have a variable number of options. It will also make reporting the final results easier as well.

|||

Maybe we can make a trick using dynamic SQL. For exampe:

create table myTable (UID int identity(1,1),option1 int,option2 int,option3 int)
go
INSERT INTO myTable (option1,option2,option3) SELECT 0,0,0
go
CREATE PROCEDURE sp_UpdVote @.opName sysname='option1',@.pkCol sysname='UID'
AS
IF (@.opName=@.pkCol)
RAISERROR('Can''t update the primary key',16,1)
ELSE
IF (exists(SELECT name FROM syscolumns
WHERE id=OBJECT_ID('myTable') ANDname=@.opName))
EXEC('UPDATE myTable SET ['+@.opName+']= ['+@.opName+']+1')
ELSE RAISERROR('There is no column named [%s] in this table.',16,1,@.opName)
go

EXEC sp_UpdVote

go
SELECT * FROM myTable

Partitions and FileGroups

I'm writing a SP to automate the creation & maintenance of Partitions and
Filegroups. Every 1st of a month, I have to create a new partition to move
all the previous months data to a new partition and at the end of the year I
have to consoldiate all the years partition into just one Partition.
I have automated the creation part and now I'm in the middle of automating
the deletion of the 11 partitions and moving the data (from all these 11
partitions) into the 12th partition.
Having moved these 11 partitions, I would like to drop the files and
filegroups associated to these partitions. To accomplish this, I need to find
out which partitions were mapped to which filegroups. Is there a way to find
out?
Thanks,
rgn
I think that the following query should get you what you need. There's a
mapping table called sys.destination_data_spaces that maps the partition
scheme's "data space ID" to the data space of the filegroup:
select P.[partition_id], P.[object_id], P.[index_id], P.[partition_number],
FG.name 'filegroup_name'
from sys.partitions P
join sys.indexes I on P.index_id = I.index_id and P.object_id =
I.object_id
join sys.partition_schemes S on I.data_space_id = S.data_space_id
join sys.destination_data_spaces DDS on S.data_space_id =
DDS.partition_scheme_id
join sys.filegroups FG on DDS.data_space_id = FG.data_space_id
order by P.object_id, P.index_Id, P.partition_number
"rgn" <rgn@.discussions.microsoft.com> wrote in message
news:F4D34E12-ED79-46FF-A067-01067410BBF6@.microsoft.com...
> I'm writing a SP to automate the creation & maintenance of Partitions and
> Filegroups. Every 1st of a month, I have to create a new partition to move
> all the previous months data to a new partition and at the end of the year
> I
> have to consoldiate all the years partition into just one Partition.
> I have automated the creation part and now I'm in the middle of automating
> the deletion of the 11 partitions and moving the data (from all these 11
> partitions) into the 12th partition.
> Having moved these 11 partitions, I would like to drop the files and
> filegroups associated to these partitions. To accomplish this, I need to
> find
> out which partitions were mapped to which filegroups. Is there a way to
> find
> out?
> Thanks,
> rgn

Partitions and FileGroups

I'm writing a SP to automate the creation & maintenance of Partitions and
Filegroups. Every 1st of a month, I have to create a new partition to move
all the previous months data to a new partition and at the end of the year I
have to consoldiate all the years partition into just one Partition.
I have automated the creation part and now I'm in the middle of automating
the deletion of the 11 partitions and moving the data (from all these 11
partitions) into the 12th partition.
Having moved these 11 partitions, I would like to drop the files and
filegroups associated to these partitions. To accomplish this, I need to find
out which partitions were mapped to which filegroups. Is there a way to find
out?
Thanks,
rgnI think that the following query should get you what you need. There's a
mapping table called sys.destination_data_spaces that maps the partition
scheme's "data space ID" to the data space of the filegroup:
select P.[partition_id], P.[object_id], P.[index_id], P.[partition_number],
FG.name 'filegroup_name'
from sys.partitions P
join sys.indexes I on P.index_id = I.index_id and P.object_id =I.object_id
join sys.partition_schemes S on I.data_space_id = S.data_space_id
join sys.destination_data_spaces DDS on S.data_space_id =DDS.partition_scheme_id
join sys.filegroups FG on DDS.data_space_id = FG.data_space_id
order by P.object_id, P.index_Id, P.partition_number
"rgn" <rgn@.discussions.microsoft.com> wrote in message
news:F4D34E12-ED79-46FF-A067-01067410BBF6@.microsoft.com...
> I'm writing a SP to automate the creation & maintenance of Partitions and
> Filegroups. Every 1st of a month, I have to create a new partition to move
> all the previous months data to a new partition and at the end of the year
> I
> have to consoldiate all the years partition into just one Partition.
> I have automated the creation part and now I'm in the middle of automating
> the deletion of the 11 partitions and moving the data (from all these 11
> partitions) into the 12th partition.
> Having moved these 11 partitions, I would like to drop the files and
> filegroups associated to these partitions. To accomplish this, I need to
> find
> out which partitions were mapped to which filegroups. Is there a way to
> find
> out?
> Thanks,
> rgn