Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Adam West
Constraint Violating Yak Guru
261 Posts |
 Posted - 2009-08-04 : 14:23:58
|
| In this View, where I have the database DBB, can this be a parameter?My idea was to have this view in various databases running the same report, and the database would be passed, so that I can have one View for all,otherwise I have to be putting this view into each of the databases.SELECT IV00101.ITMGEDSC, IV00101.ITEMDESC, RM00101.CUSTNAME, RM00101.CPRCSTNM, IV40600.UserCatLongDescr AS UserCatLongDescr_IV40600, CATS.UserCatLongDescr AS UserCatLongDescr_CATS,SOP10200.ITEMNMBR, SOP10200.SOPNUMBE, SOP10200.QUANTITY, SOP10200.OXTNDPRC, SOP10200.SOPTYPE, SOP10100.DOCDATE, 'Current' AS source, MONTH(SOP10100.DOCDATE) AS ReportMonth, YEAR(SOP10100.DOCDATE) AS ReportYearFROM DBB.dbo.SOP10200 AS SOP10200 INNER JOIN DBB.dbo.IV00101 AS IV00101 ON SOP10200.ITEMNMBR= IV00101.ITEMNMBR INNER JOIN DBB.dbo.SOP10100 AS SOP10100 ON SOP10200.SOPTYPE = SOP10100.SOPTYPEAND SOP10200.SOPNUMBE = SOP10100.SOPNUMBE INNER JOIN DBB.dbo.IV40600 AS IV40600 ON IV00101.ITMGEDSC = IV40600.USCATVAL INNER JOIN DBB.dbo.IV40600 AS CATS ON IV00101.USCATVLS_2 = CATS.USCATVAL INNER JOIN DBB.dbo.RM00101 AS RM00101 ON SOP10100.CUSTNMBR = RM00101.CUSTNMBR |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-08-04 : 14:49:33
|
| If you make it a parameter, you'd need to use dynamic SQL which can't be used inside a view. You'd have to use a stored procedure instead, but dynamic SQL will negatively impact performance. Do you need to fully qualify the objects with the database name though? Can't you just use dbo.SOP10200 instead?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
 |
|
|
|
|
|
|
|
Use a database as a Parameter?