CODEWITHSUNDEEP
sqlsql-server-2008stored-procedures
user1797079
user1797079

Reputation: 247

stored procedure input parameter

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

Upvotes: 2

Views: 13120

Answers (3)

PeterJ
PeterJ

Reputation: 3792

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]')

Upvotes: 2

Vishal Suthar
Vishal Suthar

Reputation: 17194

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

Upvotes: 2

John Woo
John Woo

Reputation: 263943

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

Upvotes: 4

Related Questions