Correct way to create stored procedures using SQL Server 2005 and SQL Server 2008

I must be missing something in SQL Server 2005. I want the attached script to do nothing more than create the stored procedure that I will, in turn, later execute. How do I get it so that when it runs it just creates the stored procedure and does not try to run it? I am missing some basic navigational skills in this new version. So my question is, using SQL Server 2005, how do I structure a script to create a stored procedure? I do not want it to run the commands in the script I just want it to create the script. If you could also comment the proper structure when it comes to checking for the existence of the script that would be helpful. I just want to be able to provide scripts that will either create or alter existing SQL scripts.
CreateTop20VendorListProcedure.sql
LVL 1
rwheeler23Asked:
Who is Participating?
 
Aneesh RetnakaranDatabase AdministratorCommented:
check this
use POWMATQP
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO
SET NOCOUNT ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rbsRebuildTop20Vendors]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	BEGIN
		drop procedure [dbo].[rbsRebuildTop20Vendors]
	END
GO

CREATE PROCEDURE [dbo].[rbsRebuildTop20Vendors] 
AS 
/* Remove all data from work table */
TRUNCATE TABLE TOP20VND

/* Declare cursor to select all historical sales and purchase order transactions */
DECLARE TopVendCurs CURSOR for
	SELECT SH.DOCDATE,S.SOPNUMBE Invoice,S.LNITMSEQ Line,S.QUANTITY,S.XTNDPRCE,S.EXTDCOST, S.ITEMNMBR Item,P.PONUMBER PO,POH.VENDORID,POH.VENDNAME
	FROM [POWMT]..[SOP30300] S
	INNER JOIN [POWMT]..[IV30301] I
	ON S.SOPNUMBE = I.DOCNUMBR and S.LNITMSEQ = I.LNSEQNBR
	INNER JOIN [POWMT]..[IV30300] IP
	ON I.RCPTNMBR = IP.DOCNUMBR AND S.ITEMNMBR = IP.ITEMNMBR
	INNER JOIN [POWMT]..[POP10500] P
	ON P.POPRCTNM = I.RCPTNMBR and IP.LNSEQNBR = P.RCPTLNNM
	INNER JOIN [POWMT]..[POP30100] POH
	ON POH.PONUMBER = P.PONUMBER 
	INNER JOIN [POWMT]..[SOP30200] SH
	ON SH.SOPTYPE = S.SOPTYPE and SH.SOPNUMBE = S.SOPNUMBE
	WHERE S.SOPTYPE = 3 
	ORDER BY S.SOPNUMBE,S.LNITMSEQ

DECLARE @DOCDATE	DATETIME
DECLARE @SOPNUMBE	CHAR(21)
DECLARE @LNITMSEQ	INT
DECLARE @QUANTITY	DECIMAL(19,5)
DECLARE @XTNDPRCE	DECIMAL(19,5)
DECLARE @EXTDCOST	DECIMAL(19,5)
DECLARE @ITEMNMBR	CHAR(50)
DECLARE @PONUMBER	CHAR(17)
DECLARE @VENDORID	CHAR(15)
DECLARE @VENDNAME	CHAR(65)



/* For every transaction make sure only the transaction for the first sales detail line is written to work table */
OPEN TopVendCurs

FETCH NEXT FROM TopVendCurs INTO @DOCDATE,@SOPNUMBE,@LNITMSEQ,@QUANTITY,@XTNDPRCE,@EXTDCOST,@ITEMNMBR,@PONUMBER,@VENDORID,@VENDNAME
WHILE (@@FETCH_STATUS = 0 )
BEGIN
    IF NOT EXISTS (SELECT * FROM TOP20VND WHERE SOPNUMBE = @SOPNUMBE and LNITMSEQ = @LNITMSEQ)
    BEGIN
        INSERT INTO TOP20VND
		(DOCDATE,SOPNUMBE,LNITMSEQ,QUANTITY,XTNDPRCE,EXTDCOST,ITEMNMBR,PONUMBER,VENDORID,VENDNAME)
	Values
	(@DOCDATE,@SOPNUMBE,@LNITMSEQ,@QUANTITY,@XTNDPRCE,@EXTDCOST,@ITEMNMBR,@PONUMBER,@VENDORID,@VENDNAME)
    End
        
    FETCH NEXT FROM TopVendCurs INTO @DOCDATE,@SOPNUMBE,@LNITMSEQ,@QUANTITY,@XTNDPRCE,@EXTDCOST,@ITEMNMBR,@PONUMBER,@VENDORID,@VENDNAME

End
Close TopVendCurs
    
DEALLOCATE TopVendCurs
GO -------------------------------this will be the end of the procedure 

/* ADD ALL OPEN SALES ORDER LINES THAT ARE DIRECTLY TIED TO A PURCHASE ORDER LINE TO THE WORK TABLE */
INSERT INTO TOP20VND (DOCDATE,SOPNUMBE,LNITMSEQ,QUANTITY,XTNDPRCE,EXTDCOST,ITEMNMBR,PONUMBER,VENDORID,VENDNAME)
SELECT SH.DOCDATE,SL.SOPNUMBE Invoice,SL.LNITMSEQ Line,SL.QUANTITY,SL.XTNDPRCE,SL.EXTDCOST,SL.ITEMNMBR Item,POL.PONUMBER PO,POH.VENDORID,POH.VENDNAME
FROM [POWMT]..[SOP10200] SL
INNER JOIN [POWMT]..[SOP60100] SP
 ON SL.SOPTYPE = SP.SOPTYPE AND SL.SOPNUMBE = SP.SOPNUMBE and SL.LNITMSEQ = SP.LNITMSEQ
INNER JOIN [POWMT]..[POP10110] POL
 ON SP.PONUMBER = POL.PONUMBER AND SP.ORD=POL.ORD
INNER JOIN [POWMT]..[POP10100] POH
 ON POL.PONUMBER = POH.PONUMBER
INNER JOIN [POWMT]..[SOP10100] SH
 ON SH.SOPTYPE = SP.SOPTYPE and SH.SOPNUMBE = SP.SOPNUMBE
WHERE SH.SOPTYPE = 2 

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
SET NOCOUNT OFF 
GO

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.