Use variables expansions in schema in sql server 2005 / 2008

Question

I am trying to create stored procedure which will decide which language to use based on a parameter passed?
How can I do something like this ?

declare @en varchar(50) = 'en'
declare @fi varchar(50) = 'fi'

select * from [@en].[TestingLanguagesInNameSpacesDelMe]
select * from [@fi].[TestingLanguagesInNameSpacesDelMe]

Answer 1

You could use Dynamic SQL:

DECLARE @SQL varchar(MAX)
SELECT @SQL = 'select * from ['
              + @LANG
              + '].[TestingLanguagesInNameSpacesDelMe]'
sp_executesql @SQL

But I would consider using a SINGLE table with an indexed COLUMN with the language:

select * from [dbo].[TestingLanguagesInNameSpacesDelMe] where [lang] = @LANG

Answer 2

How about this?

declare @sqlCall varchar(200)

set @sqlCall = 'select * from [' + @language + '].[TestingLanguagesInNameSpacesDelMe]'

exec @sqlCall

I would have to agree with the other posted answer, make a column that indicates the language and just filter on the language..

Answer 3

-- Yep I also concluded that it is the dynamic sql ..
declare @tableName varchar(50) 
set @tableName ='TestingLanguagesInNameSpacesDelMe'



declare @sql nvarchar(200)= 'select * from ' 
declare @fi varchar(40) = 'fi'
declare @en varchar(50) = 'en'

set @sql = @sql + '[' + @fi + '].[' + @tableName + ']'
print @sql
exec sp_executesql @sql

Answer 4

I would also advocate a single-table/language-segmented design where all the languages are stored in a single table with a language column.

Dynamic SQL may be potentially vulnerable to SQL injection. If the @LANG variable cannot be trusted (or if the schema and table need to be validated to be a valid combination), you can check it against sys.schemas using a technique like this:

DECLARE @template AS varchar(max)
SET @template = 'SELECT * FROM {object_name}'

DECLARE @object_name AS sysname

SELECT @object_name = QUOTENAME(s.name) + '.' + QUOTENAME(o.name)
FROM sys.objects o
INNER JOIN sys.schemas s
    ON s.schema_id = o.schema_id
WHERE o.object_id = OBJECT_ID(QUOTENAME(@LANG) + '.[TestingLanguagesInNameSpacesDelMe]')

IF @object_name IS NOT NULL
BEGIN
    DECLARE @sql AS varchar(max)
    SET @sql = REPLACE(@template, '{object_name}', @object_name)
    EXEC (@sql)
END

Now you have the power and flexibility of dynamic sql, but it is not vulnerable to injection.