SQLTeam.com | Weblogs | Forums

I don't understand this Stored Procedure


#1

Hi
Charlotte here again... can you help explain this Stored Procedure.
/there's a table created with all the database names (dbsettings), I need to write a SPquery to select the correct database from this table/
/* as per previous topic, I managed to write the SP to dump the results of a query into a table I first created, however the data comes from several tables & several databases, which I hardcoded*/
/* now I am required to write the SP to point at this table to select the correct database for my tables*/
/* forllowing is sample given to me*./

GO
Delete from dbo.HarvestingAnalysis;
DECLARE @dbname varchar(50)
DECLARE contact_cursor CURSOR FOR
SELECT DBName FROM dbo.DBSettings

OPEN contact_cursor;

FETCH NEXT FROM contact_cursor
INTO @dbname;

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN

-- Concatenate and display the current values in the variables.

EXECUTE ('Insert INTO .dbo.HarvestingAnalysis SELECT * from ' + @dbname + '.dbo.HarvestingAnalysis');
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM contact_cursor
INTO @dbname;
END


#2

I'm not sure I understand but I will give it a try....

Do you have a lot of databases with dbo.HarvestingAnalysis table?

If this is the case, I'm guessing you are inserting data from each dbo.HarvestingAnalysis into .dbo.HarvestingAnalysis

If you want a filter based on database name you should add an IF clause before EXECUTE or WHERE inside the select statement depending on your needs.


#3

That;s what I thought too. What does this code do?


#4

Inserting data from each dbo.HarvestingAnalysis into .dbo.HarvestingAnalysis

Declaring and fetching contact_cursor you get DBNAME (as @dbname) from dbo.DBSettings for every record and then you execute the insert into statement with different FROM clause every time


#5

Column [DBName] in table [DBSettings] contains names of databases to be processed

For each row, i.e. for each Database name, the cursor loop executes:

Insert INTO .dbo.HarvestingAnalysis 
SELECT * 
from THE_DATABASE_NAME.dbo.HarvestingAnalysis

so in effect it collects all HarvestingAnalysis data from all (named) databases.

There are smarter ways to do this - for example the current code deletes all existing rows from the Target table (NOTE: Using TRUNCATE rather than DELETE would be far more efficient), and then inserts all rows from all (named) databases. It will do this every time it is called, despite the fact (presumably) that many/most? of the rows will be the same as previously. If the total number of rows is modest then this probably doesn't matter, but it won't scale well. As the size, and number, of the Source databases grows it will take longer; it will also generate increasingly large transactions (which will put strain on diskspace, both for the transaction and for any transaction log backups) and it will probably block access to the Source database for the duration of the Insert - as the Size increases so will the length of time of the blocking)

A smarter way would be to "UpSert" the Target table - only inserting new rows, updating any rows that have changed, and deleting any rows no longer present in the Source table.

I would have also expected the code to store an additional column, in the Target table, containing the name of the Source database that the data had come from. As that is not present I expect that reporting on the Target table is restricted to aggregating the data, but for debugging, should that become necessary, it would without doubt be helpful to know where any Goofy row's data had come from :smile:

I would also want to have a test to see if there is any database NOT in the list which DOES contain a [HarvestingAnalysis] table, and provide a warning message indicating that that database might need adding to the [DBSettings] table - otherwise, when another database is added, its an acident-waiting-to-happen


#6

I would add a column to the target table with the name/ID of the Source database, so that a conditional query could be made (without having to re-populate the Target table)

My guess is that the Query software has been written to work on the [HarvestingAnalysis] table, regardless of which database, so adding such a column would probably break the existing code ... if so it is "too cheap & cheerful" for my liking :frowning:


#7

Wow! awesome explanation. Thanks!

My task is to point my query to this table with all the databases listed, for the purposes of selecting the right database for the table. Is this possible...?


#8

Assuming I have understood your requirement correctly?

I would do it somewhat differently, so in case helpful here's what I would do:

Add a new column to HarvestingAnalysis called [DBName] - make it the same type / length as [DBName] in dbo.DBSettings.

It is important that you add it as the FIRST column in that table (i.e. it will be followed by all the columns common to the [HarvestingAnalysis] tables in all the databases)

TRUNCATE TABLE dbo.HarvestingAnalysis;
--
DECLARE	@dbname		varchar(50) = '',
	@intRowCount	int
--
SELECT	@intRowCount = 1	-- Force first iteration
--
WHILE @intRowCount >= 1
BEGIN
	-- Select next Database to process
	SELECT	TOP 1 @dbname = DBName
	FROM	dbo.DBSettings
	WHERE	DBName > @dbname
	ORDER BY DBName
	SELECT	@intRowCount = @@ROWCOUNT	-- Number of matched rows (1=Found one, 0=No more to process)
--
	IF @intRowCount <> 0
	BEGIN
		PRINT 'Processing: ' + @dbname
		EXECUTE ('Insert INTO dbo.HarvestingAnalysis SELECT ''' 
			+ @dbname + ''', * FROM ' 
			+ @dbname + '.dbo.HarvestingAnalysis');
	END
END

make sure you run this in the correct database, otherwise it will delete any live data in HarvestingAnalysis!!

Take a backup first !!


#9

Hi Kristen,
I still don't understand how to write this query. My SQL Query looks like this.

SELECT

[BSPMS_TR].[General].Estate.EstateName,

[BSPMS_TR].[Checkroll].DailyAttendance.DailyReceiptionID,

[BSPMS_TR].[Checkroll].DailyAttendance.ActiveMonthYearID,

[BSPMS_TR].[Checkroll].DailyAttendance.RDate,

[BSPMS_TR].[Checkroll].DailyAttendance.DailyTeamActivityID,

[BSPMS_TR].[Checkroll].DailyAttendance.TotalOT,

[BSPMS_TR].[Checkroll].CREmployee.EmpCode,

[BSPMS_TR].[Checkroll].CREmployee.EmpName,

[BSPMS_TR].[Checkroll].AttendanceSetup.AttendType

FROM [BSPMS_TR].[Checkroll].DailyAttendance

INNER JOIN

[BSPMS_TR].[Checkroll].CREmployee ON [BSPMS_TR].[Checkroll].DailyAttendance.EmpID
= [BSPMS_TR].[Checkroll].CREmployee.EmpID INNER JOIN

[BSPMS_TR].[Checkroll].AttendanceSetup ON [BSPMS_TR].[Checkroll].DailyAttendance.AttendanceSetupID
= [BSPMS_TR].[Checkroll].AttendanceSetup.AttendanceSetupID

INNER JOIN

[BSPMS_TR].[General].Estate ON [BSPMS_TR].[Checkroll].DailyAttendance.EstateID
= [BSPMS_TR].[General].Estate.EstateID AND [BSPMS_TR].[Checkroll].CREmployee.EstateID
= [BSPMS_TR].[General].Estate.EstateID AND

[BSPMS_TR].[Checkroll].AttendanceSetup.EstateID = [BSPMS_TR].[General].Estate.EstateID

UNION ALL

SELECT

[BSPMS_BRF].[General].Estate.EstateName,

[BSPMS_BRF].[Checkroll].DailyAttendance.DailyReceiptionID,

[BSPMS_BRF].[Checkroll].DailyAttendance.ActiveMonthYearID,

[BSPMS_BRF].[Checkroll].DailyAttendance.RDate,

[BSPMS_BRF].[Checkroll].DailyAttendance.DailyTeamActivityID,

[BSPMS_BRF].[Checkroll].DailyAttendance.TotalOT,

[BSPMS_BRF].[Checkroll].CREmployee.EmpCode,

[BSPMS_BRF].[Checkroll].CREmployee.EmpName,

[BSPMS_BRF].[Checkroll].AttendanceSetup.AttendType

FROM [BSPMS_BRF].[Checkroll].DailyAttendance

INNER JOIN

[BSPMS_BRF].[Checkroll].CREmployee ON [BSPMS_BRF].[Checkroll].DailyAttendance.EmpID
= [BSPMS_BRF].[Checkroll].CREmployee.EmpID INNER JOIN

[BSPMS_BRF].[Checkroll].AttendanceSetup ON [BSPMS_BRF].[Checkroll].DailyAttendance.AttendanceSetupID
= [BSPMS_BRF].[Checkroll].AttendanceSetup.AttendanceSetupID

INNER JOIN

[BSPMS_BRF].[General].Estate ON [BSPMS_BRF].[Checkroll].DailyAttendance.EstateID
= [BSPMS_BRF].[General].Estate.EstateID AND [BSPMS_BRF].[Checkroll].CREmployee.EstateID
= [BSPMS_BRF].[General].Estate.EstateID AND

[BSPMS_BRF].[Checkroll].AttendanceSetup.EstateID = [BSPMS_BRF].[General].Estate.EstateID

UNION ALL

SELECT

[BSPMS_HO].[General].Estate.EstateName,

[BSPMS_HO].[Checkroll].DailyAttendance.DailyReceiptionID,

[BSPMS_HO].[Checkroll].DailyAttendance.ActiveMonthYearID,

[BSPMS_HO].[Checkroll].DailyAttendance.RDate,

[BSPMS_HO].[Checkroll].DailyAttendance.DailyTeamActivityID,

[BSPMS_HO].[Checkroll].DailyAttendance.TotalOT,

[BSPMS_HO].[Checkroll].CREmployee.EmpCode,

[BSPMS_HO].[Checkroll].CREmployee.EmpName,

[BSPMS_HO].[Checkroll].AttendanceSetup.AttendType

FROM [BSPMS_HO].[Checkroll].DailyAttendance

INNER JOIN

[BSPMS_HO].[Checkroll].CREmployee ON [BSPMS_HO].[Checkroll].DailyAttendance.EmpID
= [BSPMS_HO].[Checkroll].CREmployee.EmpID INNER JOIN

[BSPMS_HO].[Checkroll].AttendanceSetup ON
[BSPMS_HO].[Checkroll].DailyAttendance.AttendanceSetupID =
[BSPMS_HO].[Checkroll].AttendanceSetup.AttendanceSetupID

INNER JOIN

[BSPMS_HO].[General].Estate ON
[BSPMS_HO].[Checkroll].DailyAttendance.EstateID =
[BSPMS_HO].[General].Estate.EstateID AND
[BSPMS_HO].[Checkroll].CREmployee.EstateID =
[BSPMS_HO].[General].Estate.EstateID AND

[BSPMS_HO].[Checkroll].AttendanceSetup.EstateID =
[BSPMS_HO].[General].Estate.EstateID

UNION ALL

SELECT

[BSPMS_BRF].[General].Estate.EstateName,

[BSPMS_BRF].[Checkroll].DailyAttendance.DailyReceiptionID,

[BSPMS_BRF].[Checkroll].DailyAttendance.ActiveMonthYearID,

[BSPMS_BRF].[Checkroll].DailyAttendance.RDate,

[BSPMS_BRF].[Checkroll].DailyAttendance.DailyTeamActivityID,

[BSPMS_BRF].[Checkroll].DailyAttendance.TotalOT,

[BSPMS_BRF].[Checkroll].CREmployee.EmpCode,

[BSPMS_BRF].[Checkroll].CREmployee.EmpName,

[BSPMS_BRF].[Checkroll].AttendanceSetup.AttendType

FROM [BSPMS_BRF].[Checkroll].DailyAttendance

INNER JOIN

[BSPMS_BRF].[Checkroll].CREmployee ON
[BSPMS_BRF].[Checkroll].DailyAttendance.EmpID =
[BSPMS_BRF].[Checkroll].CREmployee.EmpID INNER JOIN

[BSPMS_BRF].[Checkroll].AttendanceSetup ON
[BSPMS_BRF].[Checkroll].DailyAttendance.AttendanceSetupID =
[BSPMS_BRF].[Checkroll].AttendanceSetup.AttendanceSetupID

INNER JOIN

[BSPMS_BRF].[General].Estate ON
[BSPMS_BRF].[Checkroll].DailyAttendance.EstateID = [BSPMS_BRF].[General].Estate.EstateID
AND [BSPMS_BRF].[Checkroll].CREmployee.EstateID =
[BSPMS_BRF].[General].Estate.EstateID AND

[BSPMS_BRF].[Checkroll].AttendanceSetup.EstateID =
[BSPMS_BRF].[General].Estate.EstateID

UNION ALL

SELECT

[BSPMS_POM].[General].Estate.EstateName,

[BSPMS_POM].[Checkroll].DailyAttendance.DailyReceiptionID,

[BSPMS_POM].[Checkroll].DailyAttendance.ActiveMonthYearID,

[BSPMS_POM].[Checkroll].DailyAttendance.RDate,

[BSPMS_POM].[Checkroll].DailyAttendance.DailyTeamActivityID,

[BSPMS_POM].[Checkroll].DailyAttendance.TotalOT,

[BSPMS_POM].[Checkroll].CREmployee.EmpCode,

[BSPMS_POM].[Checkroll].CREmployee.EmpName,

[BSPMS_POM].[Checkroll].AttendanceSetup.AttendType

FROM [BSPMS_POM].[Checkroll].DailyAttendance

INNER JOIN

[BSPMS_POM].[Checkroll].CREmployee ON [BSPMS_POM].[Checkroll].DailyAttendance.EmpID
= [BSPMS_POM].[Checkroll].CREmployee.EmpID INNER JOIN

[BSPMS_POM].[Checkroll].AttendanceSetup ON [BSPMS_POM].[Checkroll].DailyAttendance.AttendanceSetupID
= [BSPMS_POM].[Checkroll].AttendanceSetup.AttendanceSetupID

INNER JOIN

[BSPMS_POM].[General].Estate ON [BSPMS_POM].[Checkroll].DailyAttendance.EstateID
= [BSPMS_POM].[General].Estate.EstateID AND [BSPMS_POM].[Checkroll].CREmployee.EstateID
= [BSPMS_POM].[General].Estate.EstateID AND

[BSPMS_POM].[Checkroll].AttendanceSetup.EstateID = [BSPMS_POM].[General].Estate.EstateID

I need to write a procedure that selects the correct database from a database table instead of hardcoding, so if we add a new database it will automatically select it, without having to re-write the code.

I AM SO SORRY.... i know it's so cumbersome having to help me out like this.


#10

I thought you were selecting from the HavestingAnalysis table ...

This code will substitute the DBName in place of your hardcoded database names.

DECLARE @strSQL nvarchar(MAX) = ''
--
SELECT	@strSQL = @strSQL
	+ CASE WHEN RowNumber = 1 THEN '' ELSE ' UNION ALL ' END
	+ 'SELECT
' + Q_DBName + '.[General].Estate.EstateName,
' + Q_DBName + '.[Checkroll].DailyAttendance.DailyReceiptionID,
' + Q_DBName + '.[Checkroll].DailyAttendance.ActiveMonthYearID,
' + Q_DBName + '.[Checkroll].DailyAttendance.RDate,
' + Q_DBName + '.[Checkroll].DailyAttendance.DailyTeamActivityID,
' + Q_DBName + '.[Checkroll].DailyAttendance.TotalOT,
' + Q_DBName + '.[Checkroll].CREmployee.EmpCode,
' + Q_DBName + '.[Checkroll].CREmployee.EmpName,
' + Q_DBName + '.[Checkroll].AttendanceSetup.AttendType
FROM ' + Q_DBName + '.[Checkroll].DailyAttendance
INNER JOIN
' + Q_DBName + '.[Checkroll].CREmployee ON ' + Q_DBName + '.[Checkroll].DailyAttendance.EmpID
= ' + Q_DBName + '.[Checkroll].CREmployee.EmpID INNER JOIN
' + Q_DBName + '.[Checkroll].AttendanceSetup ON ' + Q_DBName + '.[Checkroll].DailyAttendance.AttendanceSetupID
= ' + Q_DBName + '.[Checkroll].AttendanceSetup.AttendanceSetupID
INNER JOIN
' + Q_DBName + '.[General].Estate ON ' + Q_DBName + '.[Checkroll].DailyAttendance.EstateID
= ' + Q_DBName + '.[General].Estate.EstateID AND ' + Q_DBName + '.[Checkroll].CREmployee.EstateID
= ' + Q_DBName + '.[General].Estate.EstateID AND
' + Q_DBName + '.[Checkroll].AttendanceSetup.EstateID = ' + Q_DBName + '.[General].Estate.EstateID
'
FROM
(
	SELECT	ROW_NUMBER() OVER (ORDER BY DBName) AS [RowNumber],
		QuoteName(DBName) AS [Q_DBName]
	FROM	dbo.DBSettings
) AS T
ORDER BY RowNumber
--
SELECT	[@strSQL] = @strSQL

Check that the output is correct and if so you can change the last line to:

EXEC (@strSQL)

to execute it dynamically.

But ... why do you want to do this? This is just going to select every row from that query in every relevant database. What use is that? (without a WHERE clause or some aggregate functions etc.)

Personally I would generate a VIEW for this, and then query the view. If a new database is added then re-create the VIEW to include that database (you can use the code above to mechanically generate the code for the VIEW each time, so you don't have to do it manually - you could even put it in a TRIGGER on the DBSettings table so that whenever that was changed the VIEW was recreated


#11

Note that this does NOT have the name of the source database in the selected column list. Personally I think that is important otherwise it becomes impossible to debug where a specific row of data originated from.