TSQL Cursor Fetch and Exec Stored Procedure

Transact-SQL
1

I'm trying to create a Stored Procedure that will loop through a list of Database Names and Exec a Specific Stored Procedure in each database until no more databases exist.

When I try and run this below it throws an error regarding the '..' between the @DBName and the text after, any ideas what I'm missing?

DECLARE @DBName char(5)
DECLARE @DatabaseName as CURSOR;
SET @DatabaseName = CURSOR FOR
SELECT INTERID FROM DYNAMICS..SY01500 WHERE CrmServiceUrl <> '';

OPEN @DatabaseName;
FETCH NEXT FROM @DatabaseName INTO @DBName;

WHILE @@FETCH_STATUS = 0
BEGIN
Exec @DBName +'.._DSI_UpdateAllCompanyBudgets';
FETCH NEXT FROM @DatabaseName INTO @DBName;
END

CLOSE @DatabaseName;
DEALLOCATE @DatabaseName;

I know how to do a For Each Loop in SSIS but in this case I don't want to create an SSIS Package to loop through the databases, I'm hoping I can do it with a Stored Procedure.

Any help is greatly appreciated,

Wally

2

You need parentheses around the EXEC concats:

DECLARE @DBName varchar(50) --char(5) seems rather brave!
DECLARE @DatabaseName as CURSOR;
SET @DatabaseName = CURSOR LOCAL FAST_FORWARD FOR
SELECT INTERID FROM DYNAMICS..SY01500 WHERE CrmServiceUrl <> '';

OPEN @DatabaseName;

WHILE 1 = 1
    FETCH NEXT FROM @DatabaseName INTO @DBName;
    IF @@FETCH_STATUS <> 0
        BREAK;
    EXEC('EXEC [' + @DBName + ']..DSIUpdateAllCompanyBudgets');
END

DEALLOCATE @DatabaseName;
3

I think safer to use QuoteName - in case some nutter has use "[" or "]" in the Database name ...

I think?? it should be OK to put the SProc name into a variable and just EXEC that??

DECLARE @SProcName sysname
...
SELECT @SProcName = QuoteName(@DBName) + '..DSIUpdateAllCompanyBudgets'
PRINT 'Executing ' + COALESCE(@SProcName, 'NULL')    -- Display progress
EXEC @SProcName
1 Like
4

Thank you I used this post. I simply added the Exec ( parentheses and all is good now. Thanks for the assistance.

5

didn't know that can be done this way. Tried and it works.

6

I checked the DOCs to remind myself

-- Stored Procedure
EXEC @retstat = dbo.MySProc @Param1, @Param2
SET @MySProc = 'dbo.MySProc'
EXEC @retstat = @MySProc @Param1, @Param2
-- Dynamic SQL
EXEC ('SELECT * FROM MyTable')	-- Parenthesis are required
SET @MySQL = 'SELECT * FROM MyTable WHERE Col1 = 123'
EXEC (@MySQL)
SET @MySQL = 'SELECT * FROM MyTable WHERE Col1 = '
EXEC (@MySQL + '456')	-- String concatenation only; expressions not permitted
-- Parameterised:
SET @MyNVarcharSQL = 'SELECT * FROM MyTable WHERE Col1 = @Param1'
DECLARE @MyValue int = 789
EXEC sp_ExecuteSQL @MyNVarcharSQL, N'@Param1 int', @Param1 = @MyValue

Dynamic SQL can be Varchar or NVarchar, including (MAX), and the T-SQL string can be 'string' or N'string' ... which is a lot better than trying to concatenate numerous NVarchar(4000) variables on the EXEC line in the old days!!

There's quite a bit of stuff in DOCs that was new to me, or subtlety different. Perhaps it was in the Changes notes ... but it would be nice if there was DOCs with highlight / redlining showing the differences between versions - such that one could flip through reviewing only the changes. We write our product manuals like that ... takes ages! ... hopefully Clients appreciate it!

When did EXECUTE ... AT linked_server come in? DOCs say it was there in SQL 2005 even ... I've been faffing around with alternative ways of doing that - although maybe I'll still need the String & Gum methods!

EXECUTE
(
'CREATE TABLE MyRemoteDB.dbo.MyRemoteTable 
(Col1 int, Col2 varchar(10)) ;'
) AT MyRemoteServer

What about using EXECUTE on a (scalar) UDF? Why would I do that instead of using SELECT?

EXEC @returnstatus = dbo.MyScalarFunction @MyParam1 = 1;

is presumable the same as

SELECT @returnstatus = dbo.MyScalarFunction @MyParam1 = 1;

???

As of SQL2012 you can use EXECUTE to redefine the result set(s) - why would I do that? To change the name of a column that was returned perhaps?

EXEC MySProc @MyParam1 = 1
WITH RESULT SETS
( 
   -- Resultset 1
   ([New Label 1] int NOT NULL,
    [New Label 2] nvarchar(50) NOT NULL,
... etc ...
   ),
   -- Resultset 2
   ([New Label 1] nvarchar(50) NOT NULL,
    [New Label 2] int NOT NULL,
... etc ...
   )
);
1 Like