September 21, 2006 at 11:24 am
When we access a different database from a stored procedure or trigger, we need to qualify the table name with the database name. For example, to access database TestDB I would do the following :
Update TestDB..Orders set amount = @amount where ordernum = @ordernum
I've used dynamic Sql to generate certain select and update commands; however, I can't figure out how to dynamically include the database qualifier.
For example, in my proc I have this code to build an Update cmd:
if isNull(@pSqlTable, '') <> ''
SET @SqlStr = 'Update ' + @pSqlTable + ' SET ' + @pSqlSet
if isNull(@pSqlWhere, '') <> ''
SET @SqlStr = @SqlStr + ' WHERE ' + @pSqlWhere
EXEC sp_executeSql @SqlStr
I've tried something like this to soft-code the database but it doesn't work (I forget the error):
SET @pDBName = 'TestDB'
SET @SqlStr = 'Update ' + @pDBName + '..' + @pSqlTable + ' SET ' + @pSqlSet
CAn anyone give me some advice on this ?
Thank you,
Bob
September 21, 2006 at 11:58 am
If the owner is 'dbo', your statement should work otherwise include (replace .. with .<>.) the schema name
September 25, 2006 at 3:53 am
If you need to assign a variable to the database name you might just as well hardcode it avoid using dynamic sql.
select * from pubs.dbo.testrs
or
declare @string varchar(250),@db varchar(25)
set @db='pubs'
set @string= 'select * from '+@db+'.dbo.testRS'
exec(@string)
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy