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
 storeprocedure code

Author  Topic 

rajnidas
Yak Posting Veteran

97 Posts

Posted - 2013-10-30 : 03:16:34
hi everyone, i am new in sql programming ,i need help


I have one query about storeprocedure, i want to create 1) cheque amount 2)cheque date 3)cheque no,
and validation
using with cursor and checking true or false ,if value is giving wrong display error it will store error code and try catch also using with storeprocedure.

give some sample coding which will helpful for me

pls....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-30 : 03:23:25
Whats the point in posting an open question like this?
Are you expected someone to create tables, etc and also query for you?
Atleast have the basic courtesy of giving some sample data from your tables and then explain what logic you want to implement
Also why do you need cursor? That statement gives us a feeling this is assignment question copied. So unless we see a genuine attemt from your end, we're not going to help you on this.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

rajnidas
Yak Posting Veteran

97 Posts

Posted - 2013-10-30 : 03:33:57

i need storeprocedure coding to create,
cheque amount 2)cheque date 3)cheque no,
and validate cheque amount true or false using cursor code and if amount false using error coding also need coding

thanks visakh murukes sir,
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-30 : 03:42:20
quote:
Originally posted by rajnidas


i need storeprocedure coding to create,
cheque amount 2)cheque date 3)cheque no,
and validate cheque amount true or false using cursor code and if amount false using error coding also need coding

thanks visakh murukes sir,


Show us what you've tried so far.
Otherwise expect no help from here!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

rajnidas
Yak Posting Veteran

97 Posts

Posted - 2013-10-30 : 03:52:43
need store procedure code
for cheque amount 2)cheque date 3)cheque no, and when .net frontend puting value of amount checkout true or false in backend and stored in backend and need condition code sample oracle code given below and i want create own storeprocedure in sql. pls ..rajnidas from tamilnad


--------------------------------------------------------
-- File created - Tuesday-July-09-2013
--------------------------------------------------------
--------------------------------------------------------
-- DDL for Procedure USP_DT_AMD_DIS_TRK_TEMP_VALIDT
--------------------------------------------------------
set define off;

create PROC USP_Finance_Chq_Validation_Temp
as

begin

DECLARE @v_finid AS INT
declare @v_no as int
declare @v_amount as int
declare @v_date as int
declare @v_totalrows as int


v_totalrows count int,

v_finid count int,
v_amount count int,
v_date count int,

NO_COUNT INT,


i NUMBER := 1;
ERR_ROW NUMBER :=0;
V_ERROR_CODE NUMBER :=1;
BEGIN

SELECT COUNT(1) INTO V_TOTAL_ROWS FROM DT_DISPATCH_TRACKER_TEMP;
DELETE DT_DISPATCH_TRACKER_TEMP_VALID;

LOOP
--WHILE i <= V_TOTAL_ROWS LOOP


--SELECT @OrderItemID = OrderItemID
--FROM [OrderItem]
--WHERE OrderID = @OrderID

SELECT DOC_REF_NO,DISPATCH_DATE,"DOCUMENT" INTO V_DOC_REF_NO,V_DISPATCH_DATE,V_DOCUMENT
FROM (SELECT ROWNUM AS ROW_INDEX,DOC_REF_NO,DISPATCH_DATE,"DOCUMENT" FROM DT_DISPATCH_TRACKER_TEMP)TEMP
WHERE TEMP.ROW_INDEX = i;


SELECT COUNT(1) INTO V_DOC_COUNT
FROM DT_DISPATCH_TRACKER_TEMP
WHERE DOCUMENT NOT IN (SELECT PARAM_VALUE FROM PARAMETER WHERE DESCRIPTION = 'DT_DOCUMENT')
AND DOC_REF_NO = V_DOC_REF_NO
AND DISPATCH_DATE = V_DISPATCH_DATE
AND "DOCUMENT" = V_DOCUMENT;


IF(V_DOC_COUNT > 0) THEN
SELECT '0' INTO V_DOC
FROM DT_DISPATCH_TRACKER_TEMP
WHERE DOCUMENT NOT IN (SELECT PARAM_VALUE FROM PARAMETER WHERE DESCRIPTION = 'DT_DOCUMENT')
AND DOC_REF_NO = V_DOC_REF_NO
AND DISPATCH_DATE = V_DISPATCH_DATE
AND "DOCUMENT" = V_DOCUMENT;
END IF;


SELECT COUNT(1) INTO V_DOC_TYP_COUNT
FROM DT_DISPATCH_TRACKER_TEMP
WHERE DOCUMENT_TYPE NOT IN (SELECT PARAM_VALUE FROM PARAMETER WHERE DESCRIPTION = 'DT_DOCUMENT_TYPE')
AND DOC_REF_NO = V_DOC_REF_NO
AND DISPATCH_DATE = V_DISPATCH_DATE
AND "DOCUMENT" = V_DOCUMENT;

IF(V_DOC_TYP_COUNT > 0) THEN
SELECT '0' INTO V_DOC_TYP
FROM DT_DISPATCH_TRACKER_TEMP
WHERE DOCUMENT_TYPE NOT IN (SELECT PARAM_VALUE FROM PARAMETER WHERE DESCRIPTION = 'DT_DOCUMENT_TYPE')
AND DOC_REF_NO = V_DOC_REF_NO
AND DISPATCH_DATE = V_DISPATCH_DATE
AND "DOCUMENT" = V_DOCUMENT;
END IF;


SELECT COUNT(1) INTO V_AWB_COUNT
FROM DT_DISPATCH_TRACKER_TEMP
WHERE AWB_STATUS NOT IN (SELECT PARAM_VALUE FROM PARAMETER WHERE DESCRIPTION = 'DT_STATUS')
AND DOC_REF_NO = V_DOC_REF_NO
AND DISPATCH_DATE = V_DISPATCH_DATE
AND "DOCUMENT" = V_DOCUMENT;

IF( V_AWB_COUNT > 0) THEN
SELECT '0' INTO V_AWB
FROM DT_DISPATCH_TRACKER_TEMP
WHERE AWB_STATUS NOT IN (SELECT PARAM_VALUE FROM PARAMETER WHERE DESCRIPTION = 'DT_STATUS')
AND DOC_REF_NO = V_DOC_REF_NO
AND DISPATCH_DATE = V_DISPATCH_DATE
AND "DOCUMENT" = V_DOCUMENT;
END IF;


SELECT COUNT(1) INTO V_DIS_TO_COUNT
FROM DT_DISPATCH_TRACKER_TEMP
WHERE DISPATCH_TO NOT IN (SELECT PARAM_VALUE FROM PARAMETER WHERE DESCRIPTION = 'DT_TO_FROM')
AND DOC_REF_NO = V_DOC_REF_NO
AND DISPATCH_DATE = V_DISPATCH_DATE
AND "DOCUMENT" = V_DOCUMENT;

IF(V_DIS_TO_COUNT > 0) THEN
SELECT '0' INTO V_DIS_TO
FROM DT_DISPATCH_TRACKER_TEMP
WHERE DISPATCH_TO NOT IN (SELECT PARAM_VALUE FROM PARAMETER WHERE DESCRIPTION = 'DT_TO_FROM')
AND DOC_REF_NO = V_DOC_REF_NO
AND DISPATCH_DATE = V_DISPATCH_DATE
AND "DOCUMENT" = V_DOCUMENT;
END IF;


SELECT COUNT(1) INTO V_DIS_FROM_COUNT
FROM DT_DISPATCH_TRACKER_TEMP
WHERE TRIM(DISPATCH_FROM) NOT IN (SELECT TRIM(PARAM_VALUE) FROM PARAMETER WHERE DESCRIPTION = 'DT_TO_FROM')
AND DOC_REF_NO = V_DOC_REF_NO
AND DISPATCH_DATE = V_DISPATCH_DATE
AND "DOCUMENT" = V_DOCUMENT;

IF(V_DIS_FROM_COUNT > 0) THEN
SELECT '0' INTO V_DIS_FROM
FROM DT_DISPATCH_TRACKER_TEMP
WHERE TRIM(DISPATCH_FROM) NOT IN (SELECT TRIM(PARAM_VALUE) FROM PARAMETER WHERE DESCRIPTION = 'DT_TO_FROM')
AND DOC_REF_NO = V_DOC_REF_NO
AND DISPATCH_DATE = V_DISPATCH_DATE
AND "DOCUMENT" = V_DOCUMENT;
END IF;

/* SELECT COUNT(1) INTO V_DELIVER_DT_COUNT
FROM DT_DISPATCH_TRACKER_TEMP
WHERE TO_CHAR(DELIVERY_DATE, 'dd-MM-yyyy') = '01-01-1900'
AND DOC_REF_NO = V_DOC_REF_NO
AND DISPATCH_DATE = V_DISPATCH_DATE
AND "DOCUMENT" = V_DOCUMENT;

IF(V_DELIVER_DT_COUNT > 0) THEN
SELECT '0' INTO V_DELIVER_DT
FROM DT_DISPATCH_TRACKER_TEMP
WHERE TO_CHAR(DELIVERY_DATE, 'dd-MM-yyyy') = '01-01-1900'
AND DOC_REF_NO = V_DOC_REF_NO
AND DISPATCH_DATE = V_DISPATCH_DATE
AND "DOCUMENT" = V_DOCUMENT;
END IF; */

/*SELECT COUNT(1) INTO V_EXTRACT_DT_COUNT
FROM DT_DISPATCH_TRACKER_TEMP
WHERE TO_CHAR(EXTRACT_DATE, 'dd-MM-yyyy') = '01-01-1900'
AND DOC_REF_NO = V_DOC_REF_NO
AND DISPATCH_DATE = V_DISPATCH_DATE
AND "DOCUMENT" = V_DOCUMENT;

IF(V_EXTRACT_DT_COUNT > 0) THEN
SELECT '0' INTO V_EXTRACT_DT
FROM DT_DISPATCH_TRACKER_TEMP
WHERE TO_CHAR(EXTRACT_DATE, 'dd-MM-yyyy') = '01-01-1900'
AND DOC_REF_NO = V_DOC_REF_NO
AND DISPATCH_DATE = V_DISPATCH_DATE
AND "DOCUMENT" = V_DOCUMENT;
END IF; */

SELECT COUNT(1) INTO V_AWB_NO_COUNT
FROM DT_DISPATCH_TRACKER_TEMP
WHERE AWB_NO ='-'
AND DOC_REF_NO = V_DOC_REF_NO
AND DISPATCH_DATE = V_DISPATCH_DATE
AND "DOCUMENT" = V_DOCUMENT;

IF( V_AWB_NO_COUNT > 0) THEN
SELECT '0' INTO V_AWB_NO
FROM DT_DISPATCH_TRACKER_TEMP
WHERE AWB_NO ='-'
AND DOC_REF_NO = V_DOC_REF_NO
AND DISPATCH_DATE = V_DISPATCH_DATE
AND "DOCUMENT" = V_DOCUMENT;
END IF;

INSERT INTO DT_DISPATCH_TRACKER_TEMP_VALID
(
DOC_REF_NO,
DISPATCH_DATE,
DOCUMENT,
DOCUMENT_TYPE,
--COURIER_NAME,
AWB_NO,
AWB_STATUS,
DISPATCH_FROM,
DISPATCH_TO,
--DELIVERY_DATE,
EXTRACT_DATE
)
VALUES
(
V_DOC_REF_NO,
V_DISPATCH_DATE,
V_DOC,
V_DOC_TYP,
V_AWB_NO,
V_AWB,
V_DIS_FROM,
V_DIS_TO,
--V_DELIVER_DT,
V_EXTRACT_DT
);
COMMIT;
V_DOC :=1;
V_DOC_TYP :=1;
V_AWB_NO :=1;
V_AWB :=1;
V_DIS_TO :=1;
V_DIS_FROM := 1;
--V_DELIVER_DT :=1;
V_EXTRACT_DT :=1;


BEGIN
SELECT 0 AS ERROR_CODE INTO V_ERROR_CODE
FROM DT_DISPATCH_TRACKER_TEMP_VALID DT_VALID
WHERE DT_VALID.DOC_REF_NO = V_DOC_REF_NO
AND DT_VALID.DISPATCH_DATE = V_DISPATCH_DATE
AND (
DT_VALID."DOCUMENT" = 0
OR DT_VALID.DOCUMENT_TYPE = 0
OR DT_VALID.COURIER_NAME = 0
OR DT_VALID.AWB_NO = 0
OR DT_VALID.AWB_STATUS = 0
OR DT_VALID.DISPATCH_FROM = 0
OR DT_VALID.DISPATCH_TO = 0
OR DT_VALID.DELIVERY_DATE = 0
OR DT_VALID.EXTRACT_DATE = 0
);
EXCEPTION WHEN NO_DATA_FOUND THEN
V_ERROR_CODE :=1;
END;

UPDATE DT_DISPATCH_TRACKER_TEMP DT_TEMP
SET "ERROR" = V_ERROR_CODE
WHERE DT_TEMP.DOC_REF_NO = V_DOC_REF_NO
AND DISPATCH_DATE = V_DISPATCH_DATE;

--V_TOTAL_ROWS := V_TOTAL_ROWS - i;
i := i + 1;
V_ERROR_CODE :=1;
EXIT WHEN i > V_TOTAL_ROWS;
END LOOP;
OPEN CUR_TEMP_VALID_RESULT FOR
SELECT DOC_REF_NO,
DISPATCH_DATE,
"DOCUMENT",
DOCUMENT_TYPE,
CLIENT_NAME,
COURIER_NAME,
AWB_NO,
AWB_STATUS,
DISPATCH_FROM,
DISPATCH_TO,
DELIVERY_DATE,
RECEIVED_BY,
RTO_REASON,
REMARKS,
EXTRACT_DATE
FROM DT_DISPATCH_TRACKER_TEMP_VALID
ORDER BY DOC_REF_NO;

