August 13, 2010 at 9:48 am
We are trying to tie back our list of 'end of month' DB's
So the end user will be able to select teh correct Db from a drop down.
Here's the query:
declare @databasename nvarchar(100)
set @databasename= (SELECT name FROM [vslpbbidb03].master.dbo.sysdatabases where name like'msph%')
SELECT DISTINCT
DH.EFFECTIVE_DT,
DH.ACCT_NO,
DH.ACCT_TYPE,
AGB.BRANCH_NO,
AGB.SHORT_NAME,
ADC.CLASS_CODE,
ADC.DESCRIPTION,
DA.TITLE_1,
DH.TRAN_CODE,
DH.AMT,
DD.LAST_MO_END_BAL
FROM [dbserver].'+@databasename+'.dbo.DP_HISTORY DH
INNER JOIN [dbserver].'+@databasename+'.dbo.DP_DISPLAY DD
ON DH.ACCT_NO=DD.ACCT_NO
INNER JOIN [dbserver].'+@databasename+'.dbo.DP_ACCT DA
ON DA.ACCT_NO=DH.ACCT_NO
INNER JOIN [dbserver].'+@databasename+'.dbo.AD_DP_CLS ADC
ON DD.CLASS_CODE=ADC.CLASS_CODE
INNER JOIN [dbserver].'+@databasename+'.dbo.AD_GB_BRANCH AGB
ON AGB.BRANCH_NO=DA.BRANCH_NO
WHERE
DH.TRAN_CODE='168'
AND
DD.CLASS_CODE='736'
AND
DH.EFFECTIVE_DT = '7/31/2010'
ORDER BY DH.AMT ASC
August 13, 2010 at 10:02 am
There's a couple of things wrong with this.
First is when setting the database name variable, it will error out if more than one is found.
Second, you will need to use dynamic SQL to create the statement to execute. This has it's own issues not the least of which is SQL Injection.
Lastly, why? You could setup a list of 'ArchiveRanges' that the user can select from (they shouldn't know or care about what database they are hitting). They could pass this ArchiveRange to a stored procedure where you can control the sql. You could still use dynamic sql if you want, but the risk of SQL Injection is alot lower.
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply