Stored Precedure Return Code - 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 2000 Forums

Transact-SQL (2000)

Stored Precedure Return Code

Author

Topic

Krenlore
Starting Member

3 Posts

Posted - 2005-07-05 : 00:12:42

I have been trying to understand exactly how to return a value from a stored procedure. Unfortunately I'm not hitting the mark. Here is my sp:

CREATE PROCEDURE [dbo].[usp_CreateNewProject]

@ProjectName varchar(255),
@ProjectID int =0 OUTPUT

as

if exists (select ProjectID from tProjectSummary where ProjectName = @ProjectName)
RETURN @ProjectID
else
insert tProjectSummary (ProjectName)
values(@ProjectName)

SELECT SCOPE_IDENTITY()
GO

basically I want to send the stored procedure a project name, check if it exists and send back a zero. If it doesn't exist then I want to insert the record and return the identity value the insert generated.

Can anyone provide some direction?

Thanks in advance

~Kren

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-07-05 : 00:15:56

CREATE PROCEDURE [dbo].[usp_CreateNewProject]

@ProjectName varchar(255),
@ProjectID int =0 OUTPUT

as

if exists (select ProjectID from tProjectSummary where ProjectName = @ProjectName)
RETURN 0
else
insert tProjectSummary (ProjectName)
values(@ProjectName)

SELECT @ProjectID = SCOPE_IDENTITY()

GO

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Krenlore
Starting Member

3 Posts

Posted - 2005-07-05 : 00:33:45

Thanks for the response. I see how I was missing the assignment of the SCOPE_IDENTITY value to the return param. (doh)

Seems I am doing something else wrong in my code. The return code coming back is always 0 (zero), whether the project exists or not. I thought I could start putting some hair back on my head but it looks like I have to pull a bit more out.

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2005-07-05 : 00:42:52

post your updated stored procedure & table structure of tProjectSummary

mk_garg

Krenlore
Starting Member

3 Posts

Posted - 2005-07-05 : 00:51:06

I've changed the stored procedure to:

CREATE PROCEDURE [dbo].[usp_CreateNewProject]

@ProjectName varchar(255),
@ProjectID int OUTPUT

as

if exists (select ProjectID from tProjectSummary where ProjectName = @ProjectName)
SELECT @ProjectID = 0
else
insert tProjectSummary (ProjectName)
values(@ProjectName)

SELECT @ProjectID = SCOPE_IDENTITY()

RETURN @ProjectID
GO

I'm pretty sure it should be doing the same thing (pre mucking).

The vb.net code I am using to call the strored procedure is:

Dim Parameters(1) As SqlClient.SqlParameter
Dim ProjectID As Integer
Dim SPName As String

Parameters(0) = CreateSqlParm("@ProjectName", SqlDbType.VarChar, 255, ParameterDirection.Input, ProjectName)
Parameters(1) = CreateSqlParm("@ProjectID", SqlDbType.Int, 9, ParameterDirection.Output, ProjectID)
SPName = "usp_CreateNewProject"

Try
SqlHelper.ExecuteScalar(sConnectionString, CommandType.StoredProcedure, SPName, Parameters)
CreateNewProject = ProjectID
Catch ex As Exception
ExceptionManager.Publish(ex)
Throw ex
End Try

The execution of the stored procedure completes. When I look at the table after the call the new record is there but the "ProjectID" has a value of 0 (zero). The project id field of the new record reflects the current (new) identity value.

I hope this is something stupid that I am missing because I've been staring at this for too long.

Cheers

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2005-07-05 : 01:14:23

try this



CREATE PROCEDURE [dbo].[usp_CreateNewProject]
(
	@ProjectName varchar(255),
	@ProjectID int OUTPUT
)
as

if exists (select rojectID from tProjectSummary where ProjectName = @ProjectName)
	SELECT @ProjectID = 0
else
BEGIN
	insert tProjectSummary (ProjectName)
	values(@ProjectName)
	SELECT @ProjectID = SCOPE_IDENTITY()
END
GO

test it in QA first

mk_garg

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-07-05 : 05:48:22

declare @ProjectID int
declare @returnCode int

exec @returnCode = dbo.usp_CreateNewProject @ProjectName = '...', @ProjectID = @ProjectID output

select @ReturnCode, @ProjectID

Next procedure would return:
1. If project existed before sp execution: @ReturnCode = 0, @ProjectID is null
2. If project didn't exist: @ReturnCode = -1, @ProjectID = value of identity column from inserted row.

Procedure code:

CREATE PROCEDURE [dbo].[usp_CreateNewProject]

@ProjectName varchar(255),
@ProjectID int OUTPUT

as begin

 if exists (select ProjectID from tProjectSummary where ProjectName = @ProjectName)
  RETURN 0
 else begin
  insert tProjectSummary (ProjectName)
  values(@ProjectName)
--
  SELECT @ProjectID = SCOPE_IDENTITY()
  return (-1)
 end
end
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources