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?

print your SQL statement, copy the result, and try to execute it manually to see if you have an unforeseen problem.\ — Jeremy
– Jeremy, Commented Mar 13, 2015 at 17:20
If your zDP_PM00200SI doesn't generate any output, then you won't see anything other than the query completed successfully message. If it does generate output and you're not seeing it, do what Jeremy said and/or open up your profiler and trace the queries that are executed. — DeadZone
– DeadZone, Commented Mar 13, 2015 at 17:23
@QRiz - make sure you don't pass NULL values to the parameters used in dynamic query — Pரதீப்
– Pரதீப், Commented Mar 13, 2015 at 17:25
If any of your variables are Null, then your entire varchar will be null. Any string plus a null yields a null. Use IsNull(@myVariable,'') to eliminate this. — DeadZone
– DeadZone, Commented Mar 13, 2015 at 17:28

Jim V. · Accepted Answer · 2015-03-13 18:20:33Z

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]

Alsin · Accepted Answer · 2015-03-13 17:38:24Z

0

You can create your own system procedure. Create it in master database, name with sp_ prefix and mark as system one. In a result, you can call it from any database, and SP code will be accessing calling database's objects. It is possible but not recommended way of implementing business logic.

Another way is using dynamic SQL, as you are trying to do. It is a better way to go (from my point of view) but has pros and cons on its own.

answered Mar 13, 2015 at 17:38

Alsin

1,6481 gold badge16 silver badges23 bronze badges

Collectives™ on Stack Overflow

Execute stored procedure with variable database name

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related