How to create a stored procedure with dynamic from clause

Question

I have a problem with an application which uses the same stored procedure over and over again to populate people information in dropdown lists. The problem is that sometimes people aren't there anymore as the data changes. I have two views I can use to select from, but I want to dynamically change which view is being used based on the state of the application.

For new records, I only want to see current people. If I'm updating an existing record, I may want to see all people since the existing record may reference someone who is not current anymore.

How would I go about passing the view name in to my stored procedure so I can select from it?

I've already tried adding:

@view varchar(50)

select a, b from @view

But I get an error stating I must declare the variable @view.

Is this even possible to do?

Answer 1

If you use only two views and you know their names, you could just pass a bit parameter to a procedure.

@useFirstView bit

IF @useFirstView = 1 
   -- select from firstView
ELSE
   -- select from secondView

Answer 2

You can create your statement in the SP and call:

exec sp_executesql @sql

Answer 3

You can use dynamic SQL to solve your issue.

DECLARE @sqlCommand varchar(1000)
SET @sqlCommand = 'SELECT a, b from FROM ' + @view
EXEC (@sqlCommand)

But I don't think that this is the best solution. You should probably have a query like

If @IsActiveFlag = TRUE THEN
  SELECT * FROM People WHERE IsActive = 1
ELSE
  SELECT * FROM People
ENDIF

Answer 4

Have you tried declaring a variable within the stored procedure and assigning the parameter as it's value?

For example, assuming you have a parameter named @view:

declare @viewName nvarchar(50)

set @viewName = @view

Answer 5

You just miss the declaration of you variable

DECLARE @view VARCHAR(50)
SET @view = 'myView'
SELECT a,b FROM @view

or if used in an stored procedure

CREATE PROCEDURE SelectFromView
{
    @view VARCHAR(50)
}
AS
BEGIN

    DECLARE @sqlCommand varchar(1000)
    SET @sqlCommand = 'SELECT a, b from FROM ' + @view
    EXEC (@sqlCommand)

END

Beware of SQLInjection attacs on this, like mentioned before.

[edit] Corrected use of dynamic sql creation with the code from Nathan Koop, thx. [/edit]

Answer 6

Your idea is tempting, but this is NOT SUPPORTED (why do people here even suggest to DECLARE a variable?) or, in other words, you're doing it the wrong way.

The FROM clause accepts either a table/view/function name (hardcoded, not from a varchar variable) or a table variable. A table variable looks like this:

DECLARE @table TABLE (a int, b int).

Note that it is of type TABLE, not VARCHAR. After that you can do:

SELECT a,b FROM @table.

But obviously this is not what you want. I would suggest to use sp_executesql (only if view/table names are set by you, not coming from user data or similar) or an IF-ELSE statement.

Answer 7

I was wondering about the exact same thing and I came across this thread in the MSDN forums:

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/fa523f21-5a8b-421f-99ce-9bb2ec30e848

Take a look at the answer of Sami Samir in the end - at least for me it provided a perfect solution and I hope it will do the same for you.

Dynamic SQL for a dynamic choice between a fixed number of tables/views which have the same fields just doesn't sound right - hard to debug and error/hack attack-prone.

All the best.