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.
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
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
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
