Solved

Need help creating a stored procedure

Posted on 2016-09-19
8
73 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
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.

 
LVL 46

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:
ScottPletcher 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:ScottPletcher
ID: 41807154
Hmm, doesn't that code give you only the steps that you would have deleted?!
0
 
LVL 46

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

920 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now