SQLTeam.com | Weblogs | Forums

Macro in Microsoft SQL studio management


#1

Is it possible to create a Macro in SQL which will start 5 Queries one by one like in Access or there is another way how to do this in Microsoft SQL studio management?


#2

I presume you mean five separate queries, rather than "Query 2 uses the results from Query 1" (it was a long time ago!! but I seem to remember that was also possible in Access - if that's what you want you need a CTE)

You can make a Stored Procedure

CREATE PROCEDURE MySPName
AS

SELECT Col1, Col2, ...
FROM MyTable
WHERE Col3 = 'ABC'

SELECT ColA, ColB, ...
FROM MyTable2
WHERE ColC = 123

then to "run" that "macro" you would do:

EXEC MySPName

You can also pass parameters to the SProc:

CREATE PROCEDURE MySPName
    @MyParam1 INT,
    @MyParam2 varchar(10) = 'MyDefaultValue'
AS

SELECT Col1, Col2, ...
FROM MyTable
WHERE Col3 = @MyParam2


SELECT ColA, ColB, ...
FROM MyTable2
WHERE ColC = @MyParam1
...

If a @Parameter definition does NOT have a default value then it is Required in the EXEC statement, otherwise it is optional

EXEC MySPName @MyParam1 = 123
or
EXEC MySPName @MyParam1 = 123, @MyParam2 = 'ABC'
or even
EXEC MySPName @MyParam2 = 'ABC', @MyParam1 = 123

Note: You do not need to provide the parameter names provided that you sequence the values in the same order as the definition of the SProc, so this would do:

EXEC MySPName 123
or
EXEC MySPName 123, 'ABC'

but I strongly recommend that you don't do this. At some point you will add a new parameter to the definition, or get the order wrong, and it just leads to grief, so I suggest you take the time to put the @Parameter names in the EXEC statement.

If your SProc will do some Updates / Inserts / Deletes (as in "more than one") then I recommend that you learn about using a transaction. You can "Begin" a transaction, do all of the updates, and then "Commit" the transaction. If something fails along the way (syntax error, SQL error, or logic in your program wants to "bail out") either SQL will implicitly issue a Rollback (e.g. if you had a power failure, or a Syntax error that aborted the Sproc) or you could issue an Explicit Rollback in your code. That way either ALL the changes would be made to the database, or NONE of them ... integrity of your database will be ensured.