CODEWITHSUNDEEP
sqlsql-servert-sqldynamicview
Ricibald
Ricibald

Reputation: 9949

T-SQL: Dynamically change working view

I have a stored procedure that works with a view Customer_A or a view Customer_B according to a parameter. The parameter changes the view but not the underlying query.

I don't want to write two different stored procedure to just change the working view.

Is it possible to have something like a local synonym to the view to just change the pointed view?

Thanks

Upvotes: 0

Views: 146

Answers (4)

t-clausen.dk
t-clausen.dk

Reputation: 44356

create view Customer_A as select 1 a
GO

create view Customer_B as select 2 a
GO

create procedure p_test
(
@view int
) with recompile
as
if @view = 1 
 select * from Customer_A
else if @view = 2
 select * from Customer_B

GO

exec p_test 1
exec p_test 2

Upvotes: 0

juergen d
juergen d

Reputation: 204924

You could use dynamic SQL for that

declare @tablename
select @tablename = 'customerA'
exec('select * from ' + @tablename)

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239824

One way you could do it is to add a new view:

CREATE VIEW Customers
AS
   SELECT *, --TODO, Name columns
          'A' as CustomerName
   FROM Customer_A
   UNION ALL
   SELECT *, --Ditto
          'B'
   FROM Customer_B

And now your stored procedure can query it and select the appropriate data based on the CustomerName column. Of course, you only need this new view because you've made the mistake of embedding actual data that you want to query over (such as the customers name) inside of metadata instead (a view's name).

(It may also be that the views Customer_A and Customer_B should be implemented atop a single generic view that already contains the union of all customer data, and my view above is just recreating that underlying query)

Upvotes: 1

Giannis Paraskevopoulos
Giannis Paraskevopoulos

Reputation: 18431

You may use sp_executesql and QuoteName. QuoteName will put brackets around the name you pass and sp_executesql will execute a dynamically created SQL.

CREATE PROCEDURE RunView
    @ViewName VARCHAR(MAX)
AS
BEGIN
    DECLARE @SQL AS NVARCHAR(MAX)

    SET @SQL = 
    N'SELECT * FROM ' + QuoteName(@ViewName) 

    EXECUTE sp_executesql @SQL
END

Upvotes: 1

Related Questions