Execute stored procedure with variable database name

Question

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:

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)

And here is the code I am using to call it:

exec [mdpSupportServices].[dbo].[DGP_addVendor] 'LFD', @index, 'ANT0000001','Anthony Quisenberry',@row out, @error out, '8506 west Rd', '','','Louisville', 'KY', '40247', 'USA',''

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?

Answer 1

So this version of your code shows includes debugging code. Remember that concatenating null values will result in a null value.

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DGP_addVendor]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [DGP_addVendor]
GO


CREATE 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 = NULL 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 ,
        @defaultDate DATETIME ,
        @sql VARCHAR(MAX)

    IF @DEX_ROW_ID IS NULL
        SET @DEX_ROW_ID = -1
    SET @today = CONVERT(DATETIME, '01/01/1900')
    SET @defaultDate = CONVERT(DATETIME, '01/01/1900')

    SET @sql = 'EXEC ' + @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'


    SET @sql = '<?query -- ' + CHAR(13) + COALESCE(@sql,'') + + CHAR(13) + ' --?>'
    SELECT CONVERT(XML, @sql)

    --EXEC (@sql)  

    SELECT  @O_ErrorCode = @@ERROR

GO
DECLARE @index INT ,
    @row INT ,
    @error INT

SET @index = 1

EXEC [dbo].[DGP_addVendor] 'LFD', @index, 'ANT0000001',
    'Anthony Quisenberry', @row OUT, @error OUT, '8506 west Rd', '', '',
    'Louisville', 'KY', '40247', 'USA', ''

SELECT  @row AS [@row], @error AS [@error]