execute multiple scripts - 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

General SQL Server Forums

Script Library

execute multiple scripts

Author

Topic

dbonneau
Yak Posting Veteran

50 Posts

Posted - 2012-11-08 : 02:11:09

Hello,

I have about 20 sql scripts in stored procedures in sql sever 2008. I currently open each script, change file name, and type name, and then run the execute button to get the result.
I was wondering if there is a way to automate most of my manual job.

Here is my manual process for this job.
1) First, I import raw data (csv) into SQL server using Import and Export Wizard.
2) Open a script in stored procedure (example code below), type my File_Name( the table I created in 1) )and Mailer_Type name (ex. ppp) in each script.
3) Highlight the syntax from "Begin" to the end of script and change db to the db I am allowed to run, and then execute the script.
4) Copy the output result and paste it in my excel

I do this procedure from 2) to 4) for about 20 to 30 times a day.

I was wondering if there any way to control many SQL scripts without open up each file and run one by one.

Thank you so much for your help.

USE [dev_db]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_ALT_Append_Fields]
AS

BEGIN
SET NOCOUNT ON;
DECLARE @File_Name varchar(50), @Mailer_Type varchar(50)

SET @File_Name = 'Your_mail_file_table_name'
SET @Mailer_Type = 'Choose_value_below'
--PPP
--ZZZ

..............
.............
...........

END

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-08 : 05:57:07

I would move towards complete automation in two or three steps.

First, change the stored proc to accept two parameters, @File_name and @Mailer_Type like shown below:

ALTER PROCEDURE [dbo].[sp_ALT_Append_Fields]
	@File_Name varchar(50),
	@Mailer_Type varchar(50)
AS

BEGIN
SET NOCOUNT ON;
--DECLARE @File_Name varchar(50), @Mailer_Type varchar(50)

--SET @File_Name = 'Your_mail_file_table_name' 
--SET @Mailer_Type = 'Choose_value_below'

Now, instead of step 2 and 3, simply do:

EXEC [dbo].[sp_ALT_Append_Fields] 'YourFilenameHere', 'YourMailerTypeHere';

Once you have that working correctly, use SSIS (SQL Server Integration Services) to import the data, run the stored proc and output the data the way you want it. There is quite a bit to it, but none too hard. You should look up some basic tutorials on SSIS - it is designed to facilitate exactly the kind of tasks that you are trying to do.

dbonneau
Yak Posting Veteran

50 Posts

Posted - 2012-11-08 : 18:17:18

Hi sunitabeck,

Thank you so much for your reply.

Is there a way not to change any syntax on the stored proc but just create some kind of global variables for my file name in different script to run the stored proc ?

Developers update their scripts every day in the stored proc, and I am not supposed to change any of their syntax..

Thank you

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-11-08 : 21:28:22

Then ask the developer to change the stored procedure to allow parameters as sunita suggested.

It does not make sense to hard-code values in the stored procedure when these are suppose to be variable

KH
[spoiler]Time is always against us[/spoiler]

dbonneau
Yak Posting Veteran

50 Posts

Posted - 2012-11-09 : 21:31:57

Hi,

Thank you for your reply.
I wish I could have developer to change the stored proc. But because there are some compliance issues in company i work for, They will not change the code for my needs.

Is there any way to control the stored proc without changing their code ?

Thanks.

quote:
Originally posted by khtan

Then ask the developer to change the stored procedure to allow parameters as sunita suggested.

It does not make sense to hard-code values in the stored procedure when these are suppose to be variable

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-11-10 : 10:02:28

write your own stored procedure ?

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources