[RESOLVED] how to return value from SQL 2k5 SPs

here the snipset how i insert row to tbldtr
1. first its search for the employee number pass by variable @prm_empno
2. if the employee basically it wont insert row in tbldtr as defined by
IF statement

now what i am trying to work if ever there will be no match of employee
is to send something to recordset set that the employee did not exist in the
master employee table.

Code:

---

USE [dbname]
GO
/****** Object:  StoredProcedure [dbo].[add_todtr]    Script Date: 08/01/2007 09:51:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[add_todtr]
        -- Add the parameters for the stored procedure here
@prm_tradeno nvarchar(10),
@prm_empno nvarchar(10),
@prm_ccno nvarchar(10),
@prm_tiAM nvarchar(10),
@prm_toAM nvarchar(10),
@prm_tiPM nvarchar(10),
@prm_toPM nvarchar(10),
@prm_Remarks nvarchar(50),
@prm_dtfile nvarchar(20),
@prm_deviceno nvarchar(10),
@prm_dtmigrate nvarchar(20)




AS
declare @p_cateno nvarchar(10), @p_firmno nvarchar(10)
BEGIN

-- check if employee no is in employee master
--table
select @p_cateno=fk_cateno, @p_firmno=fk_firmno from tblemployee
where pk_empno=@prm_empno

if @p_cateno<>'' or @p_cateno<>null
        begin
        begin transaction

        insert into tbldtr(
                                fk_empno,
                                dtmigrate,
                                dtfile,
                                fk_cateno,
                                fk_ccno,
                                fk_tradeno,
                                fk_firmno,
                                deviceno,
                                TI_AM,
                                TO_AM,
                                TI_PM,
                                TO_PM,
                                Remarks)
        values(
                @prm_empno,
                @prm_dtmigrate,
                @prm_dtfile,
                @p_cateno,
                @prm_ccno,
                @prm_tradeno,
                @p_firmno,
                @prm_deviceno,
                @prm_tiAM,
                @prm_toAM,
                @prm_tiPM,
                @prm_toPM,
                @prm_Remarks

        )
        if @@error <> 0
          begin
                rollback transaction
                return
          end

        commit transaction
end
END

---

I haven't done too much in this regard but the following link should get you started on the right path

http://www.sqlteam.com/article/store...returning-data

For starters, this won't work:
if @p_cateno<>'' or @p_cateno<>null

If should be

IF ISNULL(@p_cateno, '') <> '' ...


Secondly, I don't know why you want to return a recordset.... you're not returning one now...

Lastly, if you modify the end of your query, you could return a value for success, failure, or not found...

Code:

---

        if @@error <> 0
          begin
                rollback transaction
                return 1 -- This will tell you that it was rolled back
          end

        commit transaction
        return 0 -- This will tell you that it succeeded
end
Return -1 -- This will tell you that the record was not found
END

---

-tg