Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2009
    Posts
    8

    Unanswered: Dynamic Execution of Stored Proc

    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".

  2. #2
    Join Date
    Jan 2003
    Location
    British Columbia
    Posts
    44
    You can't call the Use statement with a variable.
    You can only use sp_depends to display info from the current database context.

    Build the entire sql batch and execute it.

    Declare @Database varchar(250)
    Declare @Object varchar(250)
    Declare @SQL varchar(1000)

    Set @Database = 'Northwind'
    Set @Object = 'Customers'
    Set @SQL = 'Use ' + @Database + '; exec sp_depends ' + @Object

    exec (@SQL)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •