i am creating a stored procedure in sql server 2008 such as this
-- the code
create procedure proce
@database varchar(50)
as
begin
select * from [@database].[dbo].[sometable]
end
the procedure is compiled
but when i execute the procedure using
-- here i execute it
execute proce 'somedatabase'
it throws an error
-- me gets error :(
Invalid object name '@database.dbo.sometable'
where am i going wrong???????
You cannot directly parameterized the tableName. The only way you can do that is to make a dynamic SQL Statement.
eg,
CREATE PROCEDURE proce @database VARCHAR(50)
AS
BEGIN
DECLARE @SQLQuery AS NVARCHAR(500)
SET @SQLQuery = 'SELECT * FROM [' + @database + '].[dbo].[sometable]'
EXECUTE(@SQLQuery)
END
GO
You can go with this:
DECLARE @string AS NVARCHAR(500)
SET @string = 'select * from [' + @database + '].[dbo].[sometable]'
EXEC (@string)
More more info refer: Dynamic SQL
I don't believe variables are allowed in that context, use the following which I've just confirmed works with your procedure:
exec('select * from [' + @database + '].[dbo].[sometable]')
