RETURN @@ERROR?

If I execute one SP within a parent SP and want to trap and return an error
code from the child SP, can I just use RETURN @.@.ERROR in the child SP rather
than capturing @.@.ERROR in a local variable first? Or does the successful
completion of the RETURN statement set @.@.ERROR back to 0 by the time it gets
back to the parent SP?
The reason I'm asking is that all the examples I've found about error
trapping don't seem to do it this way, and this way seems the most
straightforward. Thanks for any insight.
CREATE PROCEDURE parentSP
DECLARE @.returncode int
EXEC @.returncode = childSP @.value
IF @.returncode <> 0 Do something like rollback transaction
CREATE PROCEDURE childSP
@.value int
INSERT INTO someTable (column) VALUES (@.value)
RETURN @.@.ERRORYour method will work for the simplest procedure but in the following 0
will be returned if the first insert fails:
CREATE PROCEDURE childSP
@.value int
as
INSERT INTO someTable (column) VALUES (@.value)
IF @.@.ERROR <> 0
RETURN @.@.ERROR
INSERT INTO someOtherTable (column) VALUES (@.value)
RETURN @.@.ERROR
go
It would have to be, a minium the following but this introduces
different methods of handling errors and provides multiple exit points
for the procedured:
CREATE PROCEDURE childSP
@.value int
as
declare @.err
INSERT INTO someTable (column) VALUES (@.value)
select @.err = @.@.ERROR
IF @.err <> 0
RETURN @.err
INSERT INTO someOtherTable (column) VALUES (@.value)
RETURN @.@.ERROR
go
Best practices of coding state that each object should only have 1 exit
point so the following is the standard that I use:
CREATE PROCEDURE childSP
@.value int
as
declare @.err int
INSERT INTO someTable (column) VALUES (@.value)
select @.err = @.@.ERROR
IF @.err <> 0
goto ErrH
INSERT INTO someOtherTable (column) VALUES (@.value)
select @.err = @.@.ERROR
IF @.err <> 0
goto ErrH
ErrH:
return @.ErrorSave|||Thanks. Yes, most of my "child" INSERT, UPDATE SPs are single statements and
I didn't have them trap errors or explicitly RETURN codes and just thought
RETURN @.ERROR was the most expedient.
I'm going over my SPs now to make sure I have one exit point instead of
exits all over.
"JeffB" <jeff.bolton@.citigatehudson.com> wrote in message
news:1147291591.811293.125770@.y43g2000cwc.googlegroups.com...
> Your method will work for the simplest procedure but in the following 0
> will be returned if the first insert fails:
> CREATE PROCEDURE childSP
> @.value int
> as
> INSERT INTO someTable (column) VALUES (@.value)
> IF @.@.ERROR <> 0
> RETURN @.@.ERROR
> INSERT INTO someOtherTable (column) VALUES (@.value)
> RETURN @.@.ERROR
> go
> It would have to be, a minium the following but this introduces
> different methods of handling errors and provides multiple exit points
> for the procedured:
> CREATE PROCEDURE childSP
> @.value int
> as
> declare @.err
> INSERT INTO someTable (column) VALUES (@.value)
> select @.err = @.@.ERROR
> IF @.err <> 0
> RETURN @.err
> INSERT INTO someOtherTable (column) VALUES (@.value)
> RETURN @.@.ERROR
> go
> Best practices of coding state that each object should only have 1 exit
> point so the following is the standard that I use:
> CREATE PROCEDURE childSP
> @.value int
> as
> declare @.err int
> INSERT INTO someTable (column) VALUES (@.value)
> select @.err = @.@.ERROR
> IF @.err <> 0
> goto ErrH
> INSERT INTO someOtherTable (column) VALUES (@.value)
> select @.err = @.@.ERROR
> IF @.err <> 0
> goto ErrH
> ErrH:
> return @.ErrorSave
>|||If you don't like GOTO then the following also works:
CREATE PROCEDURE childSP
@.value int
as
declare @.err int
INSERT INTO someTable (column) VALUES (@.value)
select @.err = @.@.ERROR
IF @.err = 0 BEGIN
INSERT INTO someOtherTable (column) VALUES (@.value)
select @.err = @.@.ERROR
END
return @.Err

Retriieve table names

Hi friends,
How to retrieve all the tables in the database that is having primary key. I
also want to retrieve the foreign key tables with their parent table.
Thanks
vanithaoops ..take this link :)
http://www.dandyman.net/SQL/downloads.aspx
Dandy Weyn
[MCSE-MCSA-MCDBA-MCDST-MCT]
http://www.dandyman.net
Check my SQL Server Resource Pages at http://www.dandyman.net/sql
"Vanitha" <Vanitha@.discussions.microsoft.com> wrote in message
news:701F4FBD-7537-44B9-9C8D-600744CB013B@.microsoft.com...
> Hi friends,
> How to retrieve all the tables in the database that is having primary key.
> I
> also want to retrieve the foreign key tables with their parent table.
> Thanks
> vanitha|||Sysobjects contains objects stored in every user database.
You can join with sysconstraints and sysforeignkeyconstraints that are
having relationship with the Sysobjects table on the object_id
On Microsoft Website you can look for the SQL System table help file.
I also created a link for it on my website in the download section.
http://www.dandyman.net/sql/downloads.asp
This file might also be VERY useful for future system table querying
--
Dandy Weyn
[MCSE-MCSA-MCDBA-MCDST-MCT]
http://www.dandyman.net
Check my SQL Server Resource Pages at http://www.dandyman.net/sql
"Vanitha" <Vanitha@.discussions.microsoft.com> wrote in message
news:701F4FBD-7537-44B9-9C8D-600744CB013B@.microsoft.com...
> Hi friends,
> How to retrieve all the tables in the database that is having primary key.
> I
> also want to retrieve the foreign key tables with their parent table.
> Thanks
> vanitha|||Vanitha
try this
sp_msforeachtable @.command1 = "sp_helpconstraint '?' "
Regards
R.D
"Vanitha" wrote:

> Hi friends,
> How to retrieve all the tables in the database that is having primary key.
I
> also want to retrieve the foreign key tables with their parent table.
> Thanks
> vanitha|||Vanitha
SELECT Table_Name
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_NAME
),
'IsPrimaryKey') = 1
"Vanitha" <Vanitha@.discussions.microsoft.com> wrote in message
news:701F4FBD-7537-44B9-9C8D-600744CB013B@.microsoft.com...
> Hi friends,
> How to retrieve all the tables in the database that is having primary key.
> I
> also want to retrieve the foreign key tables with their parent table.
> Thanks
> vanitha|||Vanitha
see message pane besides grid pane for foreign key references when you
execute that
Regards
R.D
"R.D" wrote:
> Vanitha
> try this
> sp_msforeachtable @.command1 = "sp_helpconstraint '?' "
> Regards
> R.D
> "Vanitha" wrote:
>

Retrieving values from a subreport to Body of Parent Report

Is it possible to retrieve the value of a subreport's field or control from the parent report? I'm doing some grouping in the subreport and need to retrieve the group by's data value from the subreport.

Also, is there a way to repeat the main page's body when subreport has a page break? ie you page break on some thing in the subreport and need the body and head of the parent report to repeat on subsequent pages.

Thanks,
Garick

I want to do something similar.

I want the value of the amount of records retrieved in the sub report.

The table row that the sub report is in needs to be hidden if the value is not greater than 1.

Can it be done?

|||

Jabuka

I think that there's a simple way to do what you want with creating the same dataset that you have in your subreport in the parent one. Then you can evalute the field in your visibility expression.

Hope it helps you

|||

Another way is to create a simple assembly (any language in .Net) and have a static (or shared) variable in it. Set the value of this variable in your subreport and refer to that in your main report.

The only problem with this approach is concurrency as you are using a static variable.

Shyam

retrieving parent(ascendants)

i have a dimension named 'sdoc' which has 2 members
1) symptom word( first hierarchical level) and
2) doc id (second hierarchical level)

more than one 'symptom word' can have the same 'doc id'.
i want to know which all symptom words have a particular doc id (say 10.)
if i give [10].parent then it returns only the first parent whereas it actually has more than one parent.
how to retrieve all the parents.more than one 'symptom word' can have the same 'doc id'. So...there is a one-to-many relationship between 'doc id' and 'symptom word'?
if i give [10].parent then it returns only the first parent whereas it actually has more than one parent.So...there is a one-to-many relationship between 'symptom word' and 'doc id'?

Huh?

In database relationships, records normally have only one parent...

Please post the DDL code for the tables you are referencing, along with the SQL code you have tried.

Retrieving FirstChild, LastChild from a Distant Parent

I have a Time Dimension, [Time].[Fiscal Year] which has three levels: Year, Qtr, Month.

My situation requires that I get the First member of the first Qtr (month) and the last member of the last Qtr (month) given only the Year. I am close to the solution, if this is possible, and really need some help to finish this. Here is a speific scenario:

From my application, user selects "Fiscal Year" as one of the fiters. Year = 2007 (No additional level is given to select).
The calculation I am trying to form is for % growth ((Measures for FY 2007 - Measures for FY 2006) / Measures for FY 2006).

My problem is that the the total periods for FY 2007 do not yet equal the total periods for 2006. So what I want to do is calculate the growth by summing the periods that exist in 2007 at the month level. Then summing those same periods for the previous period. I can do this by getting the first existing member of the first month and the last existing member of the last month. To expand on the scenario:

FY 2007 has 6 Months of data:

FY 2007
Q1
July
Aug
Sep
Q2
Oct
Nov
Dec

FY 2006 has all 12 Months of Data (Q1, Q2, Q3, Q4)

Given only FY 2007, I need to retrieve Member July and Dec.

The closest I have come to this is:

SET [FirstMonth] AS
'{(Descendants([Time].[Fiscal Year].[2007].FirstChild, 2, SELF))}'

SET [LastMonth] AS
'{(Descendants([Time].[Fiscal Year].[2007].LastChild, 2, SELF))}'

This gives me:

[FirstMonth]

July
Aug
Sep

[LasstMonth]

Oct
Nov
Dec

When running in Select statement. Can this even be accomplished with MDX?

Take a look at OpeningPeriod() and ClosingPeriod() MDX functions - something like:

OpeningPeriod([Time].[Fiscal Year].[Month]) and ClosingPeriod([Time].[Fiscal Year].[Month])

http://msdn2.microsoft.com/en-us/library/ms145992.aspx

>>

SQL Server 2005 Books Online

OpeningPeriod (MDX)

Updated: 17 July 2006

Returns the first sibling among the descendants of a specified level, optionally at a specified member.

...

>>

|||Nice! Thank you very much.