Dynamic Execution of Stored Proc

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Dynamic Execution of Stored Proc

Author

Topic

MGouders
Starting Member

30 Posts

Posted - 2009-10-01 : 15:05:22

Trouble using Use Statement and Execution of SP using Variable as Parameter

Can someone please advise on the following:

DECLARE @Database varchar(250)
SET @Database = 'MBX_AE'

use @Database;
GO

DECLARE @DependentObject varchar(250)
SET @DependentObject = 'usp_stars_expand_sup_payout_matrix'
DECLARE @Database varchar(250)
SET @Database = 'MBX_AE'
DECLARE @ThreePartIdentifier varchar(250)
SET @ThreePartIdentifier = @Database + '.dbo.' + @DependentObject

use @Database;
GO

SELECT b.xtype,
b.name,
a.definition
FROM MBX_AE.sys.sql_modules a
JOIN MBX_AE.sys.sysobjects b
ON a.[object_id]
= b.id
AND b.name = @DependentObject

exec sp_depends @ThreePartIdentifier

Error Messages

Msg 102, Level 15, State 1, Line 8
Incorrect syntax near '@Database'.
Msg 137, Level 15, State 2, Line 9
Must declare the scalar variable "@DependentObject".
Msg 137, Level 15, State 2, Line 12
Must declare the scalar variable "@ThreePartIdentifier".

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-01 : 15:09:27

1. Use is not working with vars
2. declared vars are death after a GO

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-01 : 15:11:06

why is your database name passed from variable?

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2009-10-02 : 06:11:02

Not being rude or maybe you only pasted bits of the top.

But you instantiated the stored proc properly etc? doesnt seem like the beginning and end is there in the code above.

quote:
Originally posted by visakh16

why is your database name passed from variable?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-02 : 06:47:52

quote:
Originally posted by cipriani1984

Not being rude or maybe you only pasted bits of the top.

But you instantiated the stored proc properly etc? doesnt seem like the beginning and end is there in the code above.

quote:
Originally posted by visakh16

why is your database name passed from variable?

was that reply to me?

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2009-10-02 : 06:55:43

No, there was no just leave msg quote just clicked reply.

quote:
Originally posted by visakh16

quote:
Originally posted by cipriani1984

Not being rude or maybe you only pasted bits of the top.

But you instantiated the stored proc properly etc? doesnt seem like the beginning and end is there in the code above.

quote:
Originally posted by visakh16

why is your database name passed from variable?

was that reply to me?

mivey4
Yak Posting Veteran

66 Posts

Posted - 2009-10-02 : 08:07:53

Hi MGouders!

I agree with the rest that it seems there may portions of your code missing, but even so there are still flaws with the way you're attempting to execute.

1. You can't make calls to Database objects "outside" the context of being Dynamic T-SQL, it is an illegal operation.

This is Incorrect -> Use @Database
This is Correct ->
DECLARE @Database varchar(250), @sqlCommand VARCHAR(4000)
SET @Database = 'MBX_AE'
SET @sqlCommand = 'USE ' + @Database
EXEC(@sqlCommand)

2. Any time you use the keyword "GO" you essentially are beginning a new scope within your code so any variables declared before that keyword are no longer within scope. They technically no longer exist. This is why you are recieving the errors.

3. The last time I checked, sys.sysobjects is located in the master database and so your attempt to call any sys objects making a direct call to any other database isn't possible

Incorrect -> SELECT b.xtype,
b.name,
a.definition
FROM MBX_AE.sys.sql_modules a
JOIN MBX_AE.sys.sysobjects b
ON a.[object_id]
= b.id
AND b.name = @DependentObject

Correct ->
DECLARE @Database varchar(250), @sqlCommand VARCHAR(4000)
, @DependentObject varchar(250)
SET @Database = 'master'

SET @DependentObject = 'usp_stars_expand_sup_payout_matrix'
SET @sqlCommand = 'SELECT b.xtype,
b.name,
a.definition
FROM ' + @Database + '.sys.sql_modules a
JOIN ' + @Database + '.sys.sysobjects b
ON a.[object_id]= b.id
AND b.name = ''' + @DependentObject + ''''

EXEC(@sqlCommand)

Also, since sys objects are already globally associated with the master database; there is really no need to explicitly use the database name when making calls to them.

I would suggest you cleanup your code keeping the suggestions in mind and instead of using the keyword "USE" in combination with "GO", simply assign the database name as part of the 3-part identifier.

Hope this helps and Good Luck!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources