using two database and 4 table from same data base

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 2000 Forums

Transact-SQL (2000)

using two database and 4 table from same data base

Author

Topic

divyaaga
Starting Member

1 Post

Posted - 2009-01-27 : 01:42:52

Hi ,
I.m uploading 4 reports on the bases of 4 parameters.
1.date
2.report type: A,T,M,C
3.Camp:1,2,3,4
4.EIN

CREATE PROC [dbo].[ABS]
@REPORTTYPE NVARCHAR(1),
@ABS BIGINT
AS
SET NOCOUNT ON
IF @REPORTTYPE='A'
BEGIN
SELECT
ABS
from Database1.dbo.table1
where ABS=@ABS

END
ELSE IF @REPORTTYPE='T' --will return multiple values
BEGIN
SELECT
ABS
from Database1.dbo.table1
where ABS=@ABS

END
ELSE IF @REPORTTYPE='M'--will return multiple values
BEGIN
SELECT
ABS
from Database1.dbo.table1
where ABS=@ABS

END
ELSE IF @REPORTTYPE='C'--will return multiple values
BEGIN
SELECT
ABS
from Database1.dbo.table1
where ABS=@ABS
END
-------------------------
Then i'm calling according to ABS this stored procedure to another one.
CREATE PROC [dbo].[Main]
@ABS BIGINT,
@Fromdate DATETIME,
@Todate DATETIME,
@CAMP BIGINT,
@REPORTTYPE Char(1)
AS
SET NOCOUNT ON
BEGIN
select
'UserName'=(Select A.FirstName+' '+A.LastName from Database1.dbo.table1),
SUM(CASE WHEN TYPE='T' THEN [ABC] ELSE 0 END) 'Tanu',

'T1'=(select Sum(B.Address) from Database2.dbo.table1),

'T2'=(select Sum(C.Address) from Database2.dbo.table2),

'T3'=(select Sum(D.Address) from Database2.dbo.table3),

'T4'=(select Sum(E.Address) from Database2.dbo.table4),

FROM Database2.dbo.table5
LEFT JOIN database1.dbo.table1 AS A ON Database2.dbo.table5.ABS=A.ABS
LEFT JOIN database2.dbo.table1 AS B ON Database2.dbo.table5.ABS=B.ABS
LEFT JOIN database2.dbo.table2 AS C ON Database2.dbo.table5.ABS=C.ABS
LEFT JOIN database2.dbo.table3 AS D ON Database2.dbo.table5.ABS=D.ABS
LEFT JOIN database2.dbo.table4 AS E ON Database2.dbo.table5.ABS=E.ABS
where Database2.dbo.table5.ABS IN(Database2.dbo.ABS(@REPORTTYPE,@ABS))
AND Dated Between @Fromdate and @Todate
AND Camp=@CAMP
group by ABS

-------------------------------------------------

Please correct me . I'm not getting the data.

heavymind
Posting Yak Master

115 Posts

Posted - 2009-01-27 : 08:19:20

Only glanced the first part

IF @REPORTTYPE='A'
BEGIN
SELECT
ABS
from Database1.dbo.table1
where ABS=@ABS

END
ELSE IF @REPORTTYPE='T' --will return multiple values
BEGIN
SELECT
ABS
from Database1.dbo.table1
where ABS=@ABS

END
ELSE IF @REPORTTYPE='M'--will return multiple values
BEGIN
SELECT
ABS
from Database1.dbo.table1
where ABS=@ABS

END
ELSE IF @REPORTTYPE='C'--will return multiple values
BEGIN
SELECT
ABS
from Database1.dbo.table1
where ABS=@ABS
END

which is a bit redundant - all the ifs do the same....

Thanks, Vadym
MCITP DBA 2005/2008
Chief DBA at http://www.db-staff.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources