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.
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 mepls.... |
|
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 implementAlso 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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 codingthanks visakh murukes sir, |
|
|
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 codingthanks visakh murukes sir,
Show us what you've tried so far. Otherwise expect no help from here!------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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 |
|
|
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-sqlthis is ms sql server forum and we deal with t-sqlso if you're using different rdbms please post in relevant forums------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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, |
|
|
|
|
|
|
|