Dynamic View

Question

Post reply

Dynamic View

Mike Levan

SSChampion

Points: 14201
More actions
December 20, 2007 at 8:16 am

#62994

ALTER Procedure lookupemp
@dbname varchar(30),@UserName varchar(10)
as
begin
set nocount on
declare @SQLCmd nvarchar(4000)
set @SQLCmd = 'use ' + @DBName + ';
select jobid,empname from tblemp where user=@username
'
exec sp_executesql @SQLCmd,N'@UserName varchar(4)',@UserName
end
Like the Proc above can do the same thing for a view, I understand we cant pass paramters to view but how can i achieve a dynamic view so that by passing db name as paramter i can access view from any db.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736568 More actions · Answer 1

You cannot make a dynamic view.

You can make the same view in every db and then call it with the 3 part naming (db.owner.view).

So I can create MyView in every database and just use my database name as the parameter.

select * from Northwind.dbo.MyView

select * from Pubs.dbo.MyView

select * from AdventureWorks.dbo.MyView

Mike Levan SSChampion Points: 14201 More actions · Answer 2

Can I make a view as table valued function so that I can run the view in any db by passing the database name as parameter. something like this....

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION Employee

(

@dbname varchar(30)

)

RETURNS TABLE

AS

RETURN

declare @SQLCmd nvarchar(4000)

set @SQLCmd = 'use ' + @DBName + ';

select empno,name,jobid,jobname from tblemp

where satus=''A''

'

EXEC(SQLCmd)

GO

I am sure there shud be some syntax error, please shed some light on making a view dynamic by hook or crook.

thanks

Sergiy SSC Guru Points: 110208 More actions · Answer 3

Dynamic SQL is not allowed inside of functions.

_____________
Code for TallyGenerator

Mike Levan SSChampion Points: 14201 More actions · Answer 4

I am sure table valued function will allow parameters but why cant we pass dbname as parameter and execute it from any where in same server

Mike Levan SSChampion Points: 14201 More actions · Answer 5

so there is no way to run a view from any database?

is it possible by using master db?

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 6

Yes, it is possible... read the following article... because these are a bit "tricky", make sure that you read and understand the entire article before you try anything like that...

http://www.sqlservercentral.com/articles/Administering/utilityprocedures/2272/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)