MSDE (SQL Server 2000) does not return @@Identity

Hi All,
I'm creating an appplication with some stored procedures. The stored
procedure needs to return the @.@.Identity of the table's new record. The
function has no errors and compiles without any warnings. The function
does work fine some times (returning value). But most of the calls are
failing and the function does not return proper value.
CREATE PROCEDURE CreateNewCourse
@.CourseName NVARCHAR(256),
@.Duration Int,
@.Contents NVarChar(500),
AS
DECLARE @.Identity int
INSERT into CourseMaster
(
CourseName,Contents,
Duration
)
VALUES
(
@.CourseName,
@.Contents,
@.Duration
)
SET @.Identity = SCOPE_IDENTITY()
return @.Identity
Any suggestions?
TIA.Hi
Can you show us how you call the SP ?
To reproduce your problem please post DDL+ sample data?
"Anbu" <t_anbazhagan2001@.yahoo.co.in> wrote in message
news:1143606553.333820.230400@.t31g2000cwb.googlegroups.com...
> Hi All,
> I'm creating an appplication with some stored procedures. The stored
> procedure needs to return the @.@.Identity of the table's new record. The
> function has no errors and compiles without any warnings. The function
> does work fine some times (returning value). But most of the calls are
> failing and the function does not return proper value.
> CREATE PROCEDURE CreateNewCourse
> @.CourseName NVARCHAR(256),
> @.Duration Int,
> @.Contents NVarChar(500),
> AS
> DECLARE @.Identity int
> INSERT into CourseMaster
> (
> CourseName,Contents,
> Duration
> )
> VALUES
> (
> @.CourseName,
> @.Contents,
> @.Duration
> )
>
> SET @.Identity = SCOPE_IDENTITY()
> return @.Identity
> Any suggestions?
> TIA.
>|||I'm trying to execute the function from Visual Studio .NET IDE (through
Database Explorer). Normally it will return the output in the "output"
window of VS .NET IDE.|||"Anbu" <t_anbazhagan2001@.yahoo.co.in> wrote in message
news:1143613779.111998.71180@.g10g2000cwb.googlegroups.com...
> I'm trying to execute the function from Visual Studio .NET IDE (through
> Database Explorer). Normally it will return the output in the "output"
> window of VS .NET IDE.
>
Don't use RETURN to ouptut results. Use OUTPUT parameters and keep RETURN
for what it is intended, i.e. error status. Make sure you set NOCOUNT ON.
Try:
CREATE PROCEDURE CreateNewCourse
@.CourseName NVARCHAR(256),
@.Duration Int,
@.Contents NVarChar(500),
@.Identity INT OUTPUT
AS
SET NOCOUNT ON
DECLARE @.result INT
INSERT into CourseMaster
(
CourseName,Contents,
Duration
)
VALUES
(
@.CourseName,
@.Contents,
@.Duration
)
SET @.result =@.@.ERROR
IF @.result > 0
BEGIN
/* Error handling? */
EXEC usp_error_handler @.result, ... ;
END
SET @.Identity = SCOPE_IDENTITY()
RETURN @.result
GO
DECLARE @.i INT
EXEC CreateNewCourse
@.CourseName = '',
@.Duration = 0,
@.Contents = '',
@.Identity = @.i OUTPUT
SELECT @.i
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||It seem's it's a problem with the IDE and MSDE connection, or the way
the IDE returns the data from MSDE. I could run the same Stored Proc
from an application and retrieve the results in the return value.
Thanks,|||Does the table have an identity column?
"Anbu" <t_anbazhagan2001@.yahoo.co.in> wrote in message
news:1143606553.333820.230400@.t31g2000cwb.googlegroups.com...
> Hi All,
> I'm creating an appplication with some stored procedures. The stored
> procedure needs to return the @.@.Identity of the table's new record. The
> function has no errors and compiles without any warnings. The function
> does work fine some times (returning value). But most of the calls are
> failing and the function does not return proper value.
> CREATE PROCEDURE CreateNewCourse
> @.CourseName NVARCHAR(256),
> @.Duration Int,
> @.Contents NVarChar(500),
> AS
> DECLARE @.Identity int
> INSERT into CourseMaster
> (
> CourseName,Contents,
> Duration
> )
> VALUES
> (
> @.CourseName,
> @.Contents,
> @.Duration
> )
>
> SET @.Identity = SCOPE_IDENTITY()
> return @.Identity
> Any suggestions?
> TIA.
>