[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 167
  • Last Modified:

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
0
rwheeler23
Asked:
rwheeler23
1 Solution
 
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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now