How to run several queries based on the name of each?

Question

A short time ago, I posted a question about how to run several queries based on the name of each.  I have been pretty busy these past few weeks, and have just now had time to follow up on this project.  Anyway, I am trying to think of a way of running SQL Server queries that conform to a certain naming convention.  In Access, I use VBA, such as this:

Sub RunReconciliationQueries()
Dim i As Integer
Dim qry As dao.QueryDef
DoCmd.SetWarnings False
For Each qry In CurrentDb.QueryDefs
    If Left(qry.Name, 3) = "del" Then
        DoCmd.OpenQuery qry.Name
        Debug.Print qry.Name
    End If
Next qry
CurrentDb.TableDefs.Refresh
DoCmd.SetWarnings True
End Sub



Joon84 posted the following suggestion:
DECLARE @SQL nvarchar(max)
    DECLARE _cursor CURSOR FOR
    SELECT SQLStatment FROM Table1 -- Table1 will contain the sql statements in column SQLStatement

    OPEN _cursor
    FETCH NEXT FROM _cursor INTO @SQL

    WHILE @@FETCH_STATUS = 0
    BEGIN
IF(SUBSTRING(@SQL, 1, 3) = 'del')
BEGIN
EXECUTE sp_executesql @SQL
END
        FETCH NEXT FROM _cursor INTO @SQL
        END

CLOSE _cursor
DEALLOCATE _cursor


So, I added a Table, named ‘Table1’, and a field, named ‘SQLStatment’, a I just tried Joon’s suggestion, by hitting F11 multiple times, and ended up with this:
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'delFirstAttempt'.
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'delSecondAttempt'.

Can someone please tell me what I am doing wrong???

---

Ryan Shuell

Answer 1

Do you have procedures definitions in these SQL statements? I think there is a limit of what you can do with this approach.

Also, apparently some of the code is like 'delFirstAttempt' or delSecondAttempt. Are you sure you have these SP in the database you're running against?

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

Hi Ryan,

You cannot do parallel runs from T-SQL.

You can do it in SSIS:

http://blogs.msdn.com/b/sqlperf/archive/2007/05/11/implement-parallel-execution-in-ssis.aspx

You can also setup jobs in SQL Server Agent and launch them from a stored procedure:

http://www.sqlusa.com/bestpractices2005/startjobfromstoredproc/

When you start a job programmatically, the control is returned immediately to the next statement, there is no wait for execution. So in a second you may be able to start 100 jobs or so.

---

Kalman Toth SQL SERVER 2012 & BI TRAINING

Answer 3

Thanks Kalman!

Naomi, what do you mean there is a 'limit'?  Is there a better way to do this?  Is there some kind of 'best practice' for this kind of thing?

I got it working just fine.  I'm pretty happy with the results.  If there is a better way to do this, though, I'd like to know about it.

Thanks everyone!!

---

Ryan Shuell

Answer 4

This is REALLY cool!  I love it!!

I'm wondering how to run this as a Batch job.  I would imagine, in a production setting, something like this is automated be some kinds of SQL Server scripts.  Is that right?  I wouldn't think that someone would be sitting at his/her computer running this process.  I would suspect that it is (somehow) automated . . .

---

Ryan Shuell