title: Execute stored procedure with variable database name
body: <p>So I have created a stored procedure that hits a Dynamics GP Vendor table. It needs to be able to hit the same table in different databases. One of the possible solutions I saw said to just execute a parameter with the query written as a varchar. Here is the code actually in the stored procedure:</p>
  
  <pre><code>Procedure [dbo].[DGP_addVendor]
  @dbName varchar(4) = NULL
  ,@NoteIndex numeric(19,5) = NULL
  ,@VENDORID char(15) = NULL
  , @VENDNAME char(65) = NULL
  , @DEX_ROW_ID int = NULL output  
  , @O_ErrorCode int = 0 output
  , @ADDRESS1 char(61) = ''
  , @ADDRESS2 char(61) = ''
  , @ADDRESS3 char(61) = ''
  , @CITY char(35) = ''
  , @STATE char(29) = ''
  , @ZIPCODE char(11) = ''
  , @COUNTRY char(61) = ''
  , @PHNUMBR1 char(21) = '' as
  
  declare  @today datetime = convert(datetime,'01/01/1900')
  declare  @defaultDate datetime = convert(datetime,'01/01/1900')
  --declare @DEX int = null
  Declare @sql varchar(MAX) = @dbName + '.dbo.zDP_PM00200SI
       '''+@VENDORID+'''
      ,'''+@VENDNAME+'''
      ,'''+@VENDNAME+'''
      ,'''+@VENDNAME+'''
      ,''PRIMARY''
      ,''PRIMARY''
      ,''PRIMARY''
      ,''PRIMARY''
      ,''''
      ,''''
      ,'''+@ADDRESS1+'''
      ,'''+@ADDRESS2+'''
      ,'''+@ADDRESS3+'''
      ,'''+@CITY+'''
      ,'''+@STATE+'''
      ,'''+@ZIPCODE+'''
      ,'''+@COUNTRY+'''
      ,'''+@PHNUMBR1+'''
      ,''''
      ,''''
      ,''''
      ,''''
      ,''''
      ,''''
      ,''''
      ,''''
      ,1
      ,''''
      ,''''
      ,''''
      ,0
      ,1
      ,0
      ,0
      ,''''
      ,0
      ,0
      ,0
      ,0
      ,0
      ,''''
      ,''''
      ,''''
      ,''''
      ,0
      ,''''
      ,1
      ,1
      ,1
      ,1
      ,0
      ,0
      ,1
      ,1
      ,0
      ,0
      ,0
      ,''''
      ,'+Convert(varchar,@defaultDate)+'
      ,'+Convert(varchar,@defaultDate)+'
      ,0
      ,0
      ,0
      ,0
      ,0
      ,0
      ,0
      ,0
      ,0
      ,0
      ,0
      ,0
      ,0
      ,0
      ,0
      ,0
      ,0
      ,'+Convert(varchar,@NoteIndex)+'
      ,''''
      ,'+Convert(varchar,@today)+'
      ,'+Convert(varchar,@today)+'
      ,''''
      ,1
      ,0
      ,1
      ,''''
      ,''''
      ,0
      ,0
      ,''''
      ,0
      ,0
      ,0
      ,0
      ,0
      ,0
      ,'+Convert(varchar,@defaultDate)+'
      ,0
      ,''''
      ,''''
      ,0
      ,9
      ,0
      ,''PRIMARY''
      ,' + Convert(varchar,@DEX_ROW_ID) + ' out'
  
  
          EXEC(@sql)
  </code></pre>
  
  <p>And here is the code I am using to call it:</p>
  
  <pre><code>exec [mdpSupportServices].[dbo].[DGP_addVendor] 'LFD', @index, 'ANT0000001','Anthony Quisenberry',@row out, @error out, '8506 west Rd', '','','Louisville', 'KY', '40247', 'USA',''
  </code></pre>
  
  <p>It doesn't appear to do anything. If I am on the wrong track for doing this can someone point me in a better direction? </p>