Monday, March 12, 2012

Im new to stored Procedures

I create my store proc. I want to give it a parameter AccNmbr and I want it to return to me the total of the columns SubNmbr in total variable; total is gonna be an output. So I do this in my Stored Proc:

Create Procedure dbo.totalSub
@.AccNmbr bigint,
@.total bigint out
AS
Select @.total=sum(SubNmbr) where AccNmbr=@.AccNmbr
return

Go

When I run the proc it tells me that parameter total was not provided. I also tried with return (instead of Return @.total)
it always tells me missing total parameter. But I want total to be my result, not my input. Only AccNmbr is my input.
Thanks a lot for your help.

When you create this Stored Procedure, you have 2 input parameters. Either you can specify one as output, or just have 1 input... Here's a modification that will do what it seems you were doing. You also missed what table you were pulling the results from, that is mentioned by "FROM TABLE".

Here ya go:

Create

Procedure dbo.totalSub
@.AccNmbr int,
AS
Selectsum(SubNmbr)FROMTABLEwhere AccNmbr=@.AccNmbr
Go

|||

You re right, I correct my stuff as shown bellow:

My stored procedure works perfectly when I run Query Analyser, but when I run my VB program I get the eror: I get the message : An SqlParameter with ParameterName'@.total' is not contained by this SqlParameterCollection.


Here is my stored Proc and my VB program is right below

I- Stored Proc:

CREATE PROCEDURE dbo.totalsub
@.account bigint,
@.total bigint output
AS
select total=sum(SubPhnNmbr) from tblsub whereSubAccNmbr=@.account
return
GO

II- And my pogram in VB is:

Dim totsub As Int64
Dim cm As New SqlCommand
Dim cn As New MyConnection
cn.open
'my connection is defined by me don't worry about it
cm.CommandType = CommandType.StoredProcedure
cm.CommandText = "totalsub"
cm.Connection = cn
Dim pm As SqlParameter
pm = cm.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Account", System.Data.SqlDbType.BigInt))
pm.Value = 100000165
pm = cm.Parameters.Add(New System.Data.SqlClient.SqlParameter("total", System.Data.SqlDbType.BigInt, 4))
pm.Direction = ParameterDirection.Output
totsub = cm.Parameters("total").Value
cm.ExecuteScalar()
totsub = cm.Parameters("total").Value


I also tried using @.total instead of total and I tried ParameterDirection.ReturmValue instead of ParameterDirection.Output

No Luck, thanks a lot for help

No comments:

Post a Comment