raise error issue - 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

raise error issue

Author

Topic

pnpsql
Posting Yak Master

246 Posts

Posted - 2012-02-28 : 23:27:12

hi team i need to use raise error in my code that is given below , but
when i executed, this is not working , no error msg appears. but when i debug the same the msg appears
please help..

[CODE]
DECLARE @DRAMT AS NUMERIC (10),
@CRAMT AS NUMERIC (10),
@V_ID AS NUMERIC (10),
@V_CODE AS VARCHAR (10),
@V_STATUS AS VARCHAR (10),
@V_ERRORCODE AS VARCHAR (10),
@V_ERRORMSG AS VARCHAR (200),
@CNTTRANS AS NUMERIC (4),
@CNTAUTH AS NUMERIC (4);
BEGIN

DECLARE @V_VOUCHER_TYPE TABLE (
VOUCHERTYPEID NUMERIC (10));

SELECT @CRAMT = Isnull(Sum(AMT), 0)
FROM ABC
WHERE TYPE = 'CR'
AND STATUS = 'P';

SELECT @DRAMT = Isnull(Sum(AMT), 0)
FROM ABC
WHERE TYPE = 'DR'
AND STATUS = 'P';

SELECT @CNTTRANS = Count(1)
FROM ABC
WHERE ACCOUNTING_ID IS NOT NULL;

SELECT @CNTAUTH = Count(*)
FROM ABC
WHERE STATUS = 'P';

IF @CNTAUTH <> @CNTTRANS
BEGIN
DELETE ABC
WHERE TRANSACTION_ID IS NULL;

RAISERROR ('ONLY COMPLETE TRANSACTION CAN BE SENT TO AUTHOR FOR APPROVAL ',
16,
3);
END;

IF @CRAMT <> @DRAMT
BEGIN
DELETE ABC
WHERE TRANSACTION_ID IS NULL;

RAISERROR ('MISMATCH IN DEBIT AND CREDIT',
16,
3);
END

END

[CODE]

challenge everything

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-29 : 10:07:58

Code itself seems fine to me. If you insert print statements just before the IF blocks to print out the values of @CNTAUTH, @CNTTRANS, @CRAMT and @DRAMT, that may give some clue.

Shubhanshu
Starting Member

8 Posts

Posted - 2012-03-01 : 14:13:44

You can use Select ,to show ur messages

[CODE]
DECLARE @DRAMT AS NUMERIC (10),
@CRAMT AS NUMERIC (10),
@V_ID AS NUMERIC (10),
@V_CODE AS VARCHAR (10),
@V_STATUS AS VARCHAR (10),
@V_ERRORCODE AS VARCHAR (10),
@V_ERRORMSG AS VARCHAR (200),
@CNTTRANS AS NUMERIC (4),
@CNTAUTH AS NUMERIC (4);
BEGIN

DECLARE @V_VOUCHER_TYPE TABLE (
VOUCHERTYPEID NUMERIC (10));

SELECT @CRAMT = Isnull(Sum(AMT), 0)
FROM ABC
WHERE TYPE = 'CR'
AND STATUS = 'P';

SELECT @DRAMT = Isnull(Sum(AMT), 0)
FROM ABC
WHERE TYPE = 'DR'
AND STATUS = 'P';

SELECT @CNTTRANS = Count(1)
FROM ABC
WHERE ACCOUNTING_ID IS NOT NULL;

SELECT @CNTAUTH = Count(*)
FROM ABC
WHERE STATUS = 'P';

IF @CNTAUTH <> @CNTTRANS
BEGIN
DELETE ABC
WHERE TRANSACTION_ID IS NULL;

Select ('ONLY COMPLETE TRANSACTION CAN BE SENT TO AUTHOR FOR APPROVAL ',
16,
3) AS Message;
END;

IF @CRAMT <> @DRAMT
BEGIN
DELETE ABC
WHERE TRANSACTION_ID IS NULL;

SELECT('MISMATCH IN DEBIT AND CREDIT',
16,
3)AS Message;
END

END

[CODE]

challenge everything
[/quote]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources