Showing posts with label return. Show all posts
Showing posts with label return. Show all posts

Friday, March 23, 2012

Problems with Return Value with Stored Procedure

Hi,

I have facing this problem, and i don't know what is going wrong.

The SP when tested in the Query Analyser works fine.

the resultant is either a 0 or PID where 0 is returned when the insert doesn't happen

I might have some extra declaration, which might be causing this error , or not defining the right thing.

Any suggestion is welcomed and greatly appreciated.

Thanks


Private Sub btnCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreate.Click
Dim sSQLConnection As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim daInsertSku As SqlDataAdapter = New SqlDataAdapter()
Try
sSQLConnection.Open()
Dim cmdInsert As New SqlCommand("sp_request", sSQLConnection)
cmdInsert.CommandType = CommandType.StoredProcedure

cmdInsert.Parameters.Add("@.Username", SqlDbType.Int, 4)
cmdInsert.Parameters("@.Username").Value = "0"

Dim PID As SqlParameter = New SqlParameter("@.identity", SqlDbType.Int)
PID.Direction = ParameterDirection.ReturnValue
cmdInsert.Parameters.Add(PID)

daInsertSku.InsertCommand = cmdInsert
cmdInsert.ExecuteNonQuery()
Response.Write(PID.Value)

Catch Exc As Exception
' error handling
Finally
' close connection
End Try
End Sub

Is PID a return value, or an output parameter? You should use an output parameter to return an ident from a sproc.

And what is wrong? Are you getting an error?|||I did get it working, i changed to Output Parameter instead or Return

thank yousql

Saturday, February 25, 2012

Problems with @@ROWCOUNT

Hi,
I am just starting sql and have a stored proc which does a simple select and should return 2 when no rows match the selection criteria . The problem is that it always returns 2, even where there are rows which match the selection criteria and when there are no rows.
CREATE PROCEDURE dbo.SelectSomething
@.a INT,
@.b INT
AS
SET NOCOUNT ON
SELECT a, b, c, d FROM dbo.SomeTable WITH (READUNCOMMITTED)
WHERE a = @.a AND b = @.b
IF @.@.ERROR<>0
RETURN 1
IF @.@.ROWCOUNT=0
RETURN 2 --Always Returns 2
RETURN 0
GO
Thanks for any help.because your condition for @.@.ROWCOUNT is checked against the statement@.@.ERROR which will always be 0 and evaluate to TRUE. You could dosomething like this:
CREATE PROCEDURE dbo.SelectSomething
@.a INT,
@.b INT
AS
SET NOCOUNT ON
DECLARE
@.error int,
@.rowcount int
SELECT
a, b, c, d
FROM
dbo.SomeTable WITH (READUNCOMMITTED)
WHERE
a = @.a AND b = @.b
SELECT
@.error = @.@.ERROR, @.rowcount= @.@.ROWCOUNT
IF @.ERROR<>0
RETURN 1
IF @.ROWCOUNT=0
RETURN 2
RETURN 0
GO

|||

You are right. Thanks very much. I will keep this in mind the next time. Thanks again.