Profile / Stored Procedure

Hi,

I have created a Stored Procedure, under Stored procedures section under Enterprise Manager on SQL server 2000.

Could anybody tell me, how and what are the steps to follow to TRACE the procedure , using SQL PROFILER ?

Please advice me !

NicolFrom Enterprise Manager:
-- choose Tools
-- SQL Profiler
-- File
-- New
-- Trace
-- select/enter connection informatin as appropriate (I was able to accept the defaults)
-- press OK
-- click the General tab of the Trace Properties window
-- from the "Template name:" dropdown choose SQLProfilerTSQL_SPs
-- choose RUN
-- do whatever it is you need to do to run your Stored Procedure (from within Query Analyzer or ASP.NET)
-- go back to Profiler and click the red square "stop selected trace" icon
-- review the results

Terri|||Thanks Tmorton,

I got started with checking SP using SQL profiler.. :)

Now I found, I think, I have problem with coding in VB.NET or problem in SP.

Even though, I have given valid Username/Password, RETURNVALUE is always showing zero .

Stored procedure
-------
CREATE PROCEDURE test
(@.username varchar(100),
@.userpwd varchar(200)
)
AS
set nocount on
return (select count(*) as s from employer
WHERE user_name like '%@.username%' and
password ='@.userpwd')

GO

VB.NET Code using SP
--------
CmdCoInfo = New SqlCommand("test", ConVM)
CmdCoInfo.CommandType = CommandType.StoredProcedure
CmdCoInfo.Parameters.Add("@.username", Trim(StrUser))
CmdCoInfo.Parameters.Add("@.userpwd", Trim(StrPass))
Dim paramOut As SqlParameter
paramOut = CmdCoInfo.Parameters.Add("Returnvalue", SqlDbType.Int)
paramOut.Direction = ParameterDirection.ReturnValue
paramOut.Size = 40
' Retrieve the record that matches the username/password
ConVM.Open()
CmdCoInfo.ExecuteNonQuery()

Dim retrr As String
If Not IsDBNull(CmdCoInfo.Parameters("Returnvalue").Value) Then
retrr = (CmdCoInfo.Parameters("Returnvalue").Value)
Else
retrr = "Not know"
End If
Response.Write(retrr)

--------------

Is there problem with SP or VB.net code ?

Please advice !|||The problem is in your stored procedure.

Instead of this:

return (select count(*) as s from employer
WHERE user_name like '%@.username%' and
password ='@.userpwd')
GO

This would be more correct:

DECLARE @.RecordCount integer
select @.RecordCount= count(*) from employer
WHERE user_name like '%'+@.username +'%' and
password =@.userpwd
RETURN @.RecordCount
GO

HOWEVER, ReturnValue is meant to signal back to the calling program whether or not the stored procedure was successful. So you are misusing it to return a COUNT. You should use an Output parameter instead. Add an additional parameter called RecordCount with ParameterDirection of Output. Add the Output parameter to your stored procedure, which will look like this:

CREATE PROCEDURE test
(@.username varchar(100),
@.userpwd varchar(200) ,
@.RecordCount int OUTPUT
)
AS
set nocount on
select @.RecordCount= count(*) from employer
WHERE user_name like '%'+@.username +'%' and
password =@.userpwd
RETURN @.@.ERROR
GO

I haven't tested this code but it should be about right.

Terri|||Terri,

ur 2nd option worked for me. But i didnt understand why I am still getting zero as ReturnValue , when I tried with 1st option(given below), eventhough record exists for that username/pwd.

DECLARE @.RecordCount integer
select @.RecordCount= count(*) from employer
WHERE user_name like '%'+@.username +'%' and
password =@.userpwd
RETURN @.RecordCount
GO

Please advice !|||I used that same stored procedure, and your code modified slightly which executes the procedure and puts the returnvalue into a label and it works as expected:

CmdCoInfo = New SqlCommand("returntest", SqlConnection)
CmdCoInfo.CommandType = CommandType.StoredProcedure
CmdCoInfo.Parameters.Add("@.test1", 1)
Dim paramOut As SqlParameter
paramOut = CmdCoInfo.Parameters.Add("Returnvalue", SqlDbType.Int)
paramOut.Direction = ParameterDirection.ReturnValue
paramOut.Size = 40
SqlConnection.Open()
CmdCoInfo.ExecuteNonQuery()
Label1.Text = CmdCoInfo.Parameters("ReturnValue").Value

Terrisql