Stored procedure problem of out parameter

Question

Dear experts
I am not getting my error please help me out
I have stored procedure having out parameter like this

@pa_error varchar(800) output

and from fromt end i am calling like this

cmd.Parameters.Add("@pa_error", SqlDbType.NVarChar, 100).Direction = ParameterDirection.Output

still it is giving error too many arguments specified

Mayur Gujrathi · Answer

Yes omkar my query solved i was doing mistake i had write twice cmd.Parameters.AddWithValue(@pa_boname ) sorry once again your

omkar mhaiskar · Answer

dear Mayur your problem is getting solved or not? Thanks And Regards Omkar

Mayur Gujrathi · Answer

Sorry omkar sorry ankit i was doing wrong i had write twice cmd.Parameters.AddWithValue(@pa_boname ) sorry once again your valuable time wasted

Mayur Gujrathi · Answer

Yes omkar i tried it now it is giving error size is not mentioned then i changed to 800 from 0 then again it gives too many arguments

omkar mhaiskar · Answer

Dear mayur this is already present in your stored procedure. DO same process as like i write. @pa_error varchar(800) output so just you need to do in your code behind file. or like this is in your codebehind file. this is in your codebehind cmd.Parameters.Add(@pa_error SqlDbType.VarChar0Return); cmd.Parameters(@pa_error).Direction = ParameterDirection.Output check with following and let me know. Thanks And Regards Omkar.

Mayur Gujrathi · Answer

Dear omkar i have no authority to change procedure my dba will not be allow to change it what to do? is there any other way

omkar mhaiskar · Answer

do as like this. this is in your codebehind _command.Parameters.Add(@Return SqlDbType.BigInt 0 Return); _command.Parameters[@Return].Direction = ParameterDirection.Output; in the stored procedure add this. @Return int = null Output Thanks and Regards Omkar

Mayur Gujrathi · Answer

If you see procedure then there are many actions but what work i have given in my team there is no use of that parameter so just to access procedure i am passing parameter my action is if @pa_action ='SEARCHMAK' begin select * from Dis_req_Dtls_MAK where deleted_ind IN (046) AND CREATED_BY = @PA_LOGIN_NAME end at the end of stored procedure

omkar mhaiskar · Answer

ok if you don't want to return any out put then y did u want to use output parameter.

Mayur Gujrathi · Answer

I dont want to return any value it is for other purpose

Mayur Gujrathi · Answer

change code like this but still same error cmd.Parameters.Add(@pa_error SqlDbType.VarChar 800).Direction = ParameterDirection.Output cmd.Parameters(@pa_error).Direction = ParameterDirection.Output but still same error

Ankit Nandekar · Answer

in store procedure in @pa_error what value u r storing and try to return @pa_error means in ur storeprocedure return @pa_error.

Ankit Nandekar · Answer

in store procedure in @pa_error what value u r storing and try to return @pa_error means in ur storeprocedure return @pa_error.

Mayur Gujrathi · Answer

Sorry omkar it is varchar sorry once again

omkar mhaiskar · Answer

ok mayur. as per your last reply variable is nvarchar in stored procedure. so set length =4000 because NVarChar's maximum size is 4000 characters. cmd.Parameters.size=4000 Thanks and Regards Omkar.

Mayur Gujrathi · Answer

