June 25, 2015 at 12:16 pm
ALTER PROCEDURE [dbo].[p_sub_agent_Grp_report]
@parent_pay_agent_cd VARCHAR(25) ,
@tagno NUMERIC(18,0) = NULL,
@labFromCLS VARCHAR(10),
@labToCLS VARCHAR(10),
@status VARCHAR(1)
AS
BEGIN
SET NOCOUNT ON;
Declare @sqlquery AS nvarchar(4000);
Declare @ParmDeclaration AS nvarchar(2000);
SET @sqlquery = 'SELECT a.tran_date,
a.parent_agent_cd,
a.parent_pay_agent_cd,
a.parent_pay_sub_agent_cd,
a.remit_number,
a.payout_amount,
a.pay_crncy_cd,
d.sub_agent_name,
d.sub_agent_address,
a.action_cd
FROM
(
SELECT a.tran_id,
convert(varchar(10), a.tran_date, 121) as Tran_date,
a.parent_agent_cd,
a.remit_number,
a.pay_amount as payout_amount,
a.pay_crncy_cd,
a.parent_pay_agent_cd,a.parent_pay_sub_agent_cd ,
a.action_cd
FROM tran_master a
WHERE a.is_approve = 1 AND a.action_cd not in (9,10)
AND a.parent_pay_agent_cd = '''+@parent_pay_agent_cd+'''
AND EXISTS (SELECT 1 FROM Sub_agent_tag_dtl b WHERE A.parent_pay_sub_agent_cd = b.sub_agent_cd '
IF @tagno IS NOT NULL
SET @sqlquery = @sqlquery + ' AND b.sub_agent_tag_no = '+@tagno
SET @sqlquery = @sqlquery + ' AND b.parent_agent_cd = '''+@parent_pay_agent_cd+''' )
OR a.parent_pay_sub_agent_cd IN ( NULL ,'''')
) a INNER JOIN tran_action_hist C
ON ( a.parent_agent_cd = c.parent_agent_cd AND a.action_cd=c.action_cd
AND a.tran_date = c.tran_date AND a.tran_id=c.tran_id
)
INNER JOIN sub_agent d
ON( d.parent_agent_cd = a.parent_pay_agent_cd
AND d.sub_agent_cd = a.parent_pay_sub_agent_cd) '
IF @status = 'Y'
SET @sqlquery = @sqlquery + ' WHERE a.action_cd = 2
AND convert(varchar(10), c.action_date, 121) BETWEEN '''+@labFromCLS+''' AND '''+@labToCLS+''''
ELSE
SET @sqlquery = @sqlquery + ' WHERE a.action_cd <> 2
AND a.tran_date BETWEEN '''+@labFromCLS+''' AND '''+@labToCLS+''''
SET @ParmDeclaration = '
@parent_pay_agent_cd VARCHAR(25) ,
@tagno int,
@labFromCLS VARCHAR(10),
@labToCLS VARCHAR(10),
@status VARCHAR(1)'
print @sqlquery
Execute sp_Executesql @sqlquery, @ParmDeclaration, @parent_pay_agent_cd, @tagno, @labFromCLS ,@labToCLS , @status
RETURN (0)
END;
-----------------
----------------
1. EXEC [dbo].[p_sub_agent_Grp_report] 'BD0003' , 173, '2015-06-01' , '2015-06-25', 'Y'
2. EXEC [dbo].[p_sub_agent_Grp_report] 'BD0003' , NULL, '2015-06-01' , '2015-06-25', 'Y'
2nd Stored procedure executes successfully and 1st stored does not executes where only difference is 3rd parameter value 173 and NULL value.
Is it because " IF @tagno IS NOT NULL" used in stored procedure is not working while @tagno is sent as 173 in sp parameter. Error throwing while executing 1st stored procedure is as below.
Msg 8115, Level 16, State 6, Procedure p_sub_agent_Grp_report, Line 48
Arithmetic overflow error converting nvarchar to data type numeric.
Msg 8115, Level 16, State 6, Procedure p_sub_agent_Grp_report, Line 48
Arithmetic overflow error converting nvarchar to data type numeric.
Note: sub_agent_tag_no column of table Sub_agent_tag_dtl has datatype Numeric(18,0).
Could you please suggest me the solution?
June 25, 2015 at 12:28 pm
keshab.basnet (6/25/2015)
ALTER PROCEDURE [dbo].[p_sub_agent_Grp_report]@parent_pay_agent_cd VARCHAR(25) ,
@tagno NUMERIC(18,0) = NULL,
@labFromCLS VARCHAR(10),
@labToCLS VARCHAR(10),
@status VARCHAR(1)
AS
BEGIN
SET NOCOUNT ON;
Declare @sqlquery AS nvarchar(4000);
Declare @ParmDeclaration AS nvarchar(2000);
SET @sqlquery = 'SELECT a.tran_date,
a.parent_agent_cd,
a.parent_pay_agent_cd,
a.parent_pay_sub_agent_cd,
a.remit_number,
a.payout_amount,
a.pay_crncy_cd,
d.sub_agent_name,
d.sub_agent_address,
a.action_cd
FROM
(
SELECT a.tran_id,
convert(varchar(10), a.tran_date, 121) as Tran_date,
a.parent_agent_cd,
a.remit_number,
a.pay_amount as payout_amount,
a.pay_crncy_cd,
a.parent_pay_agent_cd,a.parent_pay_sub_agent_cd ,
a.action_cd
FROM tran_master a
WHERE a.is_approve = 1 AND a.action_cd not in (9,10)
AND a.parent_pay_agent_cd = '''+@parent_pay_agent_cd+'''
AND EXISTS (SELECT 1 FROM Sub_agent_tag_dtl b WHERE A.parent_pay_sub_agent_cd = b.sub_agent_cd '
IF @tagno IS NOT NULL
SET @sqlquery = @sqlquery + ' AND b.sub_agent_tag_no = '+@tagno
SET @sqlquery = @sqlquery + ' AND b.parent_agent_cd = '''+@parent_pay_agent_cd+''' )
OR a.parent_pay_sub_agent_cd IN ( NULL ,'''')
) a INNER JOIN tran_action_hist C
ON ( a.parent_agent_cd = c.parent_agent_cd AND a.action_cd=c.action_cd
AND a.tran_date = c.tran_date AND a.tran_id=c.tran_id
)
INNER JOIN sub_agent d
ON( d.parent_agent_cd = a.parent_pay_agent_cd
AND d.sub_agent_cd = a.parent_pay_sub_agent_cd) '
IF @status = 'Y'
SET @sqlquery = @sqlquery + ' WHERE a.action_cd = 2
AND convert(varchar(10), c.action_date, 121) BETWEEN '''+@labFromCLS+''' AND '''+@labToCLS+''''
ELSE
SET @sqlquery = @sqlquery + ' WHERE a.action_cd <> 2
AND a.tran_date BETWEEN '''+@labFromCLS+''' AND '''+@labToCLS+''''
SET @ParmDeclaration = '
@parent_pay_agent_cd VARCHAR(25) ,
@tagno int,
@labFromCLS VARCHAR(10),
@labToCLS VARCHAR(10),
@status VARCHAR(1)'
print @sqlquery
Execute sp_Executesql @sqlquery, @ParmDeclaration, @parent_pay_agent_cd, @tagno, @labFromCLS ,@labToCLS , @status
RETURN (0)
END;
-----------------
----------------
1. EXEC [dbo].[p_sub_agent_Grp_report] 'BD0003' , 173, '2015-06-01' , '2015-06-25', 'Y'
2. EXEC [dbo].[p_sub_agent_Grp_report] 'BD0003' , NULL, '2015-06-01' , '2015-06-25', 'Y'
2nd Stored procedure executes successfully and 1st stored does not executes where only difference is 3rd parameter value 173 and NULL value.
Is it because " IF @tagno IS NOT NULL" used in stored procedure is not working while @tagno is sent as 173 in sp parameter. Error throwing while executing 1st stored procedure is as below.
Msg 8115, Level 16, State 6, Procedure p_sub_agent_Grp_report, Line 48
Arithmetic overflow error converting nvarchar to data type numeric.
Msg 8115, Level 16, State 6, Procedure p_sub_agent_Grp_report, Line 48
Arithmetic overflow error converting nvarchar to data type numeric.
Note: sub_agent_tag_no column of table Sub_agent_tag_dtl has datatype Numeric(18,0).
Could you please suggest me the solution?
I think the error is coming from:
IF @tagno IS NOT NULL
SET @sqlquery = @sqlquery + ' AND b.sub_agent_tag_no = '+@tagno
You're trying to concatenate @tagno which is numeric with the string that your are building.
Try replacing the above text with:
IF @tagno IS NOT NULL
SET @sqlquery = @sqlquery + ' AND b.sub_agent_tag_no = '+ CAST(@tagno AS NVARCHAR(18))
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
June 25, 2015 at 11:51 pm
Thanks Sir. That really helped.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply