help with stored procedure

[Mike Bishop]

i am trying to write a SP that will return a value in vb.net

here is my SP

CREATE PROCEDURE ClaimTotal @Ctotal BIGINT output
AS
select SUM(total) from claim
where @ctotal = total

I want to SUM my total coloum from my table claim, and store this in a
varible called @ctotal so then i can pull this into vb.net

this is all new to me, as i do normal select queries but i have read that stored procedures are the way to go.

please please help a newbie

also how would i then pull the @ctotal into vb.net?

[prashantchalise]

Hi,

Initially you need to bind the total Sum to the @Ctotal (as Ctotal is OutPut parameter) in your SP with name "ClaimTotal" in following manner,

SELECT @Ctotal = SUM(total) FROM claim

Afterwards, You need to create proper code block to find value in @Ctotal.

Dim cTotal As Integer
Dim p(1) As SqlParameter
p(0) = New SqlParameter("@Ctotal", SqlDbType.BigInt)
p(0).Direction = ParameterDirection.Output
ExecuteNonQuery(CommandType.StoredProcedure, "ClaimTotal", p)
cTotal = CInt(p(9).Value)

Please Note that, ExecuteNonQuery is a method described in Microsoft Application Block for .NET and can be used as to perform the most common data access tasks against a Microsoft SQL Server 200X database. You can download it from here.

Thanks,