/*EXCEPTION WHEN NO_DATA_FOUND THEN
GOTO loopstart;*/
/*SELECT COUNT(1) INTO V_DOC_COUNT
FROM DT_DISPATCH_TRACKER_TEMP
WHERE DOCUMENT NOT IN (SELECT PARAM_VALUE FROM PARAMETER WHERE DESCRIPTION = 'DT_DOCUMENT');

SELECT COUNT(1) INTO V_DOC_TYP_COUNT
FROM DT_DISPATCH_TRACKER_TEMP
WHERE DOCUMENT_TYPE NOT IN (SELECT PARAM_VALUE FROM PARAMETER WHERE DESCRIPTION = 'DT_DOCUMENT_TYPE');

SELECT COUNT(1) INTO V_AWB_COUNT
FROM DT_DISPATCH_TRACKER_TEMP
WHERE AWB_STATUS NOT IN (SELECT PARAM_VALUE FROM PARAMETER WHERE DESCRIPTION = 'DT_STATUS');

SELECT COUNT(1) INTO V_DIS_TO_COUNT
FROM DT_DISPATCH_TRACKER_TEMP
WHERE DISPATCH_TO NOT IN (SELECT PARAM_VALUE FROM PARAMETER WHERE DESCRIPTION = 'DT_TO_FROM');

SELECT COUNT(1) INTO V_DIS_FROM_COUNT
FROM DT_DISPATCH_TRACKER_TEMP
WHERE DISPATCH_FROM NOT IN (SELECT PARAM_VALUE FROM PARAMETER WHERE DESCRIPTION = 'DT_TO_FROM');

IF(V_DOC_COUNT > 0) THEN
INSERT INTO DT_DISPATCH_TRACKER_TEMP_VALID (DOC_REF_NO,DISPATCH_DATE,DOCUMENT)
SELECT DOC_REF_NO,DISPATCH_DATE,'0' AS DOCUMENT
FROM DT_DISPATCH_TRACKER_TEMP
WHERE DOCUMENT NOT IN (SELECT PARAM_VALUE FROM PARAMETER WHERE DESCRIPTION = 'DT_DOCUMENT');
--WHERE DOCUMENT <> 0 ;

/* SELECT DOC_REF_NO,AWB_STATUS FROM DT_DISPATCH_TRACKER_TEMP
WHERE AWB_STATUS NOT IN (SELECT PARAM_VALUE FROM PARAMETER WHERE DESCRIPTION = 'DT_STATUS');

SELECT DOC_REF_NO,DISPATCH_TO FROM DT_DISPATCH_TRACKER_TEMP
WHERE DISPATCH_TO NOT IN (SELECT PARAM_VALUE FROM PARAMETER WHERE DESCRIPTION = 'DT_TO_FROM');

SELECT DOC_REF_NO,DISPATCH_FROM FROM DT_DISPATCH_TRACKER_TEMP
WHERE DISPATCH_FROM NOT IN (SELECT PARAM_VALUE FROM PARAMETER WHERE DESCRIPTION = 'DT_TO_FROM');


SELECT DOC_REF_NO,DOCUMENT_TYPE FROM DT_DISPATCH_TRACKER_TEMP
WHERE DOCUMENT_TYPE NOT IN (SELECT PARAM_VALUE FROM PARAMETER WHERE DESCRIPTION = 'DT_DOCUMENT_TYPE')*/
-- END IF;


END USP_DT_AMD_DIS_TRK_TEMP_VALIDT;

/



-----------xxxxxxxxxxxxxxxxxxxxxxx--------------------------------------xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx---------------------------xxxxxxxxxxxxxxxxxxxxxxxx





Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-30 : 04:14:45
are you using SQLServer? you code has syntax different from t-sql
this is ms sql server forum and we deal with t-sql
so if you're using different rdbms please post in relevant forums

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

rajnidas
Yak Posting Veteran

97 Posts

Posted - 2013-10-30 : 04:24:33
the oracle guy given sample coding for validation but i want sql coding we are using sql server 2012.


The project manager told me,

when the value put on front end first checkout value string or float if float is true and string value false. and write query in storeprocedure for validation for 3 column, as cheque amount 2)cheque date 3)cheque no,
Go to Top of Page
   

- Advertisement -