StoredProcedure e - determine success/failure

[thesti]

Hi,

how do i determine whether a INSERT/UPDATE/DELETE stored procedure is successed/failured.


if for example, I have the following stored proc here:

create procedure sp_add_employee
@userid int,
@password varchar(20)
as
insert into employee values(@userid,@password)
 

How do I know whether the insert is successful?


Please advise.

Thanks

[Plater]

For that Insert statement, if it failed, an Exception would have been thrown.

Updates and Deletes are trickier
If you had for example

UPDATE employee 
SET Password=@password
WHERE
UserID= @userid
 

You could modify it do say this:

SELECT @AffectedRows=count(*)
FROM employee
WHERE
UserID= @userid
 
UPDATE employee 
SET Password=@password
WHERE
UserID= @userid
 
return @AffectedRows
 

That should tell you how many rows were effected by the UPDATE command (0 rows, 1 row, 36 rows, whatever the case may be)
You can do similar for the DELETE command

[nateraaaa]

You need to add error handling to your stored procedure. Check out this link for more information. Pay particular attention to the @@ERROR system function.

Nathan

[azizonin]

Dear Friend,

I will suggest you to write the query in sql server itself and run at the same time, it will let you know the error if you passing through, and i think just copy the codes written in the fronthand and just paste to the sql server and just, follow the errors and you would like to learn the more as you indulje to write more queries there..

Regds
Aziz

[ssmeshack]

Dear Friend,

I will suggest you to write the query in sql server itself and run at the same time, it will let you know the error if you passing through, and i think just copy the codes written in the fronthand and just paste to the sql server and just, follow the errors and you would like to learn the more as you indulje to write more queries there..

Regds
Aziz

I always do like what Aziz suggest.

[thesti]

hi,

thanks for the reply.

just now i was practicing using output parameter from StoredProcedure .
i have the following StoredProcedure

create procedure sp_get_supplier_code
(
    @supplier_code char(3) output
)
as
    set nocount on
    set @supplier_code = 'SP'
 

then i try to access it by creating a SqlConnection and a SqlCommand and setup the output parameter, then i execute it using sqlCmd.executeN onQuery(), then i try to access the output parameter by using the following code

MsgBox(sqlCmd.Parameters("@supplied_code").Value)
 

but i only get an "S" from the messagebox, it seems that it only return the first character of 'SP'. is there something wrong?

Thank you

[Plater]

try varchar instead of char ?

[thesti]

hello,

thanks for the reply. i've changed it to varchar, but still i only get an 'S'.

Thank you

[Plater]

How did you define the @supplied_code in code side, as what data type?

[thesti]

i define it as SqlDbType.VarCh ar

here's the code that define it

MainForm.dbCon.addOutputParameter("@supplier_code_output", "", SqlDbType.VarChar)
 

(in the example above i've changed the parameter name to @supplier_code_ output).
in the program i have a class which encapsulate a DBConnection to the SQL Server 2K.

here's the definition of the addOutputParame ter method

Public Sub addOutputParameter(ByVal key As String, ByVal value As String, _
                                ByVal type As SqlDbType)
        Dim sqlParam As New SqlParameter()
 
        sqlParam.ParameterName = key
        sqlParam.Value = value
        sqlParam.SqlDbType = type
        sqlParam.Direction = ParameterDirection.Output
 
        sqlParams.Add(sqlParam)
 
        'register it to sqlOutput, so it's available to outside class
        sqlOutput.Add(key, value)
    End Sub
 

Thank you.