Problem returning OUTPUT in stored procedure

Hi, I have this output, @.RegisterFlag int OUTPUT

and I have a transaction going on, so my code (I just put some relevant code here) is:

1BEGIN TRAN2 SELECT @.getDealername = OrgNameFROM OrgWHERE OrgName = @.DealerName3If @.getDealernameisnull45ELSE6 BEGIN7 set @.RegisterFlag = 28ROLLBACK TRAN9 RETURN10 END1112COMMIT TRAN13set @.RegisterFlag = 1

The problem I am facing now is I couldn't get @.RegisterFlag = 2 return back to my asp.net code when it reached line 7, instead I got this error mesg:

Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.

How do I fix this? Many big thanks.

Hi, it's ok already. I realize that the problem is I have both transaction code, one in asp.net and another one in stored procedure. After taken out the one in asp.net, it works now. Thanks.

Problem linking tables

I have two tables, TableA and TableB.
Each has a date in smalldatetime format.
TableA has a qty field in int format.
TableB also has a nvarchar field in which the date is in the format
YYYYMMDD

A simple Query on TableA sum Qty returns a result of 100.
However, When I do a left outer join from TableA to TableB on Date, the
sum of Qty on TableA becomes massivley larger.

What could be causing this?

Regards,
Ciarn(chudson007@.hotmail.com) writes:
> I have two tables, TableA and TableB.
> Each has a date in smalldatetime format.
> TableA has a qty field in int format.
> TableB also has a nvarchar field in which the date is in the format
> YYYYMMDD
> A simple Query on TableA sum Qty returns a result of 100.
> However, When I do a left outer join from TableA to TableB on Date, the
> sum of Qty on TableA becomes massivley larger.
> What could be causing this?

Apparently there is more than one row in TableB with the same date
as in TableA.

This should be evident if you replace SUM(qty) with *, so that
you see all rows in the query.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications. Please learn that rows are not records and columns are
not fields.

>> TableB also has a nvarchar field in which the date is in the format
YYYYMMDD <<

Why? It is redundant. It is in the wrong data type. Do you actively
seek to make queries run longer and be more error prone? What
constriants do you have on that column to prevent illegal dates?

>> When I do a left outer join from TableA to TableB on Date, the sum
of Qty on TableA becomes massivel larger. <<

DATE is a reserved word in SQL and too vague to be proper data element
name. It will be because the same value appears multiple times in
TableB and you get a CROSS JOIN effect.