Stored Procedure Newbie - 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 Procedure Newbie

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-11-20 : 08:05:44

Gary writes "I am not strong with Stored Procedures and have one that I could use your assistance with, if you have time. I need to add the following table / field to the below stored procedure, and am uncertain where to add it. I am merely attempting to add a field to a report from the table. The table is Proof Job Type and the field is ProofCode. I believe that it has to be declared after the SELECT statement as: dbo.[Proof Job Type].ProofCode.

Another question, is it possible to change a sp to a view? If so, how?

Thanks,

Gary

Alter PROCEDURE billm.spBMCoverSchedule AS

SELECT dbo.BookAll.JobNum,

dbo.[Cover Print Process].CvrPrntProcessID,

dbo.[Cover Print Process].CvrPrntProcess, dbo.BookAll.DateIn,

dbo.[Scheduling - AD].dtPrintCvr2,

dbo.[Scheduling - AD].dtPrintTxt2,

dbo.[Scheduling - AD].TxtPrfStatus,

dbo.[Scheduling - AD].CvrPrfStatus, dbo.BookAll.Author,

dbo.Publisher.PublisherCode,

dbo.[Binding Style].BindStyleAbbr, dbo.BookAll.TotalWidth,

dbo.BookAll.AllCovers + dbo.BookAll.ExtraCvrs AS Quantity,

dbo.[Cover Colors].CvrColor,

dbo.[Scheduling - AD].dtBNBInvRec,

dbo.[Scheduling - AD].dtBound,

dbo.[Scheduling - AD].dtShipped,

dbo.[Scheduling - AD].dtBound2,

dbo.[Scheduling - AD].BNBDelivery,

dbo.[Scheduling-LoadedDates].PrintCoverDue, Hold, HotJob,

'JobDueDate' =

CASE

WHEN [Hold] = 1 THEN '9/9/99'

WHEN ([DaysOnHold] Is Not Null AND [CustomerDelay] = 1 And [Take Off Hold] = 1)

THEN (dbo.BoundBook.BoundBook + [DaysOnHold])

ELSE (dbo.BoundBook.BoundBook)

END,

'CoverStatus' =

CASE

WHEN [CvrPrfStatus] = '28' THEN 'RTP, Needs Plates'

ELSE 'RTP'

END,

'Text Status' =

CASE

WHEN [dtPrintTxt2] is not null THEN 'Text Printed'

When [dtPrintTxt2] is null and [TxtPrfStatus] = '12' Then 'RTP'

ELSE 'Not RTP'

END

FROM dbo.BookAll INNER JOIN

dbo.[Scheduling - AD] ON

dbo.BookAll.JobNum = dbo.[Scheduling - AD].JobNum INNER JOIN

dbo.[Job - ED] ON

dbo.BookAll.JobNum = dbo.[Job - ED].JobNum INNER JOIN

dbo.Publisher ON

dbo.BookAll.PublisherID = dbo.Publisher.PublisherId INNER JOIN

dbo.[Cover Print Process] ON

dbo.BookAll.CvrPrntProcessID = dbo.[Cover Print Process].CvrPrntProcessID

LEFT OUTER JOIN

dbo.[Scheduling-LoadedDates] ON

dbo.BookAll.JobNum = dbo.[Scheduling-LoadedDates].JobNum LEFT

OUTER JOIN

dbo.BoundBook ON

dbo.BookAll.JobNum = dbo.BoundBook.JobNum LEFT OUTER JOIN

dbo.[Hot Jobs] ON

dbo.BookAll.JobNum = dbo.[Hot Jobs].JobNum LEFT OUTER JOIN

dbo.[Cover Colors] ON

dbo.BookAll.CvrColorID = dbo.[Cover Colors].CvrColorID LEFT OUTER

JOIN

dbo.[Binding Style] ON

dbo.BookAll.BindStyleID = dbo.[Binding Style].BindStyleID LEFT

OUTER JOIN

dbo.tblHoldJobs ON

dbo.BookAll.JobNum = dbo.tblHoldJobs.JobNumber

WHERE (dbo.[Scheduling - AD].dtBNBInvRec IS NULL) AND

(dbo.[Scheduling - AD].dtBound IS NULL) AND

(dbo.[Scheduling - AD].dtShipped IS NULL) AND

(dbo.[Scheduling - AD].dtBound2 IS NULL) AND

(dbo.[Scheduling - AD].BNBDelivery IS NULL) AND

(dbo.BookAll.DateIn > CONVERT(DATETIME,

'2002-12-31 00:00:00', 102)) AND

(dbo.[Scheduling - AD].dtPrintCvr2 IS NULL) AND

(dbo.[Cover Print Process].CvrPrntProcessID = 1 OR

dbo.[Cover Print Process].CvrPrntProcessID = 2 OR

dbo.[Cover Print Process].CvrPrntProcessID = 3) AND

(dbo.[Scheduling - AD].CvrPrfStatus = 12 OR

dbo.[Scheduling - AD].CvrPrfStatus = 20 OR

dbo.[Scheduling - AD].CvrPrfStatus = 26 OR

dbo.[Scheduling - AD].CvrPrfStatus = 28)

AND dbo.BookAll.PODScanOnly is null

"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-20 : 12:55:07

Why not build your query in Query Analyzer first? Then once it works correctly, you move it into a stored procedure. But yes columns go in the SELECT part, right before the FROM clause or INTO clause if you have one. I think that you need to do some reading on views and stored procedures as they are quite different. Looking at your query now though, yes this can be moved into a view. But what is the purpose of your stored procedure?

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources