How to obtain one result set - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to obtain one result set

Author

Topic

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-08-19 : 00:09:53

I have 1 to X databases that are all the same structure and on the same server instance. I need all my stored procedures for a reporting services report to loop through all the databases for reporting. I have tried:

ALTER PROCEDURE dbo.TestmultipleDB

@DBName char(50)

AS

BEGIN
/* SET NOCOUNT ON */
Select @DBName = Name
From sys.databases
WHERE Name = 'VC' OR Name Like 'VCArchive%'

While @DBName IS NOT NULL
Begin
Select * From @DBName.dbo.UserDetails --Put what ever sql code here
End

End

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-19 : 00:13:14

just use sp_MSforeachdb undocumented stored procedure.something like

Exec sp_Msforeachdb 'select * from ?.dbo.UserDetails'

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-08-19 : 00:31:36

Sorry I don't understand. I need to only loop through some databases and how is ? replaced with the actual database? I basically have all my code for reports that work against a single DB and now I need a simple way of putting a wrapper around it so it can run against a selection of db.

I found this and it sounds like what I need to do http://weblogs.sqlteam.com/tarad/. I tried to modify to:

ALTER PROCEDURE dbo.TestmultipleDB

@DBName sysname,
@rc int

AS
Begin

/* SET NOCOUNT ON */
Select Name
Into #db
From sys.databases
WHERE Name = 'VC' OR Name Like 'VCArchive%'
SELECT @rc = 1, @dbName = MIN(name)
FROM #db

WHILE @rc <> 0
BEGIN
Select * From #db.dbo.UserDetails -- Do what ever

SELECT TOP 1 @dbName = name
FROM #db
WHERE name > @dbName
ORDER BY name

SET @rc = @@ROWCOUNT
END

DROP TABLE #db

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-08-19 : 00:57:43

Tried the suggestion above however this does seem to work, running without the where includes databases that do not match my structure so I get errors and the following fails:
Exec sp_Msforeachdb 'select * from ?.dbo.UserDetails Where Name = 'VC' OR Name Like 'VCArchive%''

I also need to have one result set for the report I guess.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-19 : 01:02:32

but you told all dbs have similar structure. if not, you need the condition check

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-08-19 : 01:06:32

Sorry visakh16 you are correct. I was only considering my databases and I forgot about the other default and reporting DBs. So how do I add the condition as my where did not work. Also, is process going to be a problem for my reports in reporting services? I am not sure how it sees the result sets?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-19 : 01:34:20

just use if exists() to check if table exists and then use the query

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-08-19 : 02:42:26

Two problem here:
1. I have found this to work reasonably well however I am getting the following error.
2. I need this code to store the results into one result set as reporting services only sees the first result set. How do I use a temp table for this or can I use something like a Union All?

Messages:
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '.'.

(1 row(s) affected)
Results:
Running [dbo].[TestMultipleDB_3].

Incorrect syntax near '.'.
Incorrect syntax near '.'.
UserID FirstName MiddleName LastName Initials LoginName LoginPassword IsDeleted ProfileXMLFile
----------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2106 Not Archive Not Archive Not Archive Not Archive Not Archive Not Archive 0 <BINARY>
2108 Steve James Harlington SJH SJH SJH 0 <BINARY>
No rows affected.
(2 row(s) returned)
UserID FirstName MiddleName LastName Initials LoginName LoginPassword IsDeleted ProfileXMLFile
----------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2733 Ray <NULL> Shi <NULL> rshi ruishi 0 <BINARY>
No rows affected.
(1 row(s) returned)
UserID FirstName MiddleName LastName Initials LoginName LoginPassword IsDeleted ProfileXMLFile
----------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2733 Ray <NULL> Shi <NULL> rshi ruishi 0 <BINARY>
No rows affected.
(1 row(s) returned)
@RETURN_VALUE = 0
Finished running [dbo].[TestMultipleDB_3].

USE [VC]
GO
/****** Object: StoredProcedure [dbo].[TestMultipleDB] Script Date: 08/19/2008 16:09:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[TestMultipleDB]

AS
BEGIN

DECLARE @SQL NVARCHAR(4000)
SET NOCOUNT ON

SET @SQL = '
IF''?'' = (''VC'') OR ''?'' LIKE (''VCA%'')
BEGIN
Select * From ?.dbo.UserDetails
END'

EXEC sp_MSForEachDb @sql

SET NOCOUNT OFF

END

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-19 : 03:09:03

declare @sql varchar(max)
select @sql=''
select @sql=@sql+ 'SELECT * FROM '+name+'.dbo.UserDetails ' from sys.databases where name='VC' or name like 'VCA%'
exec(@sql)

Madhivanan

Failing to plan is Planning to fail

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-08-19 : 04:35:36

Thanks for your reply madhivanan however I am having difficulty seeing how to put your solution into a more complex problem. I had kept the sql very simple for the example and may have shot myself in the foot by doing so. I am looking for a way of putting a wrapper around my existing code (many store procedures) to report against multiple databases. Your solution may work however I am having difficulty seeing how to declare the variables in scope etc. Is it just a matter of putting any code sql inside the ' blah blah blah ' and replacing anywhere that references a table with '+name+'.dbo.whatever? I should also indicate this code returns one result set for each db where I need all results in one result set for reporting services. Here is an example of where I need to replace:

'SELECT * FROM '+name+'.dbo.UserDetails '

with:

SELECT SUM(TotalSeconds) / 86400 AS Days, SUM(TotalSeconds) % 86400 / 3600 AS Hours, SUM(TotalSeconds) % 3600 / 60 AS Minutes, SUM(TotalSeconds) % 60 AS Seconds
FROM (SELECT EventID, DATEDIFF(second, StartTime, EndTime) AS TotalSeconds
FROM (SELECT EventDetails.EventID,
CASE WHEN EventDetails.EventStartTime < StartDateRange THEN StartDateRange ELSE EventDetails.EventStartTime END AS StartTime,
CASE WHEN EventDetails.EventEndTime > EndDateRange THEN EndDateRange ELSE EventDetails.EventEndTime END AS EndTime
FROM (SELECT StartDateRange, EndDateRange, DateRangeSelection
FROM (SELECT CAST(@DateRange AS varchar(30)) AS DateRangeSelection,
CASE
WHEN @DateRange = 'Current Hour' THEN DATEADD(HOUR, 0, DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0))
WHEN @DateRange = 'Last Hour' THEN DATEADD(HOUR, - 1, DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0))
WHEN @DateRange = 'Last 12 Hours' THEN DATEADD(HOUR, - 12, DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0))
WHEN @DateRange = 'Last 24 Hours' THEN DATEADD(HOUR, - 24, DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0))
WHEN @DateRange = 'Today' THEN DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
WHEN @DateRange = 'Yesterday' THEN DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), - 1)
WHEN @DateRange = 'Last Weekend' THEN DATEADD(WEEK, - 1, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 5))
WHEN @DateRange = 'Current Week' THEN DATEADD(WEEK, 0, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0))
WHEN @DateRange = 'Last 7 days' THEN DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), - 7)
WHEN @DateRange = 'Last 5 Week Days' THEN DATEADD(WEEK, - 1, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0))
WHEN @DateRange = 'Last Week' THEN DATEADD(WEEK, - 1, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0))
WHEN @DateRange = 'Last 2 Weeks' THEN DATEADD(WEEK, - 2, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0))
WHEN @DateRange = 'Current Month' THEN DATEADD(MONTH, 0, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
WHEN @DateRange = 'Last 30 days' THEN DATEADD(DAY, - 30, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))
WHEN @DateRange = 'Last 90 days' THEN DATEADD(DAY, - 90, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))
WHEN @DateRange = 'Last Month' THEN DATEADD(MONTH, - 1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
WHEN @DateRange = 'Last 3 Months' THEN DATEADD(MONTH, - 3, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
WHEN @DateRange = 'Current Quarter' THEN DATEADD(QUARTER, 0, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()), 0))
WHEN @DateRange = 'Last Quarter' THEN DATEADD(QUARTER, - 1, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()), 0))
WHEN @DateRange = 'Current Year' THEN DATEADD(YEAR, 0, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0))
WHEN @DateRange = 'Last Year' THEN DATEADD(YEAR, - 1, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0))
END AS StartDateRange,
CASE
WHEN @DateRange = 'Current Hour' THEN GETDATE()
WHEN @DateRange = 'Last Hour' THEN DATEADD(HOUR, 0, DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0))
WHEN @DateRange = 'Last 12 Hours' THEN DATEADD(HOUR, 0, DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0))
WHEN @DateRange = 'Last 24 Hours' THEN DATEADD(HOUR, 0, DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0))
WHEN @DateRange = 'Today' THEN GETDATE()
WHEN @DateRange = 'Yesterday' THEN DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
WHEN @DateRange = 'Last Weekend' THEN DATEADD(WEEK, - 1, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 7))
WHEN @DateRange = 'Current Week' THEN GETDATE()
WHEN @DateRange = 'Last 7 days' THEN DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
WHEN @DateRange = 'Last 5 Week Days' THEN DATEADD(WEEK, - 1, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 5))
WHEN @DateRange = 'Last Week' THEN DATEADD(WEEK, 0, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0))
WHEN @DateRange = 'Last 2 Weeks' THEN DATEADD(WEEK, 0, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0))
WHEN @DateRange = 'Current Month' THEN GETDATE()
WHEN @DateRange = 'Last 30 days' THEN DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
WHEN @DateRange = 'Last 90 days' THEN DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
WHEN @DateRange = 'Last Month' THEN DATEADD(MONTH, 0, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
WHEN @DateRange = 'Last 3 Months' THEN DATEADD(MONTH, 0, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
WHEN @DateRange = 'Current Quarter' THEN GETDATE()
WHEN @DateRange = 'Last Quarter' THEN DATEADD(QUARTER, 0, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()), 0))
WHEN @DateRange = 'Current Year' THEN GETDATE()
WHEN @DateRange = 'Last Year' THEN DATEADD(YEAR, 0, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0))
END AS EndDateRange) AS DateRangeSel) AS DateLimit CROSS JOIN
EventDetails INNER JOIN
SiteDetails ON EventDetails.SiteID = SiteDetails.SiteID
WHERE (NOT (EventDetails.EventEndTime < DateLimit.StartDateRange)) AND
(NOT (EventDetails.EventStartTime > DateLimit.EndDateRange)) AND (SiteDetails.SiteName IN (@SiteName)) AND
(EventDetails.EventType IN (@EventType))) AS DateDif) AS TotalSum

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-08-19 : 04:50:04

Here is a simpler problem to work with. I guess if this is solved then my problem above is solved.

USE [VC]
GO
/****** Object: StoredProcedure [dbo].[TestMultipleDB] Script Date: 08/19/2008 18:14:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[TestMultipleDB]

AS

BEGIN

declare @sql varchar(max)
select @sql=''
select @sql=@sql+ 'SELECT * FROM '+name+'.dbo.UserDetails Where FirstName IN (@first) ' from sys.databases where name='VC' or name like 'VCA%'
exec(@sql)

END

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-08-19 : 06:06:31

It indicates the variable @first is not declared. I put @first varchar(30) into all the locations I could think and obtained the same result.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-19 : 06:10:51

quote:
Originally posted by harlingtonthewizard

It indicates the variable @first is not declared. I put @first varchar(30) into all the locations I could think and obtained the same result.

Your code should be

declare @sql varchar(max), @first varchar(30)
SET @first='some value'
select @sql=''
select @sql=@sql+ 'SELECT * FROM '+name+'.dbo.UserDetails Where FirstName IN ('''+@first+''') ' from sys.databases where name='VC' or name like 'VCA%'
exec(@sql)

Madhivanan

Failing to plan is Planning to fail

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-08-19 : 06:39:43

Can I ask what defines the three ''' each side of +@first+. Or how do you determine how many you need?

And what does this do:
select @sql=''

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-19 : 07:08:41

see this to understand more:-
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/02/19/understanding-single-quotes.aspx

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-08-19 : 07:37:28

Thanks visakh16 that info from madhivanan web site clears that up. Now do you have an example of how to get one result set for reporting services. This code returns one result set for each database which is no good because reporting services only sees the first and others are ignored. I guess the info needs to be stored in a temp table or can union all be used?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=109295

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources