Microsoft SQL Server forum - How to exec stored proc dynamically

Author

How to exec stored proc dynamically

sjoshi

2005-06-30, 11:23 am

Hello
I have 2 procedures setup in master database, sp_RebuildIndexesMai
n and
sp_RebuildIndexesSub

The Sub just shows and execute DBCC commands for passed database
context

sp_RebuildIndexesSub
(@listOnly bit=0, @maxfrag Decimal=30.0)

This runs fine if I do pubs.. sp_RebuildIndexesSub

However when run thru. the Main proc, I get Incorrect syntax near
'pubs'.
The main proc is

Create Proc sp_RebuildIndexesMai
n(@dbName sysname, @listOnly bit=0,
@maxFrag Decimal=30.0)
As
Begin
Set NOCOUNT ON

Declare crDbs CURSOR For
Select CATALOG_NAME From INFORMATION_SCHEMA.SCHEMATA
Where CATALOG_NAME NOT IN ('tempdb', 'master', 'msdb', 'model',
'distribution', 'Northwind', 'pubs')
And CATALOG_NAME Like @dbName

Declare @execstr nvarchar(2000)

Open crDbs
Fetch crDbs INTO @dbName
If (@@FETCH_STATUS<>0) --Then no matching databases
Begin
Close crDbs
Deallocate CrDbs
Print 'No databases were found that match ''' + @dbName + ''''
Return -1
End

While(@@FETCH_STATUS
=0)
Begin
Print Char(13) + 'Rebuilding indexes on ' + @dbName
Print Char(13)
Set @execstr = @dbName + '.. sp_RebuildIndexesSub
'
EXEC sp_executesql @execstr, N'@listOnly bit, @maxFrag Decimal',
@listOnly, @maxFrag
Fetch crDbs INTO @dbName
End
Close crDbs
Deallocate CrDbs
Return 0
End

thanks
Sunit
sunitjoshi@netzero.com

joshsackett

2005-06-30, 11:23 am

I believe if you change:
Set @execstr = @dbName + '.. sp_RebuildIndexesSub
'
to
Set @execstr = '[' + @dbName + '.. sp_RebuildIndexesSub
] '

it should work.

Personally, instead of creating sp_RebuildIndexesSub
in each database,
you should just create it in the master database. Then run a job like
so:

sp_msforeachdb 'USE ? if db_id(''?'') > 4
BEGIN
Print Char(13) + 'Rebuilding indexes on ' + ?
exec sp_RebuildIndexesSub
0, 30.0
END'

Be sure not to run "exec master.. sp_RebuildIndexesSub
0, 30.0" or else
it will only run the master database during each loop.

Modify to your heart's content.

Sunit Joshi

2005-06-30, 11:23 am

Now it says
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'SPlant5_MODEL.. sp_RebuildIndexesSub
'.

The stored procedure are setup in the master db. That's why I'm using
the dbname..spname to change db context.

thanks
Sunit

*** Sent via Developersdex http://www.droptable.com ***

joshsackett

2005-06-30, 11:23 am

Don't use sp_executesql. The problem stems from you trying to run a
stored procedure through a stored procedure. So instead, build your
string first and run it by using EXEC(@execstr).

SET @execstr = 'USE ' + @dbname + ' exec sp_RebuildIndexesSub
' +
RTRIM(@listOnly) + ',' + RTRIM(@maxFrag)
EXEC (@execstr)

sjoshi

2005-06-30, 1:24 pm

Got it. Had to change to this

Set @execstr = @dbName + '.. sp_RebuildIndexesSub
'
Exec @execstr @listOnly, @maxFrag

thanks
Sunit

joshsackett

2005-06-30, 1:24 pm

You are right. Your code is much cleaner :)

Sponsored Links