stored proc - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

stored proc

Author

Topic

nvisibhm
Starting Member

23 Posts

Posted - 2004-10-12 : 15:43:36

how do you create a stored procedure that does the following
gets data from one table - "a"
loops through "a" row by row doing calculations and
inserts results into another table "b"
then does a final select statement that uses "a", "b", and
another table "c" and returns the results

thank you.

NV

Kristen
Test

22859 Posts

Posted - 2004-10-12 : 15:54:46

Perhaps something like:


CREATE @MyTempTable TABLE
(
...
)

INSERT INTO @MyTempTable
SELECT A, B, C
FROM MyTable
WHERE SomeCondition

UPDATE T
SET T.MyColumn = ResultOfCSomeCalculation
FROM @MyTempTable T
     JOIN MyOtherTable OT
         ON OT.PKCol = T.PKCol

SELECT *
FROM @MyTempTable
    JOIN MyTable MT
       ON MT.PKCol = OT.PKCol
    JOIN MyOtherTable OT
       ON OT.PKCol = OT.PKCol

Kristen

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-10-12 : 15:56:27

the question isn't necessarily how, but why.

Generally, SQL being a set-based language, a set-based solution will be the most efficient. Opening a table to loop through it and do calculations is not set-based. Maybe you should give us a little more specific sample of what you are doing, and someone will point you in the right direction.

Corey

nvisibhm
Starting Member

23 Posts

Posted - 2004-10-12 : 16:02:53

i have a database with almost 7 million recs. i have to produce a report with the following info

top 300 customers
rank custno custname sales YeartoDate% CurrentMonthSales PriorYearRank PriorYearSales and PriorYearMonth sales

