How to run several Queries based on the name of each

Question

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

How can I automate SQL Server to do essentially the same???

---

Ryan Shuell

Answer 1

maybe with the help of dynamic query. you can store the sql statments in a table, read the value using a loop/ cursor and check first 3 character of the query. if it contains del or whatever, you can perform the desired operation with EXECUTE sp_executesql "sql query".

      

    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

regards

joon

Answer 2

I finally had time to try this.  It works perfect!!  Thanks so much!!

One more thing, 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

Answer 3

you can create sql jobs, where either you can add stored proc OR direct sql queries to run. You can schedule the job to run at your desired timing.

regards

joon

Answer 4

Perfect!  Thanks again!!

---

Ryan Shuell