how can you pre-sort in a UNION

Hello:
I would like to not use a temporary table but I cannot seem to find a
solution on how to return
a recordset as the union of two recordsets with the second recordset sorted.
SELECT 0 as intDOCTYPEunid,
'ANY TYPE' as vcDOCTYPEexplicit,
'ANY TYPE OF DOCUMENT' asvcDOCTYPEDescription
UNION
SELECT intDOCTYPEunid,
vcDOCTYPEexplicit,
vcDOCTYPEDescription
FROM DOCTYPE
RETURN
-- I want A"NY TYPE" to always be the first row and the other rows to be
sorted by vcDOCTYPEexplicit
-- I tried using a sorted view for the second SELECT statement but the union
unfortunately destroys the sort order: a priori, I dont see why
it does that as it would be a great workaround...
Thanks
TerrySupport
SELECT <column lists> FROM
(
SELECT 0 as intDOCTYPEunid,
'ANY TYPE' as vcDOCTYPEexplicit,
'ANY TYPE OF DOCUMENT' asvcDOCTYPEDescription
UNION ALL
SELECT intDOCTYPEunid,
vcDOCTYPEexplicit,
vcDOCTYPEDescription
FROM DOCTYPE
) AS Der ORDER BY --what you want
"Support" <RemoveThis_Support@.mail.oci.state.ga.us> wrote in message
news:umW3r%239pFHA.2956@.TK2MSFTNGP12.phx.gbl...
> Hello:
> I would like to not use a temporary table but I cannot seem to find a
> solution on how to return
> a recordset as the union of two recordsets with the second recordset
> sorted.
> SELECT 0 as intDOCTYPEunid,
> 'ANY TYPE' as vcDOCTYPEexplicit,
> 'ANY TYPE OF DOCUMENT' asvcDOCTYPEDescription
> UNION
> SELECT intDOCTYPEunid,
> vcDOCTYPEexplicit,
> vcDOCTYPEDescription
> FROM DOCTYPE
> RETURN
> -- I want A"NY TYPE" to always be the first row and the other rows to be
> sorted by vcDOCTYPEexplicit
> -- I tried using a sorted view for the second SELECT statement but the
> union unfortunately destroys the sort order: a priori, I dont see why
> it does that as it would be a great workaround...
> Thanks
> Terry
>|||Try this
Select * From(
SELECT 0 as intDOCTYPEunid,
'ANY TYPE' as vcDOCTYPEexplicit,
'ANY TYPE OF DOCUMENT' asvcDOCTYPEDescription
UNION
SELECT intDOCTYPEunid,
vcDOCTYPEexplicit,
vcDOCTYPEDescription
FROM DOCTYPE
) T order by case when vcDOCTYPEexplicit='Any type' then 1 else 0 end
Madhivanan|||Select * from
(
SELECT 0 as intDOCTYPEunid,
'ANY TYPE' as vcDOCTYPEexplicit,
'ANY TYPE OF DOCUMENT' asvcDOCTYPEDescription
1 as SortOrder
UNION
SELECT intDOCTYPEunid,
vcDOCTYPEexplicit,
vcDOCTYPEDescription
2 as SortOrder
FROM DOCTYPE
)
Order by SortOrder,vcDOCTYPEexplicit
--
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Support" wrote:

