I'm new in stored Procedures - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

I'm new in stored Procedures

Author

Topic

rtutus
Aged Yak Warrior

522 Posts

Posted - 2006-05-10 : 15:47:19

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.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-10 : 15:49:59

Here's how to execute it in Query Analyzer:

DECLARE @total bigint
EXEC dbo.totalSub 1, @total OUTPUT
PRINT @total

Tara Kizer
aka tduggan

rtutus
Aged Yak Warrior

522 Posts

Posted - 2006-05-10 : 16:21:03

so is my stored procedure correct? I want to execute it from Visual Basic

Thanks alot

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-10 : 16:24:38

Well you don't have a FROM clause. Here's how I'd write it:

CREATE PROC dbo.totalSub
(@AccNmbr bigint, @total bigint OUTPUT)
AS

SELECT @total = SUM(SubNmbr)
FROM SomeTable
WHERE AccNmbr = @AccNmbr

GO

Also, you should test your stored procedures inside Query Analyzer before trying them out in your application. You have to ensure that they work properly there before you even have a chance of getting it to work in VB.

Tara Kizer
aka tduggan

rtutus
Aged Yak Warrior

522 Posts

Posted - 2006-05-10 : 16:38:08

sorry I made a mistake im my email. I already put the From in my clause but I didn t mention it in my message
Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-10 : 16:44:59

So does it work fine in Query Analyzer?

Tara Kizer
aka tduggan

rtutus
Aged Yak Warrior

522 Posts

Posted - 2006-05-10 : 16:57:37

worked perfectly in query analyser i ll try it in my VB application