You could do this:

IF @tbl = 'Table1'
SELECT ...
FROM Table1

IF @tbl = 'Table2'
SELECT ...
FROM Table2

but what's the point? It's much more efficient to create separate
stored procs for each table and call them by name rather than pass the
name as a parameter.

Maybe you meant that you won't know the table name until runtime. But
why? In a business process type application permanent tables should be
static at runtime and change control procedures should cope with any
changes in development. In a DBA tool you might want to parameterize
object names but in those cases Dynamic SQL is usually an acceptable
solution.

--
David Portas
SQL Server MVP
--

Why not consolidate the user information in one central DB? In each
application DB just create a view that references the central Users
table. That way you only maintain users in one place.

Dynamic SQL weakens the security model by forcing you to grant
permissions on tables. This is one of many reasons to avoid Dynamic SQL
in production code.

--
David Portas
SQL Server MVP
--

I have a table that holds an SQL database name, and a table name. I want to
parse the names and use them to reference a table in a select statement:

someting like this:

SET @DB = 'MyDB'
SET @TBL = 'MyTBL'

SET @TABLE = @DB + '.dbo.' + @TBL

SELECT * FROM @TABLE

Is there any way to accomplish this without using dynamic sql?

Thanks

Patrick

David,

Thank you for your input.

The application is a global login app that manages users access to
authorized applications by updating user access tables in the authorized
db's. A master app access table links the master user ids to specific
applications which include the app db name and application user access
table. If the master user is deleted, or added, or updated in the global
login app, I need to pull a list of authorized apps via the master app
access table and update those tables as well. You are correct, i need this
at run-time.

The solution that seems to work is to EXEC a parsed stored proc:

@db = 'MyDB'

EXEC (@db + '.dbo.DeleteUserByMasterId @ID = ' + @ID)

I thought this would be a problem with our security model of (only giving
access to the db tables via exec rights on the stored procedures). This was
based on recent issues i was having with dynamic sql and having to give
permission to the tables and views.

I hesitate to hard code the tables in stored procedures. It seems it would
be easier to add new applications by adding the info to a table rather then
changes to the db objects.



Patrick

[quoted text, click to view]