> Hello:
> I would like to not use a temporary table but I cannot seem to find a
> solution on how to return
> a recordset as the union of two recordsets with the second recordset sorte
d.
> SELECT 0 as intDOCTYPEunid,
> 'ANY TYPE' as vcDOCTYPEexplicit,
> 'ANY TYPE OF DOCUMENT' asvcDOCTYPEDescription
> UNION
> SELECT intDOCTYPEunid,
> vcDOCTYPEexplicit,
> vcDOCTYPEDescription
> FROM DOCTYPE
> RETURN
> -- I want A"NY TYPE" to always be the first row and the other rows to be
> sorted by vcDOCTYPEexplicit
> -- I tried using a sorted view for the second SELECT statement but the uni
on
> unfortunately destroys the sort order: a priori, I dont see why
> it does that as it would be a great workaround...
> Thanks
> Terry
>
>|||Support wrote:
> Hello:
> I would like to not use a temporary table but I cannot seem to find a
> solution on how to return
> a recordset as the union of two recordsets with the second recordset sorte
d.
> SELECT 0 as intDOCTYPEunid,
> 'ANY TYPE' as vcDOCTYPEexplicit,
> 'ANY TYPE OF DOCUMENT' asvcDOCTYPEDescription
> UNION
> SELECT intDOCTYPEunid,
> vcDOCTYPEexplicit,
> vcDOCTYPEDescription
> FROM DOCTYPE
> RETURN
> -- I want A"NY TYPE" to always be the first row and the other rows to be
> sorted by vcDOCTYPEexplicit
> -- I tried using a sorted view for the second SELECT statement but the uni
on
> unfortunately destroys the sort order: a priori, I dont see why
> it does that as it would be a great workaround...
Why it doesn't work the second way: A union says "return me a unique
set of rows", so after all rows are produced by the individual selects,
they're all sorted into an order (what order is determined by the
system) and eliminates the duplicates, then returns them. You might
then think "If I do a UNION ALL" (where it doesn't remove duplicates),
that'll work. And for some queries it may, but that is by no means
guaranteed, since a UNION ALL tells the system that it can return rows
from any of the selects as soon as those rows are available.
Best I can think of is to have the top select return a 0 in 1 column
(e.g. Called SortOrder) followed by any values you want/need for
remaining columns, and have the bottom select return 1 followed by the
columns on which sorting is done. Then you can order the whole union on
SortOrder, <other columns>
Damien|||Thanks to all: good help!
Take care
"Support" <RemoveThis_Support@.mail.oci.state.ga.us> wrote in message
news:umW3r%239pFHA.2956@.TK2MSFTNGP12.phx.gbl...
> Hello:
> I would like to not use a temporary table but I cannot seem to find a
> solution on how to return
> a recordset as the union of two recordsets with the second recordset
> sorted.
> SELECT 0 as intDOCTYPEunid,
> 'ANY TYPE' as vcDOCTYPEexplicit,
> 'ANY TYPE OF DOCUMENT' asvcDOCTYPEDescription
> UNION
> SELECT intDOCTYPEunid,
> vcDOCTYPEexplicit,
> vcDOCTYPEDescription
> FROM DOCTYPE
> RETURN
> -- I want A"NY TYPE" to always be the first row and the other rows to be
> sorted by vcDOCTYPEexplicit
> -- I tried using a sorted view for the second SELECT statement but the
> union unfortunately destroys the sort order: a priori, I dont see why
> it does that as it would be a great workaround...
> Thanks
> Terry
>

How can I use the last recordset in a SP that return 3 recordsets ?

Hello,

I have a SP1 that is calling another SP2,
SP2 is using select and returning records

So in SP1 I get 3 different record sets
(as I can see it on Query Analyzer - one after the other)

When I'm trying using the ASP page I get this error:
"Item cannot be found in the collection corresponding to the requested name or ordinal."

How can I access the last recordset ?If you really mean ASP.NET, the DataReader has a NextResult method that can get you from result set 1 to 2, and then to three.|||Thanks, but actually I need to do it on ASP,

what is the eqvivalent for ASP ?

H - E - L - P|||ADO has a NextRecordset method:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthnextrec.asp

This is really an ASP.NET forum, so other places may be better for info on ADO classic...

How can I use stored procedure's recordset output into simple SELECT statement?

Hi All
I have one stored procedure called MY_SP that returns a recordset. I would
like to use this recordset output into my SELECT statement like this:
SELECT * FROM (EXEC MY_SP '1', '2') RS
The above blurs up with syntax error at EXEC. Is there a way to achieve
this? (Note that MY_SP takes few parameters)
Thanks in advance.
NayanI don't understand why don't you just call EXEC MY_SP '1', '2'?
Perayu
"Nayan Mansinha" <nmansinha@.icode.com> wrote in message
news:%23ddMQp5RGHA.3972@.TK2MSFTNGP10.phx.gbl...
> Hi All
> I have one stored procedure called MY_SP that returns a recordset. I
> would like to use this recordset output into my SELECT statement like
> this:
> SELECT * FROM (EXEC MY_SP '1', '2') RS
> The above blurs up with syntax error at EXEC. Is there a way to achieve
> this? (Note that MY_SP takes few parameters)
> Thanks in advance.
> Nayan
>|||thanks for asking
The example I have included is for the purpose of getting my problem across
to the audience. I agree with you that if I need to do something as simple
as quoted, I would rather do it your way. I'm actually trying to further
create a complex query using SELECT that will require data from this SP.
I hope I have made my question little more clearer.
TIA
Nayan
"Perayu" <yu.he@.state.mn.us.Remove4Replay> wrote in message
news:eJMwRI6RGHA.5900@.tk2msftngp13.phx.gbl...
>I don't understand why don't you just call EXEC MY_SP '1', '2'?
> Perayu
> "Nayan Mansinha" <nmansinha@.icode.com> wrote in message
> news:%23ddMQp5RGHA.3972@.TK2MSFTNGP10.phx.gbl...
>|||Use Temp table to store the resultset from MY_SP is one option.
Perayu
"Nayan Mansinha" <nmansinha@.icode.com> wrote in message
news:eC%235CQ6RGHA.4976@.TK2MSFTNGP11.phx.gbl...
> thanks for asking
> The example I have included is for the purpose of getting my problem
> across to the audience. I agree with you that if I need to do something
> as simple as quoted, I would rather do it your way. I'm actually trying
> to further create a complex query using SELECT that will require data from
> this SP.
> I hope I have made my question little more clearer.
> TIA
> Nayan
> "Perayu" <yu.he@.state.mn.us.Remove4Replay> wrote in message
> news:eJMwRI6RGHA.5900@.tk2msftngp13.phx.gbl...
>|||Thanks Perayu for your quick response.
The temp table option will work fine but what if I dont have permission to
modify the said SP? In that case, what would be my options?
thanks again for your ideas.
Nayan
"Perayu" <yu.he@.state.mn.us.Remove4Replay> wrote in message
news:OfZwUV6RGHA.5656@.TK2MSFTNGP11.phx.gbl...
> Use Temp table to store the resultset from MY_SP is one option.
> Perayu
> "Nayan Mansinha" <nmansinha@.icode.com> wrote in message
> news:eC%235CQ6RGHA.4976@.TK2MSFTNGP11.phx.gbl...
>|||On Tue, 14 Mar 2006 15:59:52 -0500, Nayan Mansinha wrote:

>Thanks Perayu for your quick response.
>The temp table option will work fine but what if I dont have permission to
>modify the said SP? In that case, what would be my options?
Hi Nayan,
You don't have to modify the SP. You can insert the results of the SP in
a tem table, then use that in your later queries:
CREATE TABLE #Reults
(Col1 some_datetype NOT NULL,
..)
INSERT INTO #Results (Col1, ...)
EXEC EXEC MY_SP '1', '2'
Hugo Kornelis, SQL Server MVP|||In light of your other posts, rewrite the stored procedure as a table
returning function and then you can easily use it in the FROM part of a
SELECT statement.|||Hi Hugo.
Thanks for replying.
I already did this earlier but wanted know if there is a simpler way that
can allow me to pipe-in the resultset from an SP without first creating the
table.
thanks again.
Nayan
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:r7le129b97pr04pcafqo0boti13qba0945@.
4ax.com...
> On Tue, 14 Mar 2006 15:59:52 -0500, Nayan Mansinha wrote:
>
> Hi Nayan,
> You don't have to modify the SP. You can insert the results of the SP in
> a tem table, then use that in your later queries:
> CREATE TABLE #Reults
> (Col1 some_datetype NOT NULL,
> ...)
> INSERT INTO #Results (Col1, ...)
> EXEC EXEC MY_SP '1', '2'
> --
> Hugo Kornelis, SQL Server MVP|||Hi JeffB
I understand your solution. What I'm looking at is how can I directly call
the stored procedure in the FROM part of the SELECT statement. I hope the
answer to this is not "NO" - cant use a stored procedure in the FROM part!
Thanks for the reply
Nayan
"JeffB" <jeff.bolton@.citigatehudson.com> wrote in message
news:1142374607.563351.82890@.i40g2000cwc.googlegroups.com...
> In light of your other posts, rewrite the stored procedure as a table
> returning function and then you can easily use it in the FROM part of a
> SELECT statement.
>|||I don't believe that you can. This is what a function that returns a
table is for.