dynamic sql

[vlitim76]

I have a situation in a stored procedure where I need to call another procedure that could be in different databases. The @extDatabase is being passed from the application and I am currently doing the below. question is can I do this dynamically and not have to do an if statement for each database.

if @extDatabase = 'TEST'
     execute @ErrorCode = TEST.dbo.sp_PLay @date 
else if @extDatabase = 'LIVE'
     execute @ErrorCode = LIVE.dbo.sp_PLay @date

Thanks

tim

 

[AlexCuse]

something like this?

declare @sqlStr varchar(500)
set @sqlStr = @extDatabase + '.dbo.sp_PLay ' + @date
exec (@sqlStr)

Hope it helps,

ALex

Ignorance of certain subjects is a great part of wisdom