CODEWITHSUNDEEP
sqlsql-server
George Lu
George Lu

Reputation: 11

How to access table in another database in T-SQL stored procedure

I can access tables in another database OK using the fully qualified name like

select * from [DEV-test].dbo.ArchiveCutoff

Now I want to do the same table in a stored procedure.

My code is

create procedure test  
    @src varchar (128) 
as
begin
  set @src = '[DEV-test]'
  select * from  @src.dbo.ArchiveCutoff
end

But I get an error:

Msg 102, Level 15, State 1, Line 12
Incorrect syntax near '.'.

What is the correct way to do this.

Upvotes: 0

Views: 1642

Answers (3)

Sean Lange
Sean Lange

Reputation: 33581

I am posting a modification to the previous two answers. I realize in your code you pass in a parameter and then set it to a hardcoded value. I assume that is for testing purposes. The problem is that both of the posted solutions are vulnerable to sql injection once you remove that line. A minor change to both of the excellent previous answers might be like this to prevent sql injection.

create procedure test 
( 
    @src varchar (128) 
)as
    --Presumably this line will be removed after testing. 
    --And because we are using QUOTENAME you want the actual name here with no brackets.
    set @src = 'DEV-test' 

    set @src = QUOTENAME(@src) --This is to help prevent sql injection
    declare @x nvarchar(100)
    set @x = N'select * from ' +  @src +'.dbo.ArchiveCutoff'
    --uncomment the exec line when comfortable your code is correct
    --exec sp_executesql @x

    select @x
GO

Upvotes: 0

AHiggins
AHiggins

Reputation: 7227

You seem to be looking for Dynamic SQL, using one of the textbook examples. This is generally a bad idea, though there are workarounds. If you read the linked article, some suggestions are offered.

If you absolutely have to use it, though, you are looking for

create procedure test  @src varchar (128) as
begin

 set @src = QUOTENAME(@src) -- leave this 
 set @src = '[DEV-test]'

 declare @sql varchar(200)
 set @Sql = 'select * from ' + @src + '.dbo.ArchiveCutoff'

 EXEC (@SQL)


end

or

create procedure test  @src varchar (128) as
begin

 set @src = QUOTENAME(@src) -- leave this 
 set @src = '[DEV-test]'


 EXEC ('select * from ' + @src + '.dbo.ArchiveCutoff')


end

Upvotes: 1

Rahul Tripathi
Rahul Tripathi

Reputation: 172628

Use the inline query

create procedure test 
( @src varchar (128) )as
begin
 set @src = '[DEV-test]'
 declare @x varchar(100)
 @x = 'select * from' +  @src +'.dbo.ArchiveCutoff'
 exec(@x)
end

Upvotes: 0

Related Questions