Retrieve Data from stored procedure 1

[R7Dave]

Hello

I am having trouble returning a value from a stored procedure and displaying it in a text box on my ASP.NET page.

I have had success with ParameterDirection.ReturnValue but I only get 0 when using ParameterDirection.Output

Could someone help by pointing out the error in my ASP.NET code or stored procedure (code below)

The stored procedure is entering data correctly into the table.

Thanks
Dave

CREATE Procedure dbo.sp_AT_Request
(@ProjectNo int,@TmpQuery int OUTPUT)
AS
CREATE TABLE #RN (var int)
SELECT @TmpQuery=
Case when max(requestno) is null
then 1
else max(RequestNo)+1
end
from AT_Request
where ProjectNo = @ProjectNo
DROP TABLE #RN
SELECT @TmpQuery

insert into AT_Request
(ProjectNo , RequestNo )
values
(@ProjectNo, @TmpQuery)

Return @TmpQuery
GO



Protected Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
conn.ConnectionString = Application.Item("connection_string")
Dim Cmd As New SqlClient.SqlCommand
Cmd.Connection = conn
Cmd.CommandText = "dbTicketTracking.dbo.sp_AT_REQUEST"
Cmd.CommandType = CommandType.StoredProcedure

Dim Prm1 As New SqlClient.SqlParameter("@ProjectNo", SqlDbType.VarChar)
Prm1.Direction = ParameterDirection.Input
Cmd.Parameters.Add(Prm1)
Prm1.Value = 5

Dim Prm2 As New SqlClient.SqlParameter("@TmpQuery", SqlDbType.Int)
Cmd.Parameters.Add("@TmpQuery", SqlDbType.Int).Direction = ParameterDirection.Output

conn.Open()

Dim reader As SqlClient.SqlDataReader = Cmd.ExecuteReader()
Dim retParam As String = CType(Cmd.Parameters("@TmpQuery").Value, String)
Dim retParam2 As Integer = Cmd.Parameters("@TmpQuery").Value

conn.Close()
TB_Test.Text = retParam2 ' returns 0
End Sub

 

[gmmastros]

Try modifying the stored procedure to this...

[COLOR=blue]CREATE[/color] [COLOR=blue]Procedure[/color] dbo.sp_AT_Request
	(@ProjectNo [COLOR=blue]int[/color],@TmpQuery [COLOR=blue]int[/color] [COLOR=blue]OUTPUT[/color])
[COLOR=blue]AS[/color]
    [COLOR=blue]SET[/color] [COLOR=#FF00FF]NOCOUNT[/color] [COLOR=blue]ON[/color]

    [COLOR=blue]SELECT[/color] @TmpQuery=
        [COLOR=#FF00FF]Coalesce[/color]([COLOR=#FF00FF]Max[/color](RequestNo), 0) + 1
        [COLOR=blue]from[/color] AT_Request
        [COLOR=blue]where[/color] ProjectNo = @ProjectNo

    [COLOR=blue]insert[/color] [COLOR=blue]into[/color] AT_Request
        (ProjectNo , RequestNo )
    [COLOR=blue]values[/color]
        (@ProjectNo, @TmpQuery)

    [COLOR=blue]Return[/color] @TmpQuery
[COLOR=blue]GO[/color]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[R7Dave]

Thank you - that worked - thank you again.

Dave

 

[gmmastros]

Let me explain this a little.

First, I added SET NOCOUNT ON to the stored procedure. You should get in to the habit of adding this line to every stored procedure you ever write. Obviously, you don't NEED to have it in every stored procedure, but even if it's not needed, it doesn't hurt to have it, so it's a good habit to get in to. In this case, it is probably the reason you were having a problem in the first place.

Second, I saw that you were creating a temp table, but not using it, so I removed that code.

Third, I noticed that you had Select @tmpQuery in the middle of the procedure. You probably had this in there as a debugging step but forgot to remove it.

Fourth, I 're-arranged' your first select query. Technically, it does the same thing as your original. In fact... the only reason I changed it was so that you could see how coalesce works. I often see people write a case statment where a simple Coalesce (or even NullIf) would be a better choice. In this case, you're dealing with scalar values, and performance would be the same in either case, but knowing what functions are available to you as a programmer is helpful information to have handy.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[R7Dave]

George

Thank you for taking the time to explain this - I started to look at the improvements you made and scheduled myself to go over it again.

Thanks
Dave

 

[compu66]

you have explained everything well But can I know what is "Coalesce" and "set Nocount" on used.

 

[jbenson001]

But can I know what is "Coalesce" and "set Nocount" on used

Check books on line that comes with SQL Server.