|
Subject:
|
Varible DB Name
|
|
Posted By:
|
Mdingler
|
Post Date:
|
12/6/2006 2:56:38 PM
|
We save our DBs at month end and have that DB available with the name "Service_YYYYMMDD". I need to join from the active DB to a previous DB.
FROM Service_20050401.dbo.Company C2,Service_20050908.dbo.Company C, Service_20050908.dbo.Loan L
INNER JOIN Service_20050908.dbo.Status S ON L.LoanID=S.LoanID
INNER JOIN Service_20050401.dbo.Loan L2 ON L.LoanID=L2.LoanID
INNER JOIN Service_20050401.dbo.Status S2 ON L.LoanID=S2.LoanID
Is there a way to program the DB name as a parameter that can be pulled into the view/procedure so that I don't have to physical change the view each time before executing? I used to program in SAS and it is possible in SAS to do this.
|
|
Reply By:
|
ghemant
|
Reply Date:
|
12/7/2006 12:21:23 AM
|
refer sommarskog's dynamic Sql for more.
With Regards
Hemantgiri S. Goswami
------------------------
"Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" -Hemantgiri S. Goswami
|
|
Reply By:
|
emcarandang
|
Reply Date:
|
12/7/2006 9:19:20 PM
|
MDingler,
My friend, it is possible to use dbo.***** for inParameter/s. Since i am into UDF and not much into stored procedure, this is how i do it with UDF.
===============================================================
Please execute this with caution this is dangerous. just get the idea and NEVER EXECUTE THIS!!!!!!!!!!!!!!!!!!!!!
===============================================================
--The following is the CREATE FUNCTION script for udf_Func_UndocSystemUDFText
--It's commented out to prevent accidental creation.
CREATE FUNCTION dbo.udf_Func_UndocSystemUDFText
(
@FunctionName sysname --name of the function
)
RETURNS @Script TABLE --text of the function
([text] varchar(4000)) --a line of text
--No SCHEMABINDING due to the use of system tables
--Returns the text of an undocumented system user-defined function.
--This function can only be used in the master database, where the text
--of the undocumented UDFs is stored.
AS
BEGIN
DECLARE @objectID int
SELECT @objectID = id
FROM sysobjects
WHERE (type=N'FN' or type=N'IF or type = N'TR')
AND name = @FunctionName
INSERT INTO @Script
SELECT [text]
FROM syscomments
WHERE id = @objectID
RETURN
END
===============================================================
In your case use @SP_Name instead of @FunctionName... Again, use this as reference and do it with caution!!!!!!!!!!!!!
#9562;Ä{ôƒ#9573;#9500;
|
|
Reply By:
|
emcarandang
|
Reply Date:
|
12/7/2006 9:21:24 PM
|
MDingler,
Eratum:
It should be @DBName and not....@FunctionName
.....
.....
" RETURN
END
===============================================================
In your case use @SP_Name instead of @FunctionName... Again, use this as reference and do it with caution!!!!!!!!!!!!!"
#9562;Ä{ôƒ#9573;#9500;
|
