How to check whether my procedure works well in sql

Nel

Hi,
here is my stored procedure, and I want to check it with exec whether it works well
use db1
go
alter PROCEDURE vnesiDok
(
@vp NVARCHAR(30),
@imeprezime NVARCHAR(60),
@valuta_kurs decimal(4,2),
@valuta CHAR(3),
@iznos decimal(10,3),
@iznosden decimal(10,3),
@datum DATETIME,
@br_kas_izvod int,
@br_pat_smetka nvarchar(60),
@tip nvarchar(30),
@brdok INT OUTPUT
)
AS
/* SET NOCOUNT ON */
set @iznosden=0.0;

INSERT INTO blagajna_jspturs
(vp, imeprezime, valuta_kurs,valuta ,iznos, iznosden, datum, br_kas_izvod, br_pat_smetka, tip)
VALUES
(@vp, @imeprezime,@valuta_kurs, @valuta, @iznos, @iznos*@valuta_kurs,@datum, @br_kas_izvod,@br_pat_smetka, @tip);

SELECT @brdok=@@IDENTITY

RETURN

exec vnesiDok
@vp=sdfs,
@imeprezime=dfs,
@valuta_kurs=61.0,
@valuta=E,
@iznos=20.0,
@iznosden=1220.00,
@datum='2011-08-05',
@br_kas_izvod=1,
@br_pat_smetka=df,
@tip=rerf
select @brdok
go

when I run the exec I got an error as if brdok is not declared, and if I declare it before exec, it says it is already declared. Can anybody help me please what's wrong with my procedure?
Thanks in advance

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Zoran Horvat15y ago
Accepted answer
Yes, value for @brdok must be supplied. This should work:

declare @brdokOut int
select @brdokOut = NULL

exec vnesiDok
@vp=sdfs,
@imeprezime=dfs,
@valuta_kurs=61.0,
@valuta=E,
@iznos=20.0,
@iznosden=1220.00,
@datum='2011-08-05',
@br_kas_izvod=1,
@br_pat_smetka=df,
@tip=rerf,
@brdok = @brdokOut OUTPUT

select @brdokOut
Nel15y ago
It is already checked:), thanks
Zoran Horvat15y ago
Please don't forget to tick "This is correct answer" for the post which solved the problem.

Thanks,
Zoran
Nel15y ago
Thank you very much Zoran. This time it works fine:)
Jiteendra Sampathirao15y ago
In the Stored procedure Declare one variable(output variable) and if query runs successfully
add some value to it (EX:1). Otherwise add zero value to it and use those values in C# code behind to figure out the problem.......
Nel15y ago
Thanks, but I tried this and got this error
Must pass parameter number 11 and subsequent parameters as '@name = value'. After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'.
Zoran Horvat15y ago
Try this:

declare @brdok int
exec vnesiDok
@vp=sdfs,
@imeprezime=dfs,
@valuta_kurs=61.0,
@valuta=E,
@iznos=20.0,
@iznosden=1220.00,
@datum='2011-08-05',
@br_kas_izvod=1,
@br_pat_smetka=df,
@tip=rerf,
@brdok OUTPUT
select @brdok

Problem was that @brdok is also a parameter of the procedure, so it must be part of the argument list. Only when procedure exits, you select the @brdok value.

Pozdrav,
Zoran