EXEC as part of a SELECT

I wrote a stored proc that results in a dynamic rowset of different columns.
(not predefined except ID). To Make it available for a join with i.e. a view
,
I want to use an inline-tablereturn function, because this kind of function
doesnt need a predefined Tabledefinition, too.
But: I have just one Select-Statement to work with. And I dont know how to
hook them together.
something like:
Select * From Exec <myproc> <myparams>
(does somebody know how to work with derived tables? its probably a way)Klaus,
Might check out Erland's article:
How to share data between stored procedures
http://www.sommarskog.se/share_data.html
HTH
Jerry
"KlausSarbeach" <KlausSarbeach@.discussions.microsoft.com> wrote in message
news:C87327E7-8988-4DF5-96DB-6F95F9E2AAEC@.microsoft.com...
>I wrote a stored proc that results in a dynamic rowset of different
>columns.
> (not predefined except ID). To Make it available for a join with i.e. a
> view,
> I want to use an inline-tablereturn function, because this kind of
> function
> doesnt need a predefined Tabledefinition, too.
> But: I have just one Select-Statement to work with. And I dont know how to
> hook them together.
> something like:
> Select * From Exec <myproc> <myparams>
> (does somebody know how to work with derived tables? its probably a way)
>

Exclusive Lock Table like Oracle?

In Oracle, I can do "LOCK TABLE table IN EXCLUSIVE MODE" if I want to
hold a table, is there an equivalent in MSSQL?
I have a table of "next available id numbers", and when one is
requested, I want to lock the table, get the next one, delete it, and
then unlock the table.
Thanks.What version are you using?
Lookup LOCK hints in the BOL
<bradwiseathome@.hotmail.com> wrote in message
news:1145280771.375136.20130@.j33g2000cwa.googlegroups.com...
> In Oracle, I can do "LOCK TABLE table IN EXCLUSIVE MODE" if I want to
> hold a table, is there an equivalent in MSSQL?
> I have a table of "next available id numbers", and when one is
> requested, I want to lock the table, get the next one, delete it, and
> then unlock the table.
> Thanks.
>|||I believe you can do this with table hints.
BEGIN TRANSACTION
SELECT ... WITH TABLOCK
DELETE ...
END TRANSACTION
You can find samples from the BOL.
Mel

Excel MODE function - value that occurs the most

Hi

I am looking for an implementation of the EXCEL MODE function for Analysis Services calculation. MEDIAN is available as integrated function, but MODE is missing.

Has someone of you an MDX implementation for this ? (for an Sum Measure) (- I am concerned about the performance if I do this over >50.000 Member Sets manually.)

Best Regards

HANNES

I have programmed my one procedure the do the job.

If you are interested the code is available at http://www.hmayer.net/tiki-list_file_gallery.php?galleryId=24

HANNES

|||

Interesting problem, Hannes. It is possible in MDX - here's a solution:

Code Snippet

with

member measures.countsame as

count(

filter(union([Date].[Calendar].currentmember.level.members, {[Date].[Calendar].currentmember} as currentmonth)

, ([Date].[Calendar].currentmember, [Measures].[Customer Count])

= (currentmonth.item(0).item(0), [Measures].[Customer Count])

)

)

member measures.mode as

(topcount([Date].[Calendar].[Month].members,1, measures.countsame).item(0).item(0), [Measures].[Customer Count])

select {[Measures].[Customer Count], measures.countsame, measures.mode} on 0,

[Date].[Calendar].[Month].members on 1

from [Adventure Works]

Although I suspect there might be a more a efficient way of doing it in MDX (I need to think a bit), custom code may well give you the best performance. If you could test this against your procedure I'd be interested to hear the result!

Regards,

Chris

Excel MODE function - value that occurs the most

Hi

I am looking for an implementation of the EXCEL MODE function for Analysis Services calculation. MEDIAN is available as integrated function, but MODE is missing.

Has someone of you an MDX implementation for this ? (for an Sum Measure) (- I am concerned about the performance if I do this over >50.000 Member Sets manually.)

Best Regards

HANNES

I have programmed my one procedure the do the job.

If you are interested the code is available at http://www.hmayer.net/tiki-list_file_gallery.php?galleryId=24

HANNES

|||

Interesting problem, Hannes. It is possible in MDX - here's a solution:

Code Snippet

with

member measures.countsame as

count(

filter(union([Date].[Calendar].currentmember.level.members, {[Date].[Calendar].currentmember} as currentmonth)

, ([Date].[Calendar].currentmember, [Measures].[Customer Count])

= (currentmonth.item(0).item(0), [Measures].[Customer Count])

)

)

member measures.mode as

(topcount([Date].[Calendar].[Month].members,1, measures.countsame).item(0).item(0), [Measures].[Customer Count])

select {[Measures].[Customer Count], measures.countsame, measures.mode} on 0,

[Date].[Calendar].[Month].members on 1

from [Adventure Works]

Although I suspect there might be a more a efficient way of doing it in MDX (I need to think a bit), custom code may well give you the best performance. If you could test this against your procedure I'd be interested to hear the result!

Regards,

Chris