ALTER proc [citrus_usr].[pr_ins_upd_reqdtls](@pa_id numeric  @pa_action varchar(50)  @pa_req_slip_no varchar(100)  @pa_req_date datetime  @pa_boid varchar(16)  @pa_boname varchar(150)  @pa_sholder varchar(150)  @pa_tholder varchar(150)  @pa_login_name varchar(100)  @pa_chk_yn smallint  @pa_rmks varchar(200)  @pa_error varchar(800) output ) as begin if @pa_chk_yn = 0 begin if @pa_action ='INS' begin insert into Dis_req_Dtls select @pa_req_slip_no @pa_req_date  @pa_boid  @pa_boname  @pa_sholder  @pa_tholder  getdate()  @pa_login_name  getdate()  @pa_login_name 1 NULL @pa_rmks end if @pa_action ='EDT' begin update Dis_req_Dtls set req_slip_no=@pa_req_slip_no req_date=@pa_req_date Boid=@pa_boid Boname=@pa_boname sholder=@pa_sholder tholder = @pa_tholder remarks = @pa_rmks lst_upd_dt = getdate() lst_upd_by =@pa_login_name where id = @pa_id and deleted_ind = 1 end if @pa_action ='DEL' begin update Dis_req_Dtls set deleted_ind = 0 lst_upd_dt = getdate() lst_upd_by =@pa_login_name where id = @pa_id and deleted_ind = 1 end if @pa_action ='VALIDATESELECT' begin select SLIIM_DPAM_ACCT_NO  dpam_sba_no  isnull(DPHD_SH_FNAME'') + ' ' + isnull(DPHD_SH_MNAME'') +' ' + isnull(DPHD_SH_LNAME'') sh_holder  isnull(DPHD_TH_FNAME'') + ' ' + isnull(DPHD_TH_MNAME'') +' ' + isnull(DPHD_TH_LNAME'') th_holder from slip_issue_mstr  dp_acct_mstr left outer join dp_holder_dtls on dphd_dpam_id = dpam_id where SLIIM_SLIP_NO_TO = replace(@pa_req_slip_no SLIIM_SERIES_TYPE'') and sliim_deleted_ind =1 end if @pa_action ='SEARCH' begin select * from Dis_req_Dtls where req_slip_no = @pa_req_slip_no and deleted_ind =1 end end else if @pa_chk_yn = 1 begin if @pa_action ='INS' begin if not exists(select req_slip_no from Dis_req_Dtls where id = @pa_id) insert into Dis_req_Dtls_mak select @pa_req_slip_no @pa_req_date  @pa_boid  @pa_boname  @pa_sholder  @pa_tholder  getdate()  @pa_login_name  getdate()  @pa_login_name 0  @pa_rmks end if @pa_action ='EDT' begin if not exists(select req_slip_no from Dis_req_Dtls where id = @pa_id and deleted_ind = 1) begin update Dis_req_Dtls_mak set req_slip_no=@pa_req_slip_no req_date=@pa_req_date Boid=@pa_boid Boname=@pa_boname sholder=@pa_sholder tholder = @pa_tholder remarks = @pa_rmks lst_upd_dt = getdate() lst_upd_by =@pa_login_name where id = @pa_id and deleted_ind = 0 END ELSE BEGIN insert into Dis_req_Dtls_mak select @pa_req_slip_no @pa_req_date  @pa_boid  @pa_boname  @pa_sholder  @pa_tholder  getdate()  @pa_login_name  getdate()  @pa_login_name 6 @pa_rmks END end if @pa_action ='DEL' begin if not exists(select req_slip_no from Dis_req_Dtls where id = @pa_id and deleted_ind = 1) BEGIN delete from Dis_req_Dtls_mak where id = @pa_id and deleted_ind = 0 END ELSE BEGIN insert into Dis_req_Dtls_mak select @pa_req_slip_no @pa_req_date  @pa_boid  @pa_boname  @pa_sholder  @pa_tholder  getdate()  @pa_login_name  getdate()  @pa_login_name 4 @pa_rmks END end if @pa_action ='APP' begin insert into Dis_req_Dtls select req_slip_no req_date boid boname sholder tholder created_dt created_by lst_upd_dt lst_upd_by 1 NULL remarks from Dis_req_Dtls_mak where id = @pa_id and deleted_ind = 0 update TBLMASTER set TBLMASTER.req_slip_no=TBLMAK.req_slip_no TBLMASTER.req_date=TBLMAK.req_date TBLMASTER.Boid=TBLMAK.Boid TBLMASTER.Boname=TBLMAK.Boname TBLMASTER.sholder=TBLMAK.sholder TBLMASTER.tholder = TBLMAK.tholder TBLMASTER.lst_upd_dt = getdate() TBLMASTER.lst_upd_by = @pa_login_name from Dis_req_Dtls TBLMASTER Dis_req_Dtls_mak TBLMAK where TBLMASTER.REQ_SLIP_NO = TBLMAK.REQ_SLIP_NO AND TBLMAK.id = @pa_id and TBLMAK.deleted_ind = 6 DELETE TBLMASTER from Dis_req_Dtls TBLMASTER Dis_req_Dtls_mak TBLMAK where TBLMASTER.REQ_SLIP_NO = TBLMAK.REQ_SLIP_NO AND TBLMAK.id = @pa_id and TBLMAK.deleted_ind = 4 end if @pa_action ='VALIDATESELECT' begin select SLIIM_DPAM_ACCT_NO  dpam_sba_no  isnull(DPHD_SH_FNAME'') + ' ' + isnull(DPHD_SH_MNAME'') +' ' + isnull(DPHD_SH_LNAME'') sh_holder  isnull(DPHD_TH_FNAME'') + ' ' + isnull(DPHD_TH_MNAME'') +' ' + isnull(DPHD_TH_LNAME'') th_holder from slip_issue_mstr  dp_acct_mstr left outer join dp_holder_dtls on dphd_dpam_id = dpam_id where SLIIM_SLIP_NO_TO = replace(@pa_req_slip_no SLIIM_SERIES_TYPE'') and sliim_deleted_ind =1 end if @pa_action ='SEARCHCHK' begin IF @pa_tholder = 'P' BEGIN select * from Dis_req_Dtls_MAK where deleted_ind IN (046) AND CREATED_BY @PA_LOGIN_NAME AND req_slip_no LIKE CASE WHEN @pa_req_slip_no = '' THEN '%' ELSE @pa_req_slip_no END AND Boid LIKE CASE WHEN @pa_boid = '' THEN '%' ELSE @pa_boid END AND case when @pa_req_date = '' then '0' else req_date end = case when @pa_req_date = '' then '0' else @pa_req_date end END IF @pa_tholder = 'R' BEGIN select * from Dis_req_Dtls_MAK where deleted_ind = 3 AND req_slip_no LIKE CASE WHEN @pa_req_slip_no = '' THEN '%' ELSE @pa_req_slip_no END AND Boid LIKE CASE WHEN @pa_boid = '' THEN '%' ELSE @pa_boid END AND case when @pa_req_date = '' then '0' else req_date end = case when @pa_req_date = '' then '0' else @pa_req_date end END IF @pa_tholder = 'A' BEGIN select * from Dis_req_Dtls where deleted_ind = 1 AND req_slip_no LIKE CASE WHEN @pa_req_slip_no = '' THEN '%' ELSE @pa_req_slip_no END AND Boid LIKE CASE WHEN @pa_boid = '' THEN '%' ELSE @pa_boid END AND case when @pa_req_date = '' then '0' else req_date end = case when @pa_req_date = '' then '0' else @pa_req_date end END end if @pa_action ='SEARCHMAK' begin select * from Dis_req_Dtls_MAK where deleted_ind IN (046) AND CREATED_BY = @PA_LOGIN_NAME end end end

Mayur Gujrathi · Answer

Ankit still same error i am not getting problem

Ankit Nandekar · Answer

can i see ur storeprocedure

Mayur Gujrathi · Answer

still it is giving error System.Data.SqlClient.SqlException: Procedure or function pr_ins_upd_reqdtls has too many arguments specified.

Ankit Nandekar · Answer

i said try to give size like cmd.Parameters.Add(@pa_error SqlDbType.VarChar200)

Mayur Gujrathi · Answer

it is NVarChar in my stored proc will it work and ankit what you told i did not get please describe

Ankit Nandekar · Answer

put size in this line cmd.Parameters.Add(@pa_error SqlDbType.VarChar)

Mayur Gujrathi · Answer

ok omkar i did as you said but following error is occuring the Size property has an invalid size of 0.

omkar mhaiskar · Answer

Dear Mayur Ok i got your problem make following changes in your code. cmd.Parameters.Add(@pa_error SqlDbType.VarChar) cmd.Parameters(@pa_error).Direction = ParameterDirection.Output other wise @store procedure use text as datatype. and same it is in the codebehind. Thanks And Regards Omkar.