Dynamic SQL with variables inside a view (SQL Server)

Question

Hello I'm essentially trying to do this inside a new view window in SQL Server 2008:

declare @var = (select db from databases); exec ('select name from ' + @var ' + .dbo.Names);

This view actually runs in SQL Server but I cannot save it (it gives me an error), I could potentially just create a table returning function, do all of this same stuff in it and return the table and create a view that basically takes everything from that table but I was unsure of performance hits that could occur from doing this. Any suggestions would be greatly appreciated! Thanks.

Solution: I just ended up having it drop the old view and recreate a new view (using dynamic sql) in a Stored Procedure. When that value is changed I will just call the SP which will update the views to point to the correct databases. Thanks for all the help guys, knowing what can't be done stopped me from trying those methods.

This is not valid inside a view and AFAIK you can't use dynamic SQL in UDFs either. — Martin Smith
– Martin Smith, Commented May 10, 2011 at 22:00
Bah! Is there any way to get these results then? I just found it was strange that I could run exactly that in SQL Server 2008 as my view and it ran fine but it would not let me save. — Sam F
– Sam F, Commented May 10, 2011 at 22:02
The View designer in SSMS is just the standard graphical query designer so you can type in all kinds of arbitrary SQL and execute it but it doesn't mean its valid for a view! — Martin Smith
– Martin Smith, Commented May 10, 2011 at 22:06
Oh I never realized that haha I thought it was actually a sql view debugger. — Sam F
– Sam F, Commented May 10, 2011 at 22:09
It wasn't necessarily correct but I was planning on checking it, thanks to Martin also that was a big help :) — Sam F
– Sam F, Commented May 10, 2011 at 22:34

RThomas · Accepted Answer · 2011-05-10 22:11:10Z

2

View's cannot accept parameters. A table valued function IS the solution. But you have to at least know the table and result set that is going to come out the other end. If your passing the table to be queried as a parameter how do you know the structure of the resulting data set?

edited May 10, 2011 at 22:11

answered May 10, 2011 at 22:03

RThomas

10.9k2 gold badges50 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Sam F Over a year ago

But it is not a parameter, it is the name of the database to use. This from what I can tell needs to be done as an exec(@string) since there is no other way to define the object.

Martin Smith Over a year ago

a UDF wouldn't work. It gives the error "Invalid use of side-effecting or time-dependent operator in 'EXECUTE STRING' within a function. "

Sam F Over a year ago

Yeah just tried it, I wonder if I could create a stored procedure that will recreate the view with the new set of data?

RThomas Over a year ago

Depending on how you're going to use the result set a stored procedure can return results. But it can't be referenced by other views, or subqueries etc, like a table valued function could.

RThomas Over a year ago

Sorry, I'm new to this type of forum. I tried to edit my post after I understood the original question better. I didn't realize the table to be queried was one of the parameters.

|

Oleg Melnikov · Accepted Answer · 2017-12-12 22:36:28Z

You can easily fake an internal variable in your view using CTE. You can test-run it in your version of SQL Server.

CREATE VIEW vwImportant_Users AS
WITH params AS (
    SELECT 
    varType='%Admin%', 
    varMinStatus=1)
SELECT status, name 
    FROM sys.sysusers, params
    WHERE status > varMinStatus OR name LIKE varType

SELECT * FROM vwImportant_Users

yielding output:

status  name
12      dbo
0       db_accessadmin
0       db_securityadmin
0       db_ddladmin

also via JOIN

WITH params AS ( SELECT varType='%Admin%', varMinStatus=1)
SELECT status, name 
    FROM sys.sysusers INNER JOIN params ON 1=1
    WHERE status > varMinStatus OR name LIKE varType

also via CROSS APPLY

WITH params AS ( SELECT varType='%Admin%', varMinStatus=1)
SELECT status, name 
    FROM sys.sysusers CROSS APPLY params
    WHERE status > varMinStatus OR name LIKE varType

Collectives™ on Stack Overflow

Dynamic SQL with variables inside a view (SQL Server)

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related