the problem comes in getting the rank. it would be faster to populate the rank on both table "A" current sales and table "B" Prior Year sales by looping through temp cursors (since sql server 2000 doesn't have a RANK() function). So I get current sales, add rank, and store in a temp cursor - "A". do the same for prior year sales - temp cursor "B".

After the "A" and "B" are complete, I will need to join them into one result set along with some other info. Make sense. This is the quick and dirty explanation.

NV

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-13 : 05:12:14

well this is one way of doing rank


select 	top 300 rank=count(*), custno, custname, sales
from 	customers t1, customers t2
where 	cast(t1.custno as varchar(50)) + t1.custname >= cast(t2.custno as varchar(50)) + t2.custname
group by custno, custname, sales
order by 1

other way is to use a temp table with identity column.
you then insert your data into the temp table in order you want and identity gives you rank.

Go with the flow & have fun! Else fight the flow

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2004-10-13 : 05:53:57

In this case..
create table #temp (PriorYearRank int identity(1,1), ..and other columns)
insert into #temp (..columns..)
select (..columns..) from B order by sales desc

select top 300 IDENTITY(1,1) as rank, a.custno, a.custname, a.sales, a.YeartoDate% , a.CurrentMonthSales, b.PriorYearRank, b.PriorYearSales , b.PriorYearMonth , b.sales
into #temp2
from A left outer join B on A.id = B.id
order by A.sales desc

select * from #temp2

Hemanth Gorijala
BI Architect / DBA...

Exchange a Dollar, we still have one each.
Exchange an idea, we have TWO each.

nvisibhm
Starting Member

23 Posts

Posted - 2004-10-13 : 08:51:30

How can I make sure the temp table name is unique?

The following statement takes way to long to run since the table is so large.

select top 300 rank=count(*), custno, custname, sales
from customers t1, customers t2
where cast(t1.custno as varchar(50)) + t1.custname >= cast(t2.custno as varchar(50)) + t2.custname
group by custno, custname, sales
order by 1

NV

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-13 : 09:33:45

if there is no temp table this returns null:

select object_id('[tempdb].[dbo].[TempTableName]')

Go with the flow & have fun! Else fight the flow

nvisibhm
Starting Member

23 Posts

Posted - 2004-10-13 : 09:39:59

How do I check that statement to see if a null was returned? Also, isn't there a way to create a random name?

NV

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-13 : 09:57:13

like this:


if exists(select object_id('[tempdb].[dbo].[#temp]'))  
begin
	drop table #temp	
	-- do stuff
end
else
begin
	--do other stuff
end

no i don't this there's a way to create a random name without dynamic sql.
with that you can. but then i think you'd need a global temp table.

Go with the flow & have fun! Else fight the flow

nvisibhm
Starting Member

23 Posts

Posted - 2004-10-13 : 10:08:51

ok, below is what i have. if i hit check syntax, everything is ok, but when i click apply or ok enterprise is locking up. what have i done wrong? thanks for the help.

CREATE PROCEDURE Top300Customers AS

/* Get Prior Year Customer Totals */
IF EXISTS(Select OBJECT_ID('[tempdb].[dbo].[TempPriorSales]'))
DROP TABLE TempPriorSales
GO

/* Get Prior Year Customer Totals */
Select IDENTITY(INT, 1, 1) as PriorYearRank, PriorSales.CustomerNumber,
PriorSales.CustomerName, PriorSales.PriorSales,
PriorYearByMonth.PriorYearMonthlySales INTO #TempPriorSales
From
(
Select CustomerNumber, CustomerName, SUM(SalesAmountUSDollars) PriorSales
From slsMain
Where InvoiceDate >= '01/01/2003' and InvoiceDate < '01/01/2004'
Group By CustomerNumber, CustomerName
) PriorSales
LEFT OUTER JOIN
(
Select CustomerNumber, CustomerName, SUM(SalesAmountUSDollars) PriorYearMonthlySales
From slsMain
Where InvoiceDate >= '09/01/2003' and InvoiceDate < '10/01/2003'
Group By CustomerNumber, CustomerName
) PriorYearByMonth
ON PriorSales.CustomerNumber = PriorYearByMonth.CustomerNumber and
PriorSales.CustomerName = PriorYearByMonth.CustomerName
Order By PriorSales.PriorSales DESC

DECLARE @CurrentYearTotal NUMERIC(18,2), @PriorYearTotal NUMERIC(18,2)

/* Get Current Year's Total Sales */
Select @CurrentYearTotal = SUM(SalesAmountUSDollars)
From slsMain
Where InvoiceDate >= '01/01/2004' and InvoiceDate <= '12/31/2004'

/* Get Prior Year's Total Sales */
Select @PriorYearTotal = SUM(SalesAmountUSDollars)
From slsMain
Where InvoiceDate >= '01/01/2003' and InvoiceDate <= '12/31/2003'

/* Get Current Year Customer Totals and Join with Prior Year Customer Totals.
Add Current Total Sales and Prior Total Sales */
Select CurrentSales.CustomerNumber, CurrentSales.CustomerName,
CurrentSales.CurrentSales, CurrentYearByMonth.CurrentYearMonthlySales,
@CurrentYearTotal AS CurrentTotalSales,
TempPriorSales.PriorYearRank, TempPriorSales.PriorSales,
TempPriorSales.PriorYearMonthlySales,
@PriorYearTotal AS PriorTotalSales
From
(
Select TOP 300 CustomerNumber, CustomerName, SUM(SalesAmountUSDollars) CurrentSales
From slsMain
Where InvoiceDate >= '01/01/2004'
Group By CustomerNumber, CustomerName
Order By SUM(SalesAmountUSDollars) DESC
) CurrentSales
LEFT OUTER JOIN
(
Select CustomerNumber, CustomerName, SUM(SalesAmountUSDollars) CurrentYearMonthlySales
From slsMain
Where InvoiceDate >= '09/01/2004'
Group By CustomerNumber, CustomerName
) CurrentYearByMonth
ON CurrentSales.CustomerNumber = CurrentYearByMonth.CustomerNumber and
CurrentSales.CustomerName = CurrentYearByMonth.CustomerName
LEFT OUTER JOIN
TempPriorSales
ON CurrentSales.CustomerNumber = TempPriorSales.CustomerNumber and
CurrentSales.CustomerName = TempPriorSales.CustomerName
Order By CurrentSales.CurrentSales DESC

/* Drop TempPriorSales Table */
DROP TABLE #TempPriorSales
GO

NV

nvisibhm
Starting Member

23 Posts

Posted - 2004-10-13 : 10:13:13

never mind. i put in the begin end for the if statement and it saved. i will let you know if the procedure works.

NV

nvisibhm
Starting Member

23 Posts

Posted - 2004-10-13 : 10:41:21

ok here is my procedure, but it is not returning any results. what did i leave out? thanks.

CREATE PROCEDURE Top300Customers AS

/* Get Prior Year Customer Totals */
IF EXISTS(Select OBJECT_ID('[tempdb].[dbo].[TempPriorSales]'))
BEGIN
DROP TABLE TempPriorSales
END

/* Get Prior Year Customer Totals */
Select IDENTITY(INT, 1, 1) as PriorYearRank, PriorSales.CustomerNumber,
PriorSales.CustomerName, PriorSales.PriorSales,
PriorYearByMonth.PriorYearMonthlySales INTO #TempPriorSales
From
(
Select CustomerNumber, CustomerName, SUM(SalesAmountUSDollars) PriorSales
From slsMain
Where InvoiceDate >= '01/01/2003' and InvoiceDate < '01/01/2004'
Group By CustomerNumber, CustomerName
) PriorSales
LEFT OUTER JOIN
(
Select CustomerNumber, CustomerName, SUM(SalesAmountUSDollars) PriorYearMonthlySales
From slsMain
Where InvoiceDate >= '09/01/2003' and InvoiceDate < '10/01/2003'
Group By CustomerNumber, CustomerName
) PriorYearByMonth
ON PriorSales.CustomerNumber = PriorYearByMonth.CustomerNumber and
PriorSales.CustomerName = PriorYearByMonth.CustomerName
Order By PriorSales.PriorSales DESC

DECLARE @CurrentYearTotal NUMERIC(18,2), @PriorYearTotal NUMERIC(18,2)

/* Get Current Year's Total Sales */
Select @CurrentYearTotal = SUM(SalesAmountUSDollars)
From slsMain
Where InvoiceDate >= '01/01/2004' and InvoiceDate <= '12/31/2004'

/* Get Prior Year's Total Sales */
Select @PriorYearTotal = SUM(SalesAmountUSDollars)
From slsMain
Where InvoiceDate >= '01/01/2003' and InvoiceDate <= '12/31/2003'

/* Get Current Year Customer Totals and Join with Prior Year Customer Totals.
Add Current Total Sales and Prior Total Sales */
Select CurrentSales.CustomerNumber, CurrentSales.CustomerName,
CurrentSales.CurrentSales, CurrentYearByMonth.CurrentYearMonthlySales,
@CurrentYearTotal AS CurrentTotalSales,
#TempPriorSales.PriorYearRank, #TempPriorSales.PriorSales,
#TempPriorSales.PriorYearMonthlySales,
@PriorYearTotal AS PriorTotalSales
From
(
Select TOP 300 CustomerNumber, CustomerName, SUM(SalesAmountUSDollars) CurrentSales
From slsMain
Where InvoiceDate >= '01/01/2004'
Group By CustomerNumber, CustomerName
Order By SUM(SalesAmountUSDollars) DESC
) CurrentSales
LEFT OUTER JOIN
(
Select CustomerNumber, CustomerName, SUM(SalesAmountUSDollars) CurrentYearMonthlySales
From slsMain
Where InvoiceDate >= '09/01/2004'
Group By CustomerNumber, CustomerName
) CurrentYearByMonth
ON CurrentSales.CustomerNumber = CurrentYearByMonth.CustomerNumber and
CurrentSales.CustomerName = CurrentYearByMonth.CustomerName
LEFT OUTER JOIN
#TempPriorSales
ON CurrentSales.CustomerNumber = TempPriorSales.CustomerNumber and
CurrentSales.CustomerName = TempPriorSales.CustomerName
Order By CurrentSales.CurrentSales DESC

/* Drop TempPriorSales Table */
DROP TABLE #TempPriorSales
GO

NV

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-13 : 10:47:03

change
IF EXISTS(Select OBJECT_ID('[tempdb].[dbo].[TempPriorSales]'))
BEGIN
DROP TABLE TempPriorSales
END
to
IF EXISTS(Select OBJECT_ID('[tempdb].[dbo].[#TempPriorSales]'))
BEGIN
DROP TABLE #TempPriorSales
END

Go with the flow & have fun! Else fight the flow

nvisibhm
Starting Member

23 Posts

Posted - 2004-10-13 : 10:57:57

made the change, but the proc still returns null. do i have to tell the proc what to return? define the final cursor or something?

NV

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-13 : 11:13:02

what do the select statments return if u run then in Query Analyzer?

Go with the flow & have fun! Else fight the flow

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-13 : 11:13:03

what do the select statments return if u run then in Query Analyzer?

Go with the flow & have fun! Else fight the flow

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2004-10-13 : 11:40:51

first of all, you do not need the code to drop the temp table at the begining and end of the proc. They are taken care automatically.

and...

LEFT OUTER JOIN #TempPriorSales
ON CurrentSales.CustomerNumber = TempPriorSales.CustomerNumber and
CurrentSales.CustomerName = TempPriorSales.CustomerName

should be

LEFT OUTER JOIN #TempPriorSales
ON CurrentSales.CustomerNumber = #TempPriorSales.CustomerNumber and
CurrentSales.CustomerName = #TempPriorSales.CustomerName

If you still do not get any data, run the individual queries in QA and see which one is not returning data..

Hemanth Gorijala
BI Architect / DBA...
Exchange a Dollar, we still have ONE each.
Exchange an Idea, we have TWO each.

nvisibhm
Starting Member

23 Posts

Posted - 2004-10-13 : 12:28:23

thanks. i'm running the individual test now. By the way, can i optimize the following query any.
another words calculate the monthly sales and yearly sales for the customer with just one pass on the data.

Select PriorSales.CustomerNumber,
PriorSales.CustomerName, PriorSales.PriorSales,
PriorYearByMonth.PriorYearMonthlySales
From
(
Select CustomerNumber, CustomerName, SUM(SalesAmountUSDollars) PriorSales
From slsMain
Where InvoiceDate >= '01/01/2003' and InvoiceDate < '01/01/2004'
Group By CustomerNumber, CustomerName
) PriorSales
LEFT OUTER JOIN
(
Select CustomerNumber, CustomerName, SUM(SalesAmountUSDollars) PriorYearMonthlySales
From slsMain
Where InvoiceDate >= '09/01/2003' and InvoiceDate < '10/01/2003'
Group By CustomerNumber, CustomerName
) PriorYearByMonth
ON PriorSales.CustomerNumber = PriorYearByMonth.CustomerNumber and
PriorSales.CustomerName = PriorYearByMonth.CustomerName
Order By PriorSales.PriorSales DESC

NV

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources