passing table name as paramenter in SP

Hello,
What is the syntax to pass a table name to a stored procedure?
I know how to pass other variables, but can't get this to work..
Right now I have (and it doesn't work):
CREATE PROCEDURE storedProc1((@.rateID int, @.table varchar(20))
AS
SELECT *
FROM
@.table --this is the problem here
WHERE ID_LOGGING_RATE = @.rateID
RETURN
GO
Can someone tell me how to make this work?
Thanks!
AmberAmber,
Here is a sample:
USE PUBS
GO
CREATE PROC USP_TEST2
@.TABLENAME NVARCHAR(50)
AS
DECLARE @.SQL NVARCHAR(500)
SET @.SQL = 'SELECT * FROM ' + @.TABLENAME
EXEC (@.SQL)
GO
EXEC USP_TEST2 N'AUTHORS'
Also read:
http://www.sommarskog.se/dynamic_sql.html
HTH
Jerry
"amber" <amber@.discussions.microsoft.com> wrote in message
news:BCB08E66-0FB8-4D1A-9974-EF6C738D3EF3@.microsoft.com...
> Hello,
> What is the syntax to pass a table name to a stored procedure?
> I know how to pass other variables, but can't get this to work..
> Right now I have (and it doesn't work):
> CREATE PROCEDURE storedProc1((@.rateID int, @.table varchar(20))
> AS
> SELECT *
> FROM
> @.table --this is the problem here
> WHERE ID_LOGGING_RATE = @.rateID
> RETURN
> GO
>
> Can someone tell me how to make this work?
> Thanks!
> Amber|||You will have to use dynamic sql to accomplish what you want. Passing the
table's name as a parameter to create a dynamic sql statement is not
considered a good practice.
Example:
CREATE PROCEDURE storedProc1
@.rateID int,
@.table sysname
AS
set nocount on
declare @.sql nvarchar(4000)
set @.sql = N'SELECT * FROM ' + quotename(@.table) + N' WHERE ID_LOGGING_RATE
= @.rateID'
exec sp_executesql @.sql, N'@.rateID int', @.rateID
RETURN @.@.error
GO
The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
AMB
"amber" wrote:

> Hello,
> What is the syntax to pass a table name to a stored procedure?
> I know how to pass other variables, but can't get this to work..
> Right now I have (and it doesn't work):
> CREATE PROCEDURE storedProc1((@.rateID int, @.table varchar(20))
> AS
> SELECT *
> FROM
> @.table --this is the problem here
> WHERE ID_LOGGING_RATE = @.rateID
> RETURN
> GO
>
> Can someone tell me how to make this work?
> Thanks!
> Amber|||There are good reasons why this doesn't work as you expected it to. Passing
table names or column names as variables is a very bad idea and isn't
supported at all by standard SQL. Doing this undermines the usefulness of
server-side code, degrades performance and weakens the security model.
With good design it shouldn't usually be necessary to parameterize metadata.
Why don't you know the names of your tables at design time?
If you think you have to do this then you'll have to resort to dynamic SQL
with all that implies. Read and understand the following article first:
http://www.sommarskog.se/dynamic_sql.html
David Portas
SQL Server MVP
--
"amber" <amber@.discussions.microsoft.com> wrote in message
news:BCB08E66-0FB8-4D1A-9974-EF6C738D3EF3@.microsoft.com...
> Hello,
> What is the syntax to pass a table name to a stored procedure?
> I know how to pass other variables, but can't get this to work..
> Right now I have (and it doesn't work):
> CREATE PROCEDURE storedProc1((@.rateID int, @.table varchar(20))
> AS
> SELECT *
> FROM
> @.table --this is the problem here
> WHERE ID_LOGGING_RATE = @.rateID
> RETURN
> GO
>
> Can someone tell me how to make this work?
> Thanks!
> Amber