Clever Geek Handbook

How to create a stored procedure using dynamic function from article

I have a problem with an application that uses the same stored procedure over and over to populate people information in dropdowns. The problem is that sometimes people are gone as the data changes. I have two views that I can use to select, but I want to dynamically change which view is used based on the state of the application.

For new entries, I only want to see people. If I update an existing post, I might want to see all the people, since the existing post might link to people who are no longer working.

How do I pass the name pass into my stored procedure so I can fetch it?

I've already tried adding:

@view varchar(50)

select a, b from @view

But I am getting an error: I have to declare the @view variable.

Is it possible to do this?

0


source to share


7 replies


If you only use two views and know their names, you can simply pass a bit to the procedure.



@useFirstView bit

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


source


You can create your statement in SP and call:



exec sp_executesql @sql
+2


source


I was curious about the same and stumbled upon this thread on the MSDN forums:

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

Take a look at Sami Samir's answer at the end - at least it provided the perfect solution for me, and I hope he does the same for you.

Dynamic SQL to dynamically choose between a fixed number of tables / views that have the same fields just doesn't sound right - difficult to debug and error / hack attacks.

All the best.

+1


source


You can use dynamic SQL to solve your problem.

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

But I don't think 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
0


source


Have you tried declaring a variable in a stored procedure and assigning the parameter as a value?

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

declare @viewName nvarchar (50)

set @viewName = @view

0


source


You just skip the variable declaration

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

or if used in a 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 one as mentioned earlier.

[edit] Fixed using dynamic sql creation with code from Nathan Koop, thanks. [/ Edit]

-1


source


Your idea is tempting, but it IS NOT SUPPORTED (why are people here even suggesting WRITE the variable?) Or in other words, you are doing it wrong.

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

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

Note that this is TABLE, not VARCHAR. Then you can:

SELECT a,b FROM @table.

But obviously this is not what you want. I would suggest using sp_executesql (only if the view / table names are given by you and not from user data or similar) or an IF-ELSE statement.

-1


source






More articles:


All Articles