stored procedure batch ?

[p27br]

Hi,

I'm new to SQL Server. I have transferred some action queries from MS Access to stored procedures in SQL Server. I'd like them to run one after the other but not to start them all manually. Could I put them all into one stored procedure or is there a macro equivalent ?

the sql DB is linked to acces via an access project but the stored procedure time out (the execution time is 6 minutes for one of the SPs)

 

[bowline]

You can use DTS for this without too much work. The interface is graphical and pretty easy to understand...

- Connect to your SQL Server through Enterprise Manager, you will see a folder for "Data Transformation Services" (DTS). Go to "Local Packages" under here.
- Create a new DTS package (right click>> New Package).
- Right click in the resulting screen>> Add Connection, and supply the connection information for your database.
- Then add different "Execute SQL Tasks" for each of your stored procedures
- After all of your items are added, click on the first one you want to run, then holding down your shift key, click on the second one that you want to run. Right click on the second one after both are highlighted and go to Workflow>> On Completion. This will set up sequential processing, just follow those steps for each "Execute SQL Task

 

[donutman]

Tell us about your 6 minute SP. That sounds excessive.
-Karl

 

[p27br]

hi thanks for your answers

bowline : i'll certainly try that out ! seems to be what i need

donutman : here is the stored procedure

CREATE PROCEDURE dbo.StoredProcedure3
AS SELECT Xpo.*, MktShare.[Total MKT], MktShare.MktSH,
[New Prod Prescriber Size] = CASE WHEN [mktsh] = 0 THEN 'Null' WHEN [mktsh] < 0.075 THEN 'Low/Low' WHEN [mktsh] < 0.125 THEN 'Low' WHEN [mktsh]
< 0.25 THEN 'Medium' WHEN [mktsh] < 0.4 THEN 'High' ELSE 'High/High' END
INTO NewXpo
FROM Xpo INNER JOIN
MktShare ON (Xpo. Product = MktShare. Product) AND (Xpo.[Doctor ID] = MktShare.[Doctor ID]) AND (Xpo.Period = MktShare.Period);

GO


what it does is add a new field to a table whose values depend on a calculated field mktshare.
is it best to create separtae queries and include them in the join or use subqueries ?