Apply ROWCOUNT on one Table in UNION

I have these two SELECTs in my SPROC... how can I merge the two tables
together so that my result is one table? UNION won't work as the
ROWCOUNTs are different, I can't use TOP as it's a sproc and you'll
notice I'm randomly sorting the top table and not the bottom. Thanks
for any help.
SET ROWCOUNT @.iNumRows
SELECT a.*
FROM tbl_1
WHERE badge = 0
ORDER BY NewID()
SET ROWCOUNT 100 PERCENT
SELECT a.*
FROM tbl_1
WHERE badge = @.iFilter
~Greg"Bac2Day1" <Bac2Day1@.gmail.com> wrote in message
news:1136411031.683766.39540@.g49g2000cwa.googlegroups.com...
>I have these two SELECTs in my SPROC... how can I merge the two tables
> together so that my result is one table? UNION won't work as the
> ROWCOUNTs are different, I can't use TOP as it's a sproc and you'll
> notice I'm randomly sorting the top table and not the bottom. Thanks
> for any help.
> SET ROWCOUNT @.iNumRows
> SELECT a.*
> FROM tbl_1
> WHERE badge = 0
> ORDER BY NewID()
> SET ROWCOUNT 100 PERCENT
> SELECT a.*
> FROM tbl_1
> WHERE badge = @.iFilter
>
> ~Greg
>
Please always state what version of SQL Server you are using. In SQL Server
2005 the following is possible:
DECLARE @.iNumRows INTEGER, @.iFilter INTEGER
SELECT x, badge
FROM
(SELECT TOP (@.inumrows) x, badge
FROM tbl_1 AS a
WHERE badge = 0
ORDER BY NEWID()
UNION ALL
SELECT x, badge
FROM tbl_1 AS a
WHERE badge = @.iFilter
) AS T ;
To do that in 2000 you would have to use dynamic SQL to construct the TOP
query because TOP doesn't otherwise allow a parameter for the number of
rows.
One alternative might be to insert the first result into a table variable
(use SET ROWCOUNT) and then UNION it with the second result. That would make
me slightly uneasy however, because the effect of ORDER BY in an INSERT
SELECT statement is buggy or at least poorly defined under some conditions.
Failing that I think I would opt for the dynamic query solution or combine
the result sets in the calling code rather than in the proc.
David Portas
SQL Server MVP
--|||>> In SQL Server 2005 the following is possible:
Is this a documented construct? To allow ORDER BY in the first SELECT seems
like an oversight, left unchecked in the parser.
Anith

Apply ROWCOUNT on one Table in UNION

I have these two SELECTs in my SPROC... how can I merge the two tables
together so that my result is one table? UNION won't work as the
ROWCOUNTs are different, I can't use TOP as it's a sproc and you'll
notice I'm randomly sorting the top table and not the bottom. Thanks
for any help.
SET ROWCOUNT @.iNumRows
SELECT a.*
FROM tbl_1
WHERE badge = 0
ORDER BY NewID()
SET ROWCOUNT 100 PERCENT
SELECT a.*
FROM tbl_1
WHERE badge = @.iFilter
~Greg
"Bac2Day1" <Bac2Day1@.gmail.com> wrote in message
news:1136411031.683766.39540@.g49g2000cwa.googlegro ups.com...
>I have these two SELECTs in my SPROC... how can I merge the two tables
> together so that my result is one table? UNION won't work as the
> ROWCOUNTs are different, I can't use TOP as it's a sproc and you'll
> notice I'm randomly sorting the top table and not the bottom. Thanks
> for any help.
> SET ROWCOUNT @.iNumRows
> SELECT a.*
> FROM tbl_1
> WHERE badge = 0
> ORDER BY NewID()
> SET ROWCOUNT 100 PERCENT
> SELECT a.*
> FROM tbl_1
> WHERE badge = @.iFilter
>
> ~Greg
>
Please always state what version of SQL Server you are using. In SQL Server
2005 the following is possible:
DECLARE @.iNumRows INTEGER, @.iFilter INTEGER
SELECT x, badge
FROM
(SELECT TOP (@.inumrows) x, badge
FROM tbl_1 AS a
WHERE badge = 0
ORDER BY NEWID()
UNION ALL
SELECT x, badge
FROM tbl_1 AS a
WHERE badge = @.iFilter
) AS T ;
To do that in 2000 you would have to use dynamic SQL to construct the TOP
query because TOP doesn't otherwise allow a parameter for the number of
rows.
One alternative might be to insert the first result into a table variable
(use SET ROWCOUNT) and then UNION it with the second result. That would make
me slightly uneasy however, because the effect of ORDER BY in an INSERT
SELECT statement is buggy or at least poorly defined under some conditions.
Failing that I think I would opt for the dynamic query solution or combine
the result sets in the calling code rather than in the proc.
David Portas
SQL Server MVP
|||>> In SQL Server 2005 the following is possible:
Is this a documented construct? To allow ORDER BY in the first SELECT seems
like an oversight, left unchecked in the parser.
Anith

Apply ROWCOUNT on one Table in UNION

I have these two SELECTs in my SPROC... how can I merge the two tables
together so that my result is one table? UNION won't work as the
ROWCOUNTs are different, I can't use TOP as it's a sproc and you'll
notice I'm randomly sorting the top table and not the bottom. Thanks
for any help.
SET ROWCOUNT @.iNumRows
SELECT a.*
FROM tbl_1
WHERE badge = 0
ORDER BY NewID()
SET ROWCOUNT 100 PERCENT
SELECT a.*
FROM tbl_1
WHERE badge = @.iFilter
~Greg"Bac2Day1" <Bac2Day1@.gmail.com> wrote in message
news:1136411031.683766.39540@.g49g2000cwa.googlegroups.com...
>I have these two SELECTs in my SPROC... how can I merge the two tables
> together so that my result is one table? UNION won't work as the
> ROWCOUNTs are different, I can't use TOP as it's a sproc and you'll
> notice I'm randomly sorting the top table and not the bottom. Thanks
> for any help.
> SET ROWCOUNT @.iNumRows
> SELECT a.*
> FROM tbl_1
> WHERE badge = 0
> ORDER BY NewID()
> SET ROWCOUNT 100 PERCENT
> SELECT a.*
> FROM tbl_1
> WHERE badge = @.iFilter
>
> ~Greg
>
Please always state what version of SQL Server you are using. In SQL Server
2005 the following is possible:
DECLARE @.iNumRows INTEGER, @.iFilter INTEGER
SELECT x, badge
FROM
(SELECT TOP (@.inumrows) x, badge
FROM tbl_1 AS a
WHERE badge = 0
ORDER BY NEWID()
UNION ALL
SELECT x, badge
FROM tbl_1 AS a
WHERE badge = @.iFilter
) AS T ;
To do that in 2000 you would have to use dynamic SQL to construct the TOP
query because TOP doesn't otherwise allow a parameter for the number of
rows.
One alternative might be to insert the first result into a table variable
(use SET ROWCOUNT) and then UNION it with the second result. That would make
me slightly uneasy however, because the effect of ORDER BY in an INSERT
SELECT statement is buggy or at least poorly defined under some conditions.
Failing that I think I would opt for the dynamic query solution or combine
the result sets in the calling code rather than in the proc.
--
David Portas
SQL Server MVP
--|||>> In SQL Server 2005 the following is possible:
Is this a documented construct? To allow ORDER BY in the first SELECT seems
like an oversight, left unchecked in the parser.
--
Anith

appending current Row ID

I am trying to append the current row ID to a string I am trying toinsert via a sproc. I have retrieved the @.@.Identity and I am passing itinto a class with a parameter and calling it using:

Listings.UpdateDB AddNewListing = new Listings.UpdateDB();

AddNewListing.InsertListing (Bathrooms.Text, Bedrooms.Text,Description.Text, Features.ToString(), Address.Text, Price.Text, FN);

I would like to add the current row ID to "FN" like:

Listings.UpdateDB AddNewListing = new Listings.UpdateDB();

AddNewListing.InsertListing(Bathrooms.Text, Bedrooms.Text,Description.Text, Features.ToString(), Address.Text, Price.Text, FN +ID);

Thanks in advance,
Justin.

Why wouldn't your UpdateDB() return the new ID (or set a property)?