RE: Auto Number in Stored Procedure 1

[allyne]

Hi Everyone,

I'm using SQL 7.0 and have a stored procedure that currently looks like this:

SELECT
MAX(IcScorecardData063001.Serving_SID) AS Bid,
market_city = MAX(InfoROAM_bid.market_city),
Market_State_Code = MAX(InfoROAM_bid.market_state_code),
Company_Code = MAX(InfoROAM_bid.company_code),
[MIN] = MAX(IcScorecardData063001.[MIN]),
[MOU] = MAX(IcScorecardData063001.MOU),

[On In Region List?]=(CASE
WHEN MAX(InRegionupdated11100.[Sid/Bid]) IS NULL
THEN 'No'
ELSE 'Yes' END),

Expr1=(CASE
WHEN MAX(InRegionupdated11100.[Sid/Bid]) IS NULL
THEN 'Out Of Region'
ELSE 'In Region' END)

FROM MaxBidRev INNER JOIN
InfoROAM_bid ON
MaxBidRev.MaxOfbid_rev = .InfoROAM_bid.bid_rev AND
MaxBidRev.bid = InfoROAM_bid.bid LEFT OUTER JOIN
InRegionupdated11100 ON
InfoROAM_bid.bid = InRegionupdated11100.[Sid/Bid] LEFT
OUTER JOIN
IcScorecardData063001 ON
InRegionupdated11100.[Sid/Bid] = IcScorecardData063001.Serving_SID

Group By IcScorecardData063001.[MIN]
ORDER BY IcScorecardData063001.[MIN],MAXIcScorecardData063001.MOU) DESC

What I would like to do is be able to add another field called Id. This field needs to be like an auto number to number each record in the above stored procedure. Can you show me the correct syntax to create this field without creating a table. If possible I would rather use this stored procedure as a view. It is part of a very large stored procedure that I am revamping and uses the Id number to update information in another table.

Once Again thanks for all your help!
Cathy

 

[tlbroadbent]

Create a temporary table with an Identity column. Insert your records into the table. Select the result set from the temp table.

Make Table #tmp
(
Bid int,
market_city varchar(30),
Market_State_Code char(2),
Company_Code char(2),
MinVal int,
MOU int,
[On In Region List?] char(3),
Expr1 varchar(14),
RecNo Int Identity(1,1)
)

set nocount on

Insert #tmp
SELECT
Bid = MAX(IcScorecardData063001.Serving_SID),
market_city = MAX(InfoROAM_bid.market_city),
Market_State_Code = MAX(InfoROAM_bid.market_state_code),
Company_Code = MAX(InfoROAM_bid.company_code),
MinVal = MAX(IcScorecardData063001.[MIN]),
MOU = MAX(IcScorecardData063001.MOU),

[On In Region List?]=(CASE
WHEN MAX(InRegionupdated11100.[Sid/Bid]) IS NULL
THEN 'No'
ELSE 'Yes' END),

Expr1=(CASE
WHEN MAX(InRegionupdated11100.[Sid/Bid]) IS NULL
THEN 'Out Of Region'
ELSE 'In Region' END)

FROM MaxBidRev INNER JOIN
InfoROAM_bid ON
MaxBidRev.MaxOfbid_rev = .InfoROAM_bid.bid_rev AND
MaxBidRev.bid = InfoROAM_bid.bid LEFT OUTER JOIN
InRegionupdated11100 ON
InfoROAM_bid.bid = InRegionupdated11100.[Sid/Bid] LEFT
OUTER JOIN
IcScorecardData063001 ON
InRegionupdated11100.[Sid/Bid] =IcScorecardData063001.Serving_SID
Group By IcScorecardData063001.[MIN]
ORDER BY IcScorecardData063001.[MIN], MAXIcScorecardData063001.MOU DESC

Select * From #tmp

Drop table #tmp

set nocount off Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[allyne]

Thanks a bunch!

Cathy

 

[tlbroadbent]

You're welcome! Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin