Solved

Need help creating a stored procedure

Posted on 2016-09-19
8
82 Views
Last Modified: 2016-09-22
I want to create a stored procedure with the following script below. Reason being I want schedule this procedure as a job. How can I do this?

DROP TABLE DBO.WOMAST01B
DROP TABLE DBO.WOLABO01B

SELECT * into WOMAST01B FROM OPENQUERY(ATQAV, 'SELECT * FROM WOMAST01 where 1=0')
SELECT * into WOLABO01B FROM OPENQUERY(ATQAV, 'SELECT * FROM wolabo01 where 1=0')


INSERT INTO WOMAST01B
SELECT * FROM OPENQUERY(ATQAV, 'SELECT * FROM WOMAST01')

INSERT INTO WOLABO01B
SELECT * FROM OPENQUERY(ATQAV, 'SELECT * FROM WOLABO01')



DELETE FROM WOMAST01B
WHERE STEP = 'IV'

DELETE FROM WOMAST01B
WHERE STEP = 'CL'

DELETE FROM WOMAST01B
WHERE STEP = 'PA'

DELETE FROM WOMAST01B
WHERE STEP = 'HD'

DELETE FROM WOMAST01B
WHERE STEP = 'ST'

DELETE FROM WOMAST01B
WHERE STEP = 'EG'

DELETE FROM WOMAST01B
WHERE STEP = 'BK'

DELETE FROM WOMAST01B
WHERE STEP = 'RE'

DELETE FROM WOMAST01B
WHERE STEP = 'RR'

DELETE FROM WOMAST01B
WHERE STEP = 'DR'

DELETE FROM WOMAST01B
WHERE STEP = 'SC'

DELETE FROM WOMAST01B
WHERE STEP = 'ML'

DELETE FROM WOMAST01B
WHERE STEP = 'AM'

DELETE FROM WOMAST01B
WHERE STEP = 'TE'

DELETE FROM WOMAST01B
WHERE STEP = '12'

DELETE FROM WOMAST01B
WHERE STEP = '01'

DELETE FROM WOMAST01B
WHERE STEP = 'QC'

Delete from WOMAST01B 
WHERE RECDATE = '1899-12-30'

Delete from WOMAST01B 
WHERE RECDATE < '2011-01-01'

Open in new window

0
Comment
Question by:maximus1974
  • 2
  • 2
  • 2
  • +1
8 Comments
 
LVL 40

Expert Comment

by:Sharath
ID: 41805683
To create the SP, you can try like this.
create procedure your_proc as 
begin
DROP TABLE DBO.WOMAST01B
DROP TABLE DBO.WOLABO01B

SELECT * into WOMAST01B FROM OPENQUERY(ATQAV, 'SELECT * FROM WOMAST01 where 1=0')
SELECT * into WOLABO01B FROM OPENQUERY(ATQAV, 'SELECT * FROM wolabo01 where 1=0')


INSERT INTO WOMAST01B
SELECT * FROM OPENQUERY(ATQAV, 'SELECT * FROM WOMAST01')

INSERT INTO WOLABO01B
SELECT * FROM OPENQUERY(ATQAV, 'SELECT * FROM WOLABO01')



DELETE FROM WOMAST01B
WHERE STEP = 'IV'

DELETE FROM WOMAST01B
WHERE STEP = 'CL'

DELETE FROM WOMAST01B
WHERE STEP = 'PA'

DELETE FROM WOMAST01B
WHERE STEP = 'HD'

DELETE FROM WOMAST01B
WHERE STEP = 'ST'

DELETE FROM WOMAST01B
WHERE STEP = 'EG'

DELETE FROM WOMAST01B
WHERE STEP = 'BK'

DELETE FROM WOMAST01B
WHERE STEP = 'RE'

DELETE FROM WOMAST01B
WHERE STEP = 'RR'

DELETE FROM WOMAST01B
WHERE STEP = 'DR'

DELETE FROM WOMAST01B
WHERE STEP = 'SC'

DELETE FROM WOMAST01B
WHERE STEP = 'ML'

DELETE FROM WOMAST01B
WHERE STEP = 'AM'

DELETE FROM WOMAST01B
WHERE STEP = 'TE'

DELETE FROM WOMAST01B
WHERE STEP = '12'

DELETE FROM WOMAST01B
WHERE STEP = '01'

DELETE FROM WOMAST01B
WHERE STEP = 'QC'

Delete from WOMAST01B 
WHERE RECDATE = '1899-12-30'

Delete from WOMAST01B 
WHERE RECDATE < '2011-01-01'
end

Open in new window


Do you have lot of data to delete? Why don't you move all the filter conditions into one statement?
create procedure your_proc as 
begin
DROP TABLE DBO.WOMAST01B
DROP TABLE DBO.WOLABO01B

SELECT * into WOMAST01B FROM OPENQUERY(ATQAV, 'SELECT * FROM WOMAST01 where 1=0')
SELECT * into WOLABO01B FROM OPENQUERY(ATQAV, 'SELECT * FROM wolabo01 where 1=0')


INSERT INTO WOMAST01B
SELECT * FROM OPENQUERY(ATQAV, 'SELECT * FROM WOMAST01')

INSERT INTO WOLABO01B
SELECT * FROM OPENQUERY(ATQAV, 'SELECT * FROM WOLABO01')



DELETE FROM WOMAST01B
WHERE STEP IN ('IV', 'CL', 'PA', 'HD', 'ST', 'EG', 'BK', 'RE', 'RR', 'DR', 'SC', 'ML', 'AM', 'TE', '12', '01', 'QC')
OR RECDATE < '2011-01-01'

end

Open in new window

0
 
LVL 24

Expert Comment

by:chaau
ID: 41805783
@Sharath: I think you need to execute DDLs as dynamic SQL statements. Your stored procedure may not compile as the tables WOMAST01B and WOLABO01B may not be available
0
 
LVL 40

Expert Comment

by:Sharath
ID: 41805805
Why dynamic SQL? "SELECT * INTO" shall create the tables. right?
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 41806020
Your code can be easily improved in one single command for each table:
CREATE PROCEDURE sp_MyProcName
AS
	DROP TABLE DBO.WOMAST01B
	DROP TABLE DBO.WOLABO01B

	INSERT INTO WOMAST01B
	SELECT * FROM OPENQUERY(ATQAV, 'SELECT * FROM WOMAST01
		WHERE STEP IN (''IV'',''CL'',''PA'',''HD'',''ST'',''EG'',''BK'',''RE'',''RR'',''DR'',''SC'',''ML'',''AM'',''TE'',''12'',''01'',''QC'')
	   OR RECDATE < ''2011-01-01''')

	INSERT INTO WOLABO01B
	SELECT * FROM OPENQUERY(ATQAV, 'SELECT * FROM WOLABO01')
RETURN

Open in new window

0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 250 total points
ID: 41807069
Here's my adjustments:
1) make sure ANSI_NULLS and QUOTED_IDENTIFIER are set properly
2) check for tables before DROPping them (otherwise you'll get an error if they don't exist)
3) don't copy rows from the remote server that you don't need (that you'll just delete anyway).  Alphabetize the list of STEPs to be excluded to make it easier to adjust / verify the list.


SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE procedure_name
AS
SET NOCOUNT ON;
IF OBJECT_ID('DBO.WOMAST01B') IS NOT NULL
    DROP TABLE DBO.WOMAST01B;
IF OBJECT_ID('DBO.WOLABO01B') IS NOT NULL
    DROP TABLE DBO.WOLABO01B;
   
SELECT * INTO WOMAST01B FROM OPENQUERY(ATQAV, 'SELECT * FROM WOMAST01 where 1=0');
SELECT * INTO WOLABO01B FROM OPENQUERY(ATQAV, 'SELECT * FROM WOLABO01 where 1=0');

INSERT INTO WOMAST01B
SELECT * FROM OPENQUERY(ATQAV, 'SELECT * FROM WOMAST01 WHERE STEP NOT IN(''01'',''12'',''AM'',''BK'',''CL'',''DR'',''EG'',''HD'',''IV'',''ML'',''PA'',''QC'',''RE'',''RR'',''SC'',''ST'',''TE'') AND RECDATE >= ''2011-01-01''');

INSERT INTO WOLABO01B
SELECT * FROM OPENQUERY(ATQAV, 'SELECT * FROM WOLABO01');
GO /*end of proc*/
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 41807154
Hmm, doesn't that code give you only the steps that you would have deleted?!
0
 
LVL 47

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 250 total points
ID: 41808143
Scott is right. I forgot to negate the filter:
CREATE PROCEDURE sp_MyProcName
AS
	DROP TABLE DBO.WOMAST01B
	DROP TABLE DBO.WOLABO01B

	INSERT INTO WOMAST01B
	SELECT * FROM OPENQUERY(ATQAV, 'SELECT * FROM WOMAST01
		WHERE STEP NOT IN (''IV'',''CL'',''PA'',''HD'',''ST'',''EG'',''BK'',''RE'',''RR'',''DR'',''SC'',''ML'',''AM'',''TE'',''12'',''01'',''QC'')
	   OR RECDATE >= ''2011-01-01''')

	INSERT INTO WOLABO01B
	SELECT * FROM OPENQUERY(ATQAV, 'SELECT * FROM WOLABO01')
RETURN

Open in new window

0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question