SQL Server : dynamic database name in stored procedure

Question

I'm creating a stored procedure with dynamic db name. But I can't get it run after I specify the parameters

@BatchStartTime = N'2013/12/26 15:00:00',
@BatchEndTime = N'2013/12/26 15:30:00',
@DBName = N'MYDATABASE'

Here are my sample code:

ALTER PROCEDURE [dbo].[newSpMurexTest]
    @BatchStartTime AS VARCHAR(30),
    @BatchEndTime AS VARCHAR(30),
    @DBName AS VARCHAR(30)
AS  BEGIN
       declare @vwAllTrans nvarchar(max)

       set @vwAllTrans = N'select T.fin_payAmt, C.fin_midAmt, C.myr_equivalent from '+ QUOTENAME(@DBName) +'.dbo.tbl_transaction_history as H 
                       inner join '+ QUOTENAME(@DBName) + '.dbo.tbl_transaction_cancellation as C on H.transaction_id = C.trans_id
                      inner join '+ QUOTENAME(@DBName) +'.dbo.tbl_transaction as T on H.transaction_id = T.transaction_id
                      where  H.dt_last_chg >=  @BatchStartTime  H.dt_last_chg <= @BatchEndTime and (H.new_status IN (7))'

    EXECUTE sp_executesql @vwAllTrans
                          ,'@BatchStartTime AS VARCHAR(30), @BatchEndTime AS VARCHAR(30)'
                          , @BatchStartTime, @BatchEndTime
END

I can run the query if I remove the @vwAllTrans, directly select it and replace the @dbname, @BatchStartTime, and @BatchEndTime.. do I mess up the query when it put it under @vwAllTrans and exec it?

is it possible that your BatchStartTime, BatchEndTime or DBName is null? — PeterRing
– PeterRing, Commented Jan 7, 2014 at 9:07
no, i have make sure that the BatchStartTime, BatchEndTime or DBName is not null..this is microsoft sql server — user2085499
– user2085499, Commented Jan 7, 2014 at 9:08

M.Ali · Accepted Answer · 2014-01-07 09:51:03Z

4

ALTER PROCEDURE [dbo].[newSpMurexTest]
@BatchStartTime  AS NVARCHAR(30),
@BatchEndTime    AS NVARCHAR(30),
@DBName          AS NVARCHAR(30)

AS
BEGIN 
  SET NOCOUNT ON;   

declare @vwAllTrans nvarchar(max)
set @vwAllTrans = N'select T.fin_payAmt, C.fin_midAmt, C.myr_equivalent from '+ QUOTENAME(@DBName) + N'.dbo.tbl_transaction_history as H 
                  inner join '+ QUOTENAME(@DBName) + N'.dbo.tbl_transaction_cancellation as C on H.transaction_id = C.trans_id
                  inner join '+ QUOTENAME(@DBName) + N'.dbo.tbl_transaction as T on H.transaction_id = T.transaction_id
                  where  H.dt_last_chg >=  @BatchStartTime AND H.dt_last_chg <= @BatchEndTime and H.new_status IN (7)'

EXECUTE sp_executesql @vwAllTrans
                      ,N'@BatchStartTime AS NVARCHAR(30), @BatchEndTime AS NVARCHAR(30)'
                      , @BatchStartTime, @BatchEndTime
END

GO

edited Jan 7, 2014 at 9:51

answered Jan 7, 2014 at 9:08

M.Ali

69.9k17 gold badges110 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

M.Ali Over a year ago

Have you tested your query passing hardcoded values does it work then ??

user2085499 Over a year ago

Run it Msg 214, Level 16, State 3, Procedure sp_executesql, Line 1 Procedure expects parameter '@parameters' of type 'ntext/nchar/nvarchar'.

M.Ali Over a year ago

@user2085499 try now I think you had an extra set or paranthesis.

user2085499 Over a year ago

Same error-Msg 214, Level 16, State 3, Procedure sp_executesql, Line 1 Procedure expects parameter '@parameters' of type 'ntext/nchar/nvarchar'. Sorry i'm just started with sql. Yesterday i learn how to insert, today i have to modify this stored procedure..LOL

M.Ali Over a year ago

Change datatype of your variables to NVARCHAR from VARCHAR and prefix any string concatenations with N see my update.

|

Collectives™ on Stack Overflow

SQL Server : dynamic database name in stored procedure

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related