get return value from stored proc

How can I get the return value from stored procedure?
Basically, if employee already exists in the following sp, I would like to get the ReturnCode -1 in VB 6 app and display "employee already exists" message. Or, I would like to raise an error in sp which can be displayed in VB client app. How can I do this? Thanks.

hr_addNewEmployee:

DECLARE @.lReturnCode INT
IF EXISTS ( SELECT e.EmployeeID
FROM Employee e
WHERE
e.EmployeeID = @.NewEmployeeID)
BEGIN
SELECT @.lReturnCode = -1
RETURN @.lReturnCode
END

ELSE
...
...
...
RETURN @.lReturnCode

Pretty easy:

declare @.returnValue int
execute @.returnValue = procedureName
select @.returnValue

there is also a way to get that info from the ADO.NET, but you would want to go to the ADO.NET forums for that info

To raise an error just add:

Raiserror ('your error here',16,1)

That is what I would probably do, though for your error, if you are inserting a single employee. just insert the row and let the constraint get it. Same amount of work to check existence but when the work is done, you just have to add the row to the physical structures, rather then do it again.

The duplicate row error will tell you that you have a duplicate (it will always be the same error number) so you can catch it on the client side, or in 2005 you can use a try...catch and look at the ERROR_NUMBER() function and get that value.|||

Yes you can...

Code Snippet

Dim oconn As New ADODB.Connection
oconn.ConnectionString = "{Your Connection STRING}"
oconn.Open

Dim ocmd As New ADODB.Command
Set ocmd.ActiveConnection = oconn
ocmd.CommandType = adCmdStoredProc
ocmd.CommandText = "dbo.testReturn"

Dim param As New ADODB.Parameter
param.Direction = adParamReturnValue
param.Type = adInteger
ocmd.Parameters.Append param

ocmd.Execute

MsgBox param.Value

get record with earliest datetime value

Hello all,

Quick sql syntax question:

I have this table:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[REQUESTS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[REQUESTS]
GO

CREATE TABLE [dbo].[REQUESTS] (
[ROW_ID] [uniqueidentifier] NULL ,
[REQUEST_DATE] [datetime] NULL ,
[STATUS] [tinyint] NULL
) ON [PRIMARY]
GO

with these values:

insert into REQUESTS (REQUEST_DATE, STATUS)
values (getdate(), 0)
insert into REQUESTS (REQUEST_DATE, STATUS)
values (getdate(), 1)
insert into REQUESTS (REQUEST_DATE, STATUS)
values (getdate(), 0)

I need to select the single record with a STATUS = 0 with the earliest
REQUEST_DATE

I am using this query:
SELECT TOP 1 ROW_ID FROM REQUEST_LOG WHERE STATUS = 0 ORDER BY
REQUEST_DATE

not sure if this is the way to go...

pointer appreciated
thanksHow about doing this:
1: Change Row_ID from NULL to NOT NULL
CREATE TABLE [dbo].[REQUESTS] (
[ROW_ID] [uniqueidentifier] NOT NULL ,
[REQUEST_DATE] [datetime] NULL ,
[STATUS] [tinyint] NULL
) ON [PRIMARY]
GO

2: Add value for column ROW_ID in INSERT:
insert into REQUESTS (ROW_ID, REQUEST_DATE, STATUS)
values (NEWID(), getdate(), 0)
insert into REQUESTS (ROW_ID, REQUEST_DATE, STATUS)
values (NEWID(), getdate(), 1)
insert into REQUESTS (ROW_ID, REQUEST_DATE, STATUS)
values (NEWID(), getdate(), 0)

3: Use correct table name in SELECT - from REQUEST_LOG to REQUESTS
SELECT TOP 1 ROW_ID FROM REQUESTs WHERE STATUS = 0 ORDER BY
REQUEST_DATE

On Feb 9, 9:59 am, "hharry" <paulquig...@.nyc.comwrote:

Quote:

Originally Posted by

Hello all,
>
Quick sql syntax question:
>
I have this table:
>
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[REQUESTS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[REQUESTS]
GO
>
CREATE TABLE [dbo].[REQUESTS] (
[ROW_ID] [uniqueidentifier] NULL ,
[REQUEST_DATE] [datetime] NULL ,
[STATUS] [tinyint] NULL
) ON [PRIMARY]
GO
>
with these values:
>
insert into REQUESTS (REQUEST_DATE, STATUS)
values (getdate(), 0)
insert into REQUESTS (REQUEST_DATE, STATUS)
values (getdate(), 1)
insert into REQUESTS (REQUEST_DATE, STATUS)
values (getdate(), 0)
>
I need to select the single record with a STATUS = 0 with the earliest
REQUEST_DATE
>
I am using this query:
SELECT TOP 1 ROW_ID FROM REQUEST_LOG WHERE STATUS = 0 ORDER BY
REQUEST_DATE
>
not sure if this is the way to go...
>
pointer appreciated
thanks

|||apologies for the typos

what i should have asked is this:

Is TOP applied after the ORDER BY or before...can someone confirm
this ?|||Yes, TOP is applied after the result set rows are ordered with ORDER BY.

Regards,

Plamen Ratchev
http://www.SQLStudio.com