PREPARE XML DOCUMENT
While handling using bulk inserts / updates ( using XMl in Stored Procedures )
care must be taken to handle to below characters.
1. &
2. <
3. >
4. '
5. "
XMl cannot process these symbols.
We need to pass them as html codes so that they get inserted properly.
1. &
2. <
3. >
4. "
5. '
As shown below.
strRemarks = Textbox.Replace("&", "&")
strRemarks = strRemarks.Replace("<", "<")
strRemarks = strRemarks.Replace(">", ">")
strRemarks = strRemarks.Replace("""", """)
strRemarks = strRemarks.Replace("'""'", """)
strRemarks = strRemarks.Replace("'", "'")
Thursday, February 19, 2009
Monday, January 12, 2009
My Learnings...
The deadlines must not be streched.
UNIT TESTING IS VERY IMPORTANT..
When some resource tells, i've completed the module, they must show unit testing as well as functional...
Provide complete information for the resources. Regarding their logic..
Make Sure they are not hard coding any things in the Application..
Before starting coding, do a brain storm session and ensure what reusable code and design can be help us out...
DATE FORMATS ARE Always a headache.
Use Convert.Datetime instead of Date.parse...
ASP TEXTBOXES made as Readonly fields wont return any value to the server, So use always HTML input boxes for readonly (Calendar)
Ensure that WBS is updated if necessary before start of coding.
ALWAYS HAVE A PROPER TIME ESTIMATE..
FROM THE NEXT PROJECT, JUST RECORD THE TIME REQUIRED TO COMPLETE A USP/ FORM OR WHATEVER, SO THAT WE CAN USE THAT INFORMATION FOR FURTHER TIME ESTIMTATION.
ASK FOR THE TEAM'S LEAVE PLAN...
ALWAYS BE PREPARED FOR ANY KINDA RISKS..
production issues.. needs to be addresses...
first ensure the design is proper in the stage of designing only.. Once done, we must not tinker with it always...
---------------------------------------------
My Observations and learning’s while working on MER
Requirement Gathering
I. Don’t Assume Any thing, get information properly
II. If we are gathering requirements for a particular page, then make sure all fields are covered, don’t miss even a single field.
III. MOMz are a must. Also specify as much details as possible in the documents so that in future it wont affect the functionality
IV. In SDD, Database level details must be specified very clearly.
V. If there are some conditional constraints that are very decisive, then it must be clearly specified.
VI. If there is any interaction with a third party system or a different system, then make sure, an exactly similar environment is available at the development end. Or else, while integrating both, problems are meant to rise and time also won’t be on our side.
VII. Once the SRS and SDD are done, just go through the same and do as it says. Don’t assume or remember things and do. SRS / SDD must be the source for coding. Just Open it and type the code.
VIII. It’s a very bad practice to start development without the SRS and SDD being signed off or else except more headaches in the later phases of the project where in the user may demand for requirements which you have not even heard of.
IX. If something can be done technically in more than one way, Keep all the options in front of them and let them come back with the option feasible for them.
a. For Example, Consider Client Side Validations, This can be done in the following ways
i. Javascript (Which we follow religiously)
ii. VB Script
iii. Field Validators.
So, instead of suffering last minute changes, we can avoid this at the requirement gathering stage itself.
Database Design
I. Do not have unnecessary table and Stored Procedures, which will just confuse us needlessly.
II. Make sure for a particular field A, the data type and the size is correctly defined.
III. Follow one Case, Either all BIG CAPS or all small caps. NeVeR BOTH
a. The above statement refers to the naming conventions of tables / views / stored procedures as well as Script statements
IV. If there is interaction with another database, just make sure views are created to access the same instead of directly querying onto it.
V. Creating VIEWS is very much important and it will reduce a lot of statement in the Where clause.
VI. If we are inserting / updating to more than one table in a Stored Procedure, Sql Transaction statements are a MUST.
VII. Also, most importantly try to follow the EXACT Coding Standards specified in the SDD.
Coding
I. Also, most importantly try to follow the EXACT Coding Standards specified in the SDD.
II. If there is some functionality which is common across the entire layer / Application, then use a common class file; write that piece of code here.
a. If there are changes, then it can be easily dealt with.
b. For example, if there is an ACTIVITYLOG functionality to be implemented, then
III. Add one line of comment for every method and if there is some complex code written, a comment is always necessary there. All comments must be short and live up to the complexity of the code.
IV. Make sure the pages cannot be directly accessed. Use Session Variables to ensure Direct Access to the application will not happen.
V. All datasets / Sessions or other disposable resources must be disposed where ever applicable.
VI. Try to user Data Reader where ever applicable, since the performance of the application will take a huge hike.
Testing
I. Try to see the application as a USER instead of a developer. There can be a chance wherein we can think of some changes that can help the user, do his work more effectively
II. Give the code to someone very new to the application and so
Implementation
VII. G
UAT
I. G
II. Give more importance to Design as well, CSS and alignment details.
III. Try
IV.
---------------------------------------------
UNIT TESTING IS VERY IMPORTANT..
When some resource tells, i've completed the module, they must show unit testing as well as functional...
Provide complete information for the resources. Regarding their logic..
Make Sure they are not hard coding any things in the Application..
Before starting coding, do a brain storm session and ensure what reusable code and design can be help us out...
DATE FORMATS ARE Always a headache.
Use Convert.Datetime instead of Date.parse...
ASP TEXTBOXES made as Readonly fields wont return any value to the server, So use always HTML input boxes for readonly (Calendar)
Ensure that WBS is updated if necessary before start of coding.
ALWAYS HAVE A PROPER TIME ESTIMATE..
FROM THE NEXT PROJECT, JUST RECORD THE TIME REQUIRED TO COMPLETE A USP/ FORM OR WHATEVER, SO THAT WE CAN USE THAT INFORMATION FOR FURTHER TIME ESTIMTATION.
ASK FOR THE TEAM'S LEAVE PLAN...
ALWAYS BE PREPARED FOR ANY KINDA RISKS..
production issues.. needs to be addresses...
first ensure the design is proper in the stage of designing only.. Once done, we must not tinker with it always...
---------------------------------------------
My Observations and learning’s while working on MER
Requirement Gathering
I. Don’t Assume Any thing, get information properly
II. If we are gathering requirements for a particular page, then make sure all fields are covered, don’t miss even a single field.
III. MOMz are a must. Also specify as much details as possible in the documents so that in future it wont affect the functionality
IV. In SDD, Database level details must be specified very clearly.
V. If there are some conditional constraints that are very decisive, then it must be clearly specified.
VI. If there is any interaction with a third party system or a different system, then make sure, an exactly similar environment is available at the development end. Or else, while integrating both, problems are meant to rise and time also won’t be on our side.
VII. Once the SRS and SDD are done, just go through the same and do as it says. Don’t assume or remember things and do. SRS / SDD must be the source for coding. Just Open it and type the code.
VIII. It’s a very bad practice to start development without the SRS and SDD being signed off or else except more headaches in the later phases of the project where in the user may demand for requirements which you have not even heard of.
IX. If something can be done technically in more than one way, Keep all the options in front of them and let them come back with the option feasible for them.
a. For Example, Consider Client Side Validations, This can be done in the following ways
i. Javascript (Which we follow religiously)
ii. VB Script
iii. Field Validators.
So, instead of suffering last minute changes, we can avoid this at the requirement gathering stage itself.
Database Design
I. Do not have unnecessary table and Stored Procedures, which will just confuse us needlessly.
II. Make sure for a particular field A, the data type and the size is correctly defined.
III. Follow one Case, Either all BIG CAPS or all small caps. NeVeR BOTH
a. The above statement refers to the naming conventions of tables / views / stored procedures as well as Script statements
IV. If there is interaction with another database, just make sure views are created to access the same instead of directly querying onto it.
V. Creating VIEWS is very much important and it will reduce a lot of statement in the Where clause.
VI. If we are inserting / updating to more than one table in a Stored Procedure, Sql Transaction statements are a MUST.
VII. Also, most importantly try to follow the EXACT Coding Standards specified in the SDD.
Coding
I. Also, most importantly try to follow the EXACT Coding Standards specified in the SDD.
II. If there is some functionality which is common across the entire layer / Application, then use a common class file; write that piece of code here.
a. If there are changes, then it can be easily dealt with.
b. For example, if there is an ACTIVITYLOG functionality to be implemented, then
III. Add one line of comment for every method and if there is some complex code written, a comment is always necessary there. All comments must be short and live up to the complexity of the code.
IV. Make sure the pages cannot be directly accessed. Use Session Variables to ensure Direct Access to the application will not happen.
V. All datasets / Sessions or other disposable resources must be disposed where ever applicable.
VI. Try to user Data Reader where ever applicable, since the performance of the application will take a huge hike.
Testing
I. Try to see the application as a USER instead of a developer. There can be a chance wherein we can think of some changes that can help the user, do his work more effectively
II. Give the code to someone very new to the application and so
Implementation
VII. G
UAT
I. G
II. Give more importance to Design as well, CSS and alignment details.
III. Try
IV.
---------------------------------------------
Saturday, January 10, 2009
Been busy these days working with hectic Schedules.
Been discovering many new ways and enjoying working with Sql Server.
Creating Complex Stored Procedures and making Fun of
Here is , which i created recently.
Dynamically Create Columns and Rows from Two independent tables.
This was for one fin Report and i spent 3 hrs for this.
Try if you can digest it or else Move On...
-------------------------------------------
CREATE PROCEDURE USP_AUTO_MONTHLY_CONSOLIDATED_REPORT@PRODUCT_TYPE INT,@DATE_FROM DATETIME,@DATE_TO DATETIMEAS
/*DECLARE @PRODUCT_TYPE INTSET @PRODUCT_TYPE = 141DECLARE @DATE_FROM DATETIMEDECLARE @DATE_TO DATETIMESET @DATE_FROM = '1/01/2008'SET @DATE_TO = '1/07/2009'*/
BEGIN
SET NOCOUNT ON
DECLARE @TMP_DATE_FROM DATETIME DECLARE @MONTHNAME NVARCHAR(20) DECLARE @MONTHNO INT DECLARE @YEARNO INT
SET @TMP_DATE_FROM = @DATE_FROM
CREATE TABLE #TEMP_MONTH_YEAR_DETAILS ( MONTH_NAME NVARCHAR(20), MONTH_NO INT, YEAR_NO INT )
WHILE @TMP_DATE_FROM <= @DATE_TO BEGIN SELECT @MONTHNAME = CASE MONTH(@TMP_DATE_FROM) WHEN 1 THEN 'January (' + CAST(YEAR(@TMP_DATE_FROM) AS NVARCHAR) + ')' WHEN 2 THEN 'February (' + CAST(YEAR(@TMP_DATE_FROM) AS NVARCHAR) + ')' WHEN 3 THEN 'March (' + CAST(YEAR(@TMP_DATE_FROM) AS NVARCHAR) + ')' WHEN 4 THEN 'April (' + CAST(YEAR(@TMP_DATE_FROM) AS NVARCHAR) + ')' WHEN 5 THEN 'May (' + CAST(YEAR(@TMP_DATE_FROM) AS NVARCHAR) + ')' WHEN 6 THEN 'June (' + CAST(YEAR(@TMP_DATE_FROM) AS NVARCHAR) + ')' WHEN 7 THEN 'July (' + CAST(YEAR(@TMP_DATE_FROM) AS NVARCHAR) + ')' WHEN 8 THEN 'August (' + CAST(YEAR(@TMP_DATE_FROM) AS NVARCHAR) + ')' WHEN 9 THEN 'September (' + CAST(YEAR(@TMP_DATE_FROM) AS NVARCHAR) + ')' WHEN 10 THEN 'October (' + CAST(YEAR(@TMP_DATE_FROM) AS NVARCHAR) + ')' WHEN 11 THEN 'November (' + CAST(YEAR(@TMP_DATE_FROM) AS NVARCHAR) + ')' WHEN 12 THEN 'December (' + CAST(YEAR(@TMP_DATE_FROM) AS NVARCHAR) + ')' END
--SELECT @MONTHNAME SET @MONTHNO = MONTH(@TMP_DATE_FROM) SET @YEARNO = YEAR(@TMP_DATE_FROM)
IF NOT EXISTS ( SELECT MONTH_NO FROM #TEMP_MONTH_YEAR_DETAILS WHERE MONTH_NO = @MONTHNO AND YEAR_NO = @YEARNO ) INSERT INTO #TEMP_MONTH_YEAR_DETAILS (MONTH_NAME , MONTH_NO , YEAR_NO) VALUES (@MONTHNAME,@MONTHNO,@YEARNO)
SET @TMP_DATE_FROM = DATEADD(MM,1,@TMP_DATE_FROM) END
--SELECT * FROM #TEMP_MONTH_YEAR_DETAILS
IF @PRODUCT_TYPE <> -1BEGIN
SELECT O.MONTH_NAME, ISNULL(A.TOTAL_INFLOW_COUNT,0) TOTAL_INFLOW_COUNT, ISNULL(B.DECLINED,0) DECLINED_COUNT, ISNULL(C.REJECTED,0) REJECTED_COUNT, ISNULL(D.APPROVED,0) APPROVED_COUNT FROM ( SELECT * FROM #TEMP_MONTH_YEAR_DETAILS ) O LEFT OUTER JOIN ( SELECT COUNT(MONTH(CREATED_ON)) TOTAL_INFLOW_COUNT, MONTH(CREATED_ON) CREATED_MONTH, YEAR(CREATED_ON) CREATED_YEAR FROM AT_AUTO_CE WHERE AT_AUTO_CE.FK_PRODUCT_TYPE = @PRODUCT_TYPE AND AT_AUTO_CE.CREATED_ON BETWEEN @DATE_FROM AND @DATE_TO GROUP BY MONTH(CREATED_ON),YEAR(CREATED_ON) ) A ON O.MONTH_NO = A.CREATED_MONTH AND O.YEAR_NO = A.CREATED_YEAR LEFT OUTER JOIN ( SELECT COUNT(MONTH(CREATED_ON)) DECLINED, MONTH(CREATED_ON) CREATED_MONTH, YEAR(CREATED_ON) CREATED_YEAR FROM AT_AUTO_CE WHERE FK_STATUS = 33 AND AT_AUTO_CE.CREATED_ON BETWEEN @DATE_FROM AND @DATE_TO AND AT_AUTO_CE.FK_PRODUCT_TYPE = @PRODUCT_TYPE GROUP BY MONTH(CREATED_ON),YEAR(CREATED_ON) ) B ON O.MONTH_NO = B.CREATED_MONTH AND O.YEAR_NO = B.CREATED_YEAR LEFT OUTER JOIN ( SELECT COUNT(MONTH(CREATED_ON)) REJECTED, MONTH(CREATED_ON) CREATED_MONTH, YEAR(CREATED_ON) CREATED_YEAR FROM AT_AUTO_CE WHERE FK_STATUS = 32 AND AT_AUTO_CE.FK_PRODUCT_TYPE = @PRODUCT_TYPE AND AT_AUTO_CE.CREATED_ON BETWEEN @DATE_FROM AND @DATE_TO GROUP BY MONTH(CREATED_ON),YEAR(CREATED_ON) ) C
ON O.MONTH_NO = C.CREATED_MONTH AND O.YEAR_NO = C.CREATED_YEAR LEFT OUTER JOIN ( SELECT COUNT(MONTH(CREATED_ON)) APPROVED, MONTH(CREATED_ON) CREATED_MONTH, YEAR(CREATED_ON) CREATED_YEAR FROM AT_AUTO_CE WHERE FK_STATUS = 31 AND AT_AUTO_CE.FK_PRODUCT_TYPE = @PRODUCT_TYPE AND AT_AUTO_CE.CREATED_ON BETWEEN @DATE_FROM AND @DATE_TO GROUP BY MONTH(CREATED_ON),YEAR(CREATED_ON) ) D ON O.MONTH_NO = D.CREATED_MONTH AND O.YEAR_NO = D.CREATED_YEAR
ENDELSEBEGIN-- SELECT O.MONTH_NAME, ISNULL(A.TOTAL_INFLOW_COUNT,0) TOTAL_INFLOW_COUNT, ISNULL(B.DECLINED,0) DECLINED_COUNT, ISNULL(C.REJECTED,0) REJECTED_COUNT, ISNULL(D.APPROVED,0) APPROVED_COUNT FROM ( SELECT * FROM #TEMP_MONTH_YEAR_DETAILS ) O LEFT OUTER JOIN ( SELECT COUNT(MONTH(CREATED_ON)) TOTAL_INFLOW_COUNT, MONTH(CREATED_ON) CREATED_MONTH, YEAR(CREATED_ON) CREATED_YEAR FROM AT_AUTO_CE WHERE AT_AUTO_CE.CREATED_ON BETWEEN @DATE_FROM AND @DATE_TO GROUP BY MONTH(CREATED_ON),YEAR(CREATED_ON) ) A ON O.MONTH_NO = A.CREATED_MONTH AND O.YEAR_NO = A.CREATED_YEAR LEFT OUTER JOIN ( SELECT COUNT(MONTH(CREATED_ON)) DECLINED, MONTH(CREATED_ON) CREATED_MONTH, YEAR(CREATED_ON) CREATED_YEAR FROM AT_AUTO_CE WHERE FK_STATUS = 33 AND AT_AUTO_CE.CREATED_ON BETWEEN @DATE_FROM AND @DATE_TO GROUP BY MONTH(CREATED_ON),YEAR(CREATED_ON) ) B ON O.MONTH_NO = B.CREATED_MONTH AND O.YEAR_NO = B.CREATED_YEAR LEFT OUTER JOIN ( SELECT COUNT(MONTH(CREATED_ON)) REJECTED, MONTH(CREATED_ON) CREATED_MONTH, YEAR(CREATED_ON) CREATED_YEAR FROM AT_AUTO_CE WHERE FK_STATUS = 32 AND AT_AUTO_CE.CREATED_ON BETWEEN @DATE_FROM AND @DATE_TO GROUP BY MONTH(CREATED_ON),YEAR(CREATED_ON) ) C
ON O.MONTH_NO = C.CREATED_MONTH AND O.YEAR_NO = C.CREATED_YEAR LEFT OUTER JOIN ( SELECT COUNT(MONTH(CREATED_ON)) APPROVED, MONTH(CREATED_ON) CREATED_MONTH, YEAR(CREATED_ON) CREATED_YEAR FROM AT_AUTO_CE WHERE FK_STATUS = 31 AND AT_AUTO_CE.CREATED_ON BETWEEN @DATE_FROM AND @DATE_TO GROUP BY MONTH(CREATED_ON),YEAR(CREATED_ON) ) D ON O.MONTH_NO = D.CREATED_MONTH AND O.YEAR_NO = D.CREATED_YEAR--END DROP TABLE #TEMP_MONTH_YEAR_DETAILSEND
GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GO
/************************************************************************** SP
************************************************************************/
CREATE PROCEDURE USP_PRODUCTWISE_DEVIATION_REASON_REPORT@DATE_FROM DATETIME,@DATE_TO DATETIMEASBEGIN /*DECLARE @DATE_FROM DATETIME DECLARE @DATE_TO DATETIME --- ONLY FOR TESTING PURPOSE. SET @DATE_FROM = '1/1/1900' SET @DATE_TO = '1/1/2100'*/
DECLARE @TEMP_SQL NVARCHAR(4000) -- THIS WILL HAVE A SQL SCRIPT FOR CREATING A NEW TABLE "TEMP_OUTPUT" DECLARE @TEMP_DATATYPE NVARCHAR(100) -- A SQL SCRIPT WHICH SPECIFIES DATATYPE OF THE COLUMNS IN "TEMP_OUTPUT" DECLARE @TEMP_PROD_TYPES NVARCHAR(100) -- THIS WILL STORE PRODCUTNAME WHICH WILL BE THE COLUMNS OF THE "TEMP_OUTPUT"
DECLARE @TEMP_FINAL_SQL NVARCHAR(4000) -- THIS WILL HAVE A SQL SCRIPT TO UPDATE "TEMP_OUTPUT" WITH COUNTS. DECLARE @TEMP_SQL_COLUMN_NAME_QUERY NVARCHAR(4000) -- THIS WILL HAVE A SQL SCRIPT TO IDENTIFY WHICH COLUMN NEEDS TO BE UPDATED IN "TEMP_OUTPUT" DECLARE @TEMP_SQL_UPDATE_QUERY NVARCHAR(4000) -- THIS WILL HAVE A DEFAULT UPDATE SCRIPT DECLARE @TEMP_SQL_UPDATE_WHERE_QUERY NVARCHAR(4000) -- THIS WILL HAVE A DEFAULT UPDATE SCRIPT WHERE CLAUSE
DECLARE @TEMP_PROD NUMERIC(13,0) -- THIS WILL STORE THE PRODUCTS DECLARE @TEMP_PROD_TWO NVARCHAR(1000) -- THIS WILL ALSO STORE THE PRODUCTS, BUT AS A NVARCHAR DECLARE @TEMP_PROD_DESC NVARCHAR(1000) -- THIS WILL STORE THE PRODUCT DESCRIPTION, SOMETHING LIKE PRODNAME_PRODID DECLARE @TEMP_DEVIATION_ID NUMERIC(13,0) -- THIS WILL STORE THE DEVIATION ID DECLARE @TEMP_DEVIATION_REASON NVARCHAR(100) -- THIS WILL STORE THE DEVIATION REASON DECLARE @PROD_COUNT_VALUE INT -- THIS WILL STORE THE REQUIRED COUNTS OF EACH PRODUCT FOR EACH DEVIATION
-- --BELOW THREE DECLARED VARIABLE, ARE USED TO GET THE UPDATE COLUMN. DECLARE @TARGET_COL NVARCHAR(100) DECLARE @UPDATE_COL NVARCHAR(100) DECLARE @TEMP_COL_OUTPUT NVARCHAR(100) --
--BASIC TABLE CREATION QUERY SET @TEMP_SQL = ' CREATE TABLE TEMP_OUTPUT ([DEVIATION_REASON_SG_KEY] [numeric](13, 0) NOT NULL , DEVIATION_REASON NVARCHAR(200) ' SET @TEMP_DATATYPE = ' NVARCHAR(300) NULL ' --
--BASIC TABLE UPDATE QUERY SET @UPDATE_COL = N'@TARGET_COL varchar(5000) OUTPUT' SET @TEMP_SQL_UPDATE_QUERY = 'UPDATE TEMP_OUTPUT SET ' -- COLUMN NAME = VALUE SET @TEMP_SQL_UPDATE_WHERE_QUERY = ' WHERE DEVIATION_REASON_SG_KEY =' --
-- BEGIN "TEMP_OUTPUT" TABLE CREATION
DECLARE CUR_FETCH_PROD_NAMES CURSOR FOR
SELECT TYPE_DESCRIPTION + '_'+ CAST(TYPE_CODE AS NVARCHAR) FROM AT_TYPE WHERE TYPE_FOR = 'Product Type' AND TYPE = 2 ORDER BY TYPE_DESCRIPTION + '_'+ CAST(TYPE_CODE AS NVARCHAR) DESC OPEN CUR_FETCH_PROD_NAMES
FETCH NEXT FROM CUR_FETCH_PROD_NAMES INTO @TEMP_PROD_TYPES
WHILE @@FETCH_STATUS = 0 BEGIN --SELECT @TEMP_PROD_TYPES SET @TEMP_SQL = @TEMP_SQL + ', [' +@TEMP_PROD_TYPES + ']'+ @TEMP_DATATYPE
FETCH NEXT FROM CUR_FETCH_PROD_NAMES INTO @TEMP_PROD_TYPES END
SET @TEMP_SQL = @TEMP_SQL + ' ) ' EXEC (@TEMP_SQL) -- THE TABLE "TEMP_OUTPUT" IS CREATED, NOW ITS TIME TO DUMP IN RECORDS.
CLOSE CUR_FETCH_PROD_NAMES DEALLOCATE CUR_FETCH_PROD_NAMES -- CLEAR THE CURSOR CUR_FETCH_PROD_NAMES
---------------------------------------------------------------------------------------------------------
-- START TO INPUT DATA TO THE NEWLY CREATED "TEMP_OUTPUT" TABLE.
DECLARE CUR_LOOP_PROD_NAMES CURSOR FOR
SELECT TYPE_DESCRIPTION + '_'+ CAST(TYPE_CODE AS NVARCHAR) , TYPE_CODE FROM AT_TYPE WHERE TYPE_FOR = 'Product Type' AND TYPE = 2 ORDER BY TYPE_DESCRIPTION + '_'+ CAST(TYPE_CODE AS NVARCHAR) DESC
OPEN CUR_LOOP_PROD_NAMES
FETCH NEXT FROM CUR_LOOP_PROD_NAMES INTO @TEMP_PROD_DESC, @TEMP_PROD -- FETCH PROD DESCRIPTION AND PRODUCT ID
WHILE @@FETCH_STATUS = 0 BEGIN SET @TEMP_PROD_TWO = '' SET @TEMP_PROD_TWO = @TEMP_PROD -- @TEMP_PROD_TWO WILL BE USED FOR THE DYNAMIC QUERY. --PRINT @TEMP_PROD_TWO
DECLARE CUR_LOOP_DEVIATION_REASON CURSOR FOR SELECT DESCRIPTION,SG_KEY FROM AT_DEVIATION_MASTER
OPEN CUR_LOOP_DEVIATION_REASON
FETCH NEXT FROM CUR_LOOP_DEVIATION_REASON INTO @TEMP_DEVIATION_REASON,@TEMP_DEVIATION_ID -- FETCH DEVIATION REASON AND DEVIATION ID
WHILE @@FETCH_STATUS = 0 BEGIN --- CHECK IF DEVIATION SG_KEY IS PRESENT IN "TEMP_OUTPUT" TABLE.
IF NOT EXISTS(SELECT DEVIATION_REASON_SG_KEY FROM TEMP_OUTPUT WHERE DEVIATION_REASON_SG_KEY = @TEMP_DEVIATION_ID) BEGIN -- DEVIATION_REASON_SG_KEY IS NOT PRESENT, SO INSERT. INSERT INTO TEMP_OUTPUT(DEVIATION_REASON_SG_KEY,DEVIATION_REASON) VALUES(@TEMP_DEVIATION_ID,@TEMP_DEVIATION_REASON) END ELSE BEGIN -- DEVIATION_REASON_SG_KEY IS PRESENT --UPDATE "TEMP_OUTPUT" TABLE.
-- FETCH CORRESPONDING COUNT VALUES. SET @PROD_COUNT_VALUE = ( SELECT ISNULL(COUNT(FK_PRODUCT_TYPE),0) FROM AT_AUTO_CE WHERE AT_AUTO_CE.CE_DEVTN = 1 AND AT_AUTO_CE.CE_DEVTN_REASON = @TEMP_DEVIATION_ID --AT_DEVIATION_MASTER.SG_KEY AND AT_AUTO_CE.FK_PRODUCT_TYPE = @TEMP_PROD-- FK_PRODUCT_TYPE AND AT_AUTO_CE.CREATED_ON BETWEEN @DATE_FROM AND @DATE_TO GROUP BY FK_PRODUCT_TYPE ) -- ONLY UPDATE THE TABLE IF @PROD_COUNT_VALUE <> 0 -- OR ELSE THE VALUE WILL BE NULL, BT DEFAULT. IF @PROD_COUNT_VALUE <> 0 BEGIN --SELECT @PROD_COUNT_VALUE --IDENTIFY THE COLUMN TO UPDATE THE VALUE -- SET @TEMP_SQL_COLUMN_NAME_QUERY = 'SELECT @TARGET_COL = column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name= ''TEMP_OUTPUT'' AND column_name LIKE''%' SET @TEMP_SQL_COLUMN_NAME_QUERY = @TEMP_SQL_COLUMN_NAME_QUERY + @TEMP_PROD_TWO + '''' --PRINT @TEMP_SQL_COLUMN_NAME_QUERY EXECUTE sp_executesql @TEMP_SQL_COLUMN_NAME_QUERY, @UPDATE_COL, @TARGET_COL = @TEMP_COL_OUTPUT OUTPUT --SELECT @TEMP_COL_OUTPUT AS OUTT -- NOW THE REQ COLUMN NAME WILL BE STORED IN @TEMP_COL_OUTPUT VARIABLE SET @TEMP_FINAL_SQL = @TEMP_SQL_UPDATE_QUERY + '['+ @TEMP_COL_OUTPUT + '] =' + CAST(@PROD_COUNT_VALUE AS NVARCHAR) + @TEMP_SQL_UPDATE_WHERE_QUERY + CAST(@TEMP_DEVIATION_ID AS NVARCHAR) --PRINT @TEMP_FINAL_SQL -- @TEMP_FINAL_SQL WILL HOLD THE FINAL UPDATE QUERY. EXEC(@TEMP_FINAL_SQL) -- UPDATED THE "TEMP_OUTPUT" TABLE. END-- END FETCH NEXT FROM CUR_LOOP_DEVIATION_REASON INTO @TEMP_DEVIATION_REASON,@TEMP_DEVIATION_ID END
CLOSE CUR_LOOP_DEVIATION_REASON DEALLOCATE CUR_LOOP_DEVIATION_REASON
FETCH NEXT FROM CUR_LOOP_PROD_NAMES INTO @TEMP_PROD_DESC, @TEMP_PROD END
CLOSE CUR_LOOP_PROD_NAMES DEALLOCATE CUR_LOOP_PROD_NAMES
--------------------------------------------------------------------------------------------------------
SELECT * FROM TEMP_OUTPUT DROP TABLE TEMP_OUTPUTEND
GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO
Been discovering many new ways and enjoying working with Sql Server.
Creating Complex Stored Procedures and making Fun of
Here is , which i created recently.
Dynamically Create Columns and Rows from Two independent tables.
This was for one fin Report and i spent 3 hrs for this.
Try if you can digest it or else Move On...
-------------------------------------------
CREATE PROCEDURE USP_AUTO_MONTHLY_CONSOLIDATED_REPORT@PRODUCT_TYPE INT,@DATE_FROM DATETIME,@DATE_TO DATETIMEAS
/*DECLARE @PRODUCT_TYPE INTSET @PRODUCT_TYPE = 141DECLARE @DATE_FROM DATETIMEDECLARE @DATE_TO DATETIMESET @DATE_FROM = '1/01/2008'SET @DATE_TO = '1/07/2009'*/
BEGIN
SET NOCOUNT ON
DECLARE @TMP_DATE_FROM DATETIME DECLARE @MONTHNAME NVARCHAR(20) DECLARE @MONTHNO INT DECLARE @YEARNO INT
SET @TMP_DATE_FROM = @DATE_FROM
CREATE TABLE #TEMP_MONTH_YEAR_DETAILS ( MONTH_NAME NVARCHAR(20), MONTH_NO INT, YEAR_NO INT )
WHILE @TMP_DATE_FROM <= @DATE_TO BEGIN SELECT @MONTHNAME = CASE MONTH(@TMP_DATE_FROM) WHEN 1 THEN 'January (' + CAST(YEAR(@TMP_DATE_FROM) AS NVARCHAR) + ')' WHEN 2 THEN 'February (' + CAST(YEAR(@TMP_DATE_FROM) AS NVARCHAR) + ')' WHEN 3 THEN 'March (' + CAST(YEAR(@TMP_DATE_FROM) AS NVARCHAR) + ')' WHEN 4 THEN 'April (' + CAST(YEAR(@TMP_DATE_FROM) AS NVARCHAR) + ')' WHEN 5 THEN 'May (' + CAST(YEAR(@TMP_DATE_FROM) AS NVARCHAR) + ')' WHEN 6 THEN 'June (' + CAST(YEAR(@TMP_DATE_FROM) AS NVARCHAR) + ')' WHEN 7 THEN 'July (' + CAST(YEAR(@TMP_DATE_FROM) AS NVARCHAR) + ')' WHEN 8 THEN 'August (' + CAST(YEAR(@TMP_DATE_FROM) AS NVARCHAR) + ')' WHEN 9 THEN 'September (' + CAST(YEAR(@TMP_DATE_FROM) AS NVARCHAR) + ')' WHEN 10 THEN 'October (' + CAST(YEAR(@TMP_DATE_FROM) AS NVARCHAR) + ')' WHEN 11 THEN 'November (' + CAST(YEAR(@TMP_DATE_FROM) AS NVARCHAR) + ')' WHEN 12 THEN 'December (' + CAST(YEAR(@TMP_DATE_FROM) AS NVARCHAR) + ')' END
--SELECT @MONTHNAME SET @MONTHNO = MONTH(@TMP_DATE_FROM) SET @YEARNO = YEAR(@TMP_DATE_FROM)
IF NOT EXISTS ( SELECT MONTH_NO FROM #TEMP_MONTH_YEAR_DETAILS WHERE MONTH_NO = @MONTHNO AND YEAR_NO = @YEARNO ) INSERT INTO #TEMP_MONTH_YEAR_DETAILS (MONTH_NAME , MONTH_NO , YEAR_NO) VALUES (@MONTHNAME,@MONTHNO,@YEARNO)
SET @TMP_DATE_FROM = DATEADD(MM,1,@TMP_DATE_FROM) END
--SELECT * FROM #TEMP_MONTH_YEAR_DETAILS
IF @PRODUCT_TYPE <> -1BEGIN
SELECT O.MONTH_NAME, ISNULL(A.TOTAL_INFLOW_COUNT,0) TOTAL_INFLOW_COUNT, ISNULL(B.DECLINED,0) DECLINED_COUNT, ISNULL(C.REJECTED,0) REJECTED_COUNT, ISNULL(D.APPROVED,0) APPROVED_COUNT FROM ( SELECT * FROM #TEMP_MONTH_YEAR_DETAILS ) O LEFT OUTER JOIN ( SELECT COUNT(MONTH(CREATED_ON)) TOTAL_INFLOW_COUNT, MONTH(CREATED_ON) CREATED_MONTH, YEAR(CREATED_ON) CREATED_YEAR FROM AT_AUTO_CE WHERE AT_AUTO_CE.FK_PRODUCT_TYPE = @PRODUCT_TYPE AND AT_AUTO_CE.CREATED_ON BETWEEN @DATE_FROM AND @DATE_TO GROUP BY MONTH(CREATED_ON),YEAR(CREATED_ON) ) A ON O.MONTH_NO = A.CREATED_MONTH AND O.YEAR_NO = A.CREATED_YEAR LEFT OUTER JOIN ( SELECT COUNT(MONTH(CREATED_ON)) DECLINED, MONTH(CREATED_ON) CREATED_MONTH, YEAR(CREATED_ON) CREATED_YEAR FROM AT_AUTO_CE WHERE FK_STATUS = 33 AND AT_AUTO_CE.CREATED_ON BETWEEN @DATE_FROM AND @DATE_TO AND AT_AUTO_CE.FK_PRODUCT_TYPE = @PRODUCT_TYPE GROUP BY MONTH(CREATED_ON),YEAR(CREATED_ON) ) B ON O.MONTH_NO = B.CREATED_MONTH AND O.YEAR_NO = B.CREATED_YEAR LEFT OUTER JOIN ( SELECT COUNT(MONTH(CREATED_ON)) REJECTED, MONTH(CREATED_ON) CREATED_MONTH, YEAR(CREATED_ON) CREATED_YEAR FROM AT_AUTO_CE WHERE FK_STATUS = 32 AND AT_AUTO_CE.FK_PRODUCT_TYPE = @PRODUCT_TYPE AND AT_AUTO_CE.CREATED_ON BETWEEN @DATE_FROM AND @DATE_TO GROUP BY MONTH(CREATED_ON),YEAR(CREATED_ON) ) C
ON O.MONTH_NO = C.CREATED_MONTH AND O.YEAR_NO = C.CREATED_YEAR LEFT OUTER JOIN ( SELECT COUNT(MONTH(CREATED_ON)) APPROVED, MONTH(CREATED_ON) CREATED_MONTH, YEAR(CREATED_ON) CREATED_YEAR FROM AT_AUTO_CE WHERE FK_STATUS = 31 AND AT_AUTO_CE.FK_PRODUCT_TYPE = @PRODUCT_TYPE AND AT_AUTO_CE.CREATED_ON BETWEEN @DATE_FROM AND @DATE_TO GROUP BY MONTH(CREATED_ON),YEAR(CREATED_ON) ) D ON O.MONTH_NO = D.CREATED_MONTH AND O.YEAR_NO = D.CREATED_YEAR
ENDELSEBEGIN-- SELECT O.MONTH_NAME, ISNULL(A.TOTAL_INFLOW_COUNT,0) TOTAL_INFLOW_COUNT, ISNULL(B.DECLINED,0) DECLINED_COUNT, ISNULL(C.REJECTED,0) REJECTED_COUNT, ISNULL(D.APPROVED,0) APPROVED_COUNT FROM ( SELECT * FROM #TEMP_MONTH_YEAR_DETAILS ) O LEFT OUTER JOIN ( SELECT COUNT(MONTH(CREATED_ON)) TOTAL_INFLOW_COUNT, MONTH(CREATED_ON) CREATED_MONTH, YEAR(CREATED_ON) CREATED_YEAR FROM AT_AUTO_CE WHERE AT_AUTO_CE.CREATED_ON BETWEEN @DATE_FROM AND @DATE_TO GROUP BY MONTH(CREATED_ON),YEAR(CREATED_ON) ) A ON O.MONTH_NO = A.CREATED_MONTH AND O.YEAR_NO = A.CREATED_YEAR LEFT OUTER JOIN ( SELECT COUNT(MONTH(CREATED_ON)) DECLINED, MONTH(CREATED_ON) CREATED_MONTH, YEAR(CREATED_ON) CREATED_YEAR FROM AT_AUTO_CE WHERE FK_STATUS = 33 AND AT_AUTO_CE.CREATED_ON BETWEEN @DATE_FROM AND @DATE_TO GROUP BY MONTH(CREATED_ON),YEAR(CREATED_ON) ) B ON O.MONTH_NO = B.CREATED_MONTH AND O.YEAR_NO = B.CREATED_YEAR LEFT OUTER JOIN ( SELECT COUNT(MONTH(CREATED_ON)) REJECTED, MONTH(CREATED_ON) CREATED_MONTH, YEAR(CREATED_ON) CREATED_YEAR FROM AT_AUTO_CE WHERE FK_STATUS = 32 AND AT_AUTO_CE.CREATED_ON BETWEEN @DATE_FROM AND @DATE_TO GROUP BY MONTH(CREATED_ON),YEAR(CREATED_ON) ) C
ON O.MONTH_NO = C.CREATED_MONTH AND O.YEAR_NO = C.CREATED_YEAR LEFT OUTER JOIN ( SELECT COUNT(MONTH(CREATED_ON)) APPROVED, MONTH(CREATED_ON) CREATED_MONTH, YEAR(CREATED_ON) CREATED_YEAR FROM AT_AUTO_CE WHERE FK_STATUS = 31 AND AT_AUTO_CE.CREATED_ON BETWEEN @DATE_FROM AND @DATE_TO GROUP BY MONTH(CREATED_ON),YEAR(CREATED_ON) ) D ON O.MONTH_NO = D.CREATED_MONTH AND O.YEAR_NO = D.CREATED_YEAR--END DROP TABLE #TEMP_MONTH_YEAR_DETAILSEND
GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GO
/************************************************************************** SP
************************************************************************/
CREATE PROCEDURE USP_PRODUCTWISE_DEVIATION_REASON_REPORT@DATE_FROM DATETIME,@DATE_TO DATETIMEASBEGIN /*DECLARE @DATE_FROM DATETIME DECLARE @DATE_TO DATETIME --- ONLY FOR TESTING PURPOSE. SET @DATE_FROM = '1/1/1900' SET @DATE_TO = '1/1/2100'*/
DECLARE @TEMP_SQL NVARCHAR(4000) -- THIS WILL HAVE A SQL SCRIPT FOR CREATING A NEW TABLE "TEMP_OUTPUT" DECLARE @TEMP_DATATYPE NVARCHAR(100) -- A SQL SCRIPT WHICH SPECIFIES DATATYPE OF THE COLUMNS IN "TEMP_OUTPUT" DECLARE @TEMP_PROD_TYPES NVARCHAR(100) -- THIS WILL STORE PRODCUTNAME WHICH WILL BE THE COLUMNS OF THE "TEMP_OUTPUT"
DECLARE @TEMP_FINAL_SQL NVARCHAR(4000) -- THIS WILL HAVE A SQL SCRIPT TO UPDATE "TEMP_OUTPUT" WITH COUNTS. DECLARE @TEMP_SQL_COLUMN_NAME_QUERY NVARCHAR(4000) -- THIS WILL HAVE A SQL SCRIPT TO IDENTIFY WHICH COLUMN NEEDS TO BE UPDATED IN "TEMP_OUTPUT" DECLARE @TEMP_SQL_UPDATE_QUERY NVARCHAR(4000) -- THIS WILL HAVE A DEFAULT UPDATE SCRIPT DECLARE @TEMP_SQL_UPDATE_WHERE_QUERY NVARCHAR(4000) -- THIS WILL HAVE A DEFAULT UPDATE SCRIPT WHERE CLAUSE
DECLARE @TEMP_PROD NUMERIC(13,0) -- THIS WILL STORE THE PRODUCTS DECLARE @TEMP_PROD_TWO NVARCHAR(1000) -- THIS WILL ALSO STORE THE PRODUCTS, BUT AS A NVARCHAR DECLARE @TEMP_PROD_DESC NVARCHAR(1000) -- THIS WILL STORE THE PRODUCT DESCRIPTION, SOMETHING LIKE PRODNAME_PRODID DECLARE @TEMP_DEVIATION_ID NUMERIC(13,0) -- THIS WILL STORE THE DEVIATION ID DECLARE @TEMP_DEVIATION_REASON NVARCHAR(100) -- THIS WILL STORE THE DEVIATION REASON DECLARE @PROD_COUNT_VALUE INT -- THIS WILL STORE THE REQUIRED COUNTS OF EACH PRODUCT FOR EACH DEVIATION
-- --BELOW THREE DECLARED VARIABLE, ARE USED TO GET THE UPDATE COLUMN. DECLARE @TARGET_COL NVARCHAR(100) DECLARE @UPDATE_COL NVARCHAR(100) DECLARE @TEMP_COL_OUTPUT NVARCHAR(100) --
--BASIC TABLE CREATION QUERY SET @TEMP_SQL = ' CREATE TABLE TEMP_OUTPUT ([DEVIATION_REASON_SG_KEY] [numeric](13, 0) NOT NULL , DEVIATION_REASON NVARCHAR(200) ' SET @TEMP_DATATYPE = ' NVARCHAR(300) NULL ' --
--BASIC TABLE UPDATE QUERY SET @UPDATE_COL = N'@TARGET_COL varchar(5000) OUTPUT' SET @TEMP_SQL_UPDATE_QUERY = 'UPDATE TEMP_OUTPUT SET ' -- COLUMN NAME = VALUE SET @TEMP_SQL_UPDATE_WHERE_QUERY = ' WHERE DEVIATION_REASON_SG_KEY =' --
-- BEGIN "TEMP_OUTPUT" TABLE CREATION
DECLARE CUR_FETCH_PROD_NAMES CURSOR FOR
SELECT TYPE_DESCRIPTION + '_'+ CAST(TYPE_CODE AS NVARCHAR) FROM AT_TYPE WHERE TYPE_FOR = 'Product Type' AND TYPE = 2 ORDER BY TYPE_DESCRIPTION + '_'+ CAST(TYPE_CODE AS NVARCHAR) DESC OPEN CUR_FETCH_PROD_NAMES
FETCH NEXT FROM CUR_FETCH_PROD_NAMES INTO @TEMP_PROD_TYPES
WHILE @@FETCH_STATUS = 0 BEGIN --SELECT @TEMP_PROD_TYPES SET @TEMP_SQL = @TEMP_SQL + ', [' +@TEMP_PROD_TYPES + ']'+ @TEMP_DATATYPE
FETCH NEXT FROM CUR_FETCH_PROD_NAMES INTO @TEMP_PROD_TYPES END
SET @TEMP_SQL = @TEMP_SQL + ' ) ' EXEC (@TEMP_SQL) -- THE TABLE "TEMP_OUTPUT" IS CREATED, NOW ITS TIME TO DUMP IN RECORDS.
CLOSE CUR_FETCH_PROD_NAMES DEALLOCATE CUR_FETCH_PROD_NAMES -- CLEAR THE CURSOR CUR_FETCH_PROD_NAMES
---------------------------------------------------------------------------------------------------------
-- START TO INPUT DATA TO THE NEWLY CREATED "TEMP_OUTPUT" TABLE.
DECLARE CUR_LOOP_PROD_NAMES CURSOR FOR
SELECT TYPE_DESCRIPTION + '_'+ CAST(TYPE_CODE AS NVARCHAR) , TYPE_CODE FROM AT_TYPE WHERE TYPE_FOR = 'Product Type' AND TYPE = 2 ORDER BY TYPE_DESCRIPTION + '_'+ CAST(TYPE_CODE AS NVARCHAR) DESC
OPEN CUR_LOOP_PROD_NAMES
FETCH NEXT FROM CUR_LOOP_PROD_NAMES INTO @TEMP_PROD_DESC, @TEMP_PROD -- FETCH PROD DESCRIPTION AND PRODUCT ID
WHILE @@FETCH_STATUS = 0 BEGIN SET @TEMP_PROD_TWO = '' SET @TEMP_PROD_TWO = @TEMP_PROD -- @TEMP_PROD_TWO WILL BE USED FOR THE DYNAMIC QUERY. --PRINT @TEMP_PROD_TWO
DECLARE CUR_LOOP_DEVIATION_REASON CURSOR FOR SELECT DESCRIPTION,SG_KEY FROM AT_DEVIATION_MASTER
OPEN CUR_LOOP_DEVIATION_REASON
FETCH NEXT FROM CUR_LOOP_DEVIATION_REASON INTO @TEMP_DEVIATION_REASON,@TEMP_DEVIATION_ID -- FETCH DEVIATION REASON AND DEVIATION ID
WHILE @@FETCH_STATUS = 0 BEGIN --- CHECK IF DEVIATION SG_KEY IS PRESENT IN "TEMP_OUTPUT" TABLE.
IF NOT EXISTS(SELECT DEVIATION_REASON_SG_KEY FROM TEMP_OUTPUT WHERE DEVIATION_REASON_SG_KEY = @TEMP_DEVIATION_ID) BEGIN -- DEVIATION_REASON_SG_KEY IS NOT PRESENT, SO INSERT. INSERT INTO TEMP_OUTPUT(DEVIATION_REASON_SG_KEY,DEVIATION_REASON) VALUES(@TEMP_DEVIATION_ID,@TEMP_DEVIATION_REASON) END ELSE BEGIN -- DEVIATION_REASON_SG_KEY IS PRESENT --UPDATE "TEMP_OUTPUT" TABLE.
-- FETCH CORRESPONDING COUNT VALUES. SET @PROD_COUNT_VALUE = ( SELECT ISNULL(COUNT(FK_PRODUCT_TYPE),0) FROM AT_AUTO_CE WHERE AT_AUTO_CE.CE_DEVTN = 1 AND AT_AUTO_CE.CE_DEVTN_REASON = @TEMP_DEVIATION_ID --AT_DEVIATION_MASTER.SG_KEY AND AT_AUTO_CE.FK_PRODUCT_TYPE = @TEMP_PROD-- FK_PRODUCT_TYPE AND AT_AUTO_CE.CREATED_ON BETWEEN @DATE_FROM AND @DATE_TO GROUP BY FK_PRODUCT_TYPE ) -- ONLY UPDATE THE TABLE IF @PROD_COUNT_VALUE <> 0 -- OR ELSE THE VALUE WILL BE NULL, BT DEFAULT. IF @PROD_COUNT_VALUE <> 0 BEGIN --SELECT @PROD_COUNT_VALUE --IDENTIFY THE COLUMN TO UPDATE THE VALUE -- SET @TEMP_SQL_COLUMN_NAME_QUERY = 'SELECT @TARGET_COL = column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name= ''TEMP_OUTPUT'' AND column_name LIKE''%' SET @TEMP_SQL_COLUMN_NAME_QUERY = @TEMP_SQL_COLUMN_NAME_QUERY + @TEMP_PROD_TWO + '''' --PRINT @TEMP_SQL_COLUMN_NAME_QUERY EXECUTE sp_executesql @TEMP_SQL_COLUMN_NAME_QUERY, @UPDATE_COL, @TARGET_COL = @TEMP_COL_OUTPUT OUTPUT --SELECT @TEMP_COL_OUTPUT AS OUTT -- NOW THE REQ COLUMN NAME WILL BE STORED IN @TEMP_COL_OUTPUT VARIABLE SET @TEMP_FINAL_SQL = @TEMP_SQL_UPDATE_QUERY + '['+ @TEMP_COL_OUTPUT + '] =' + CAST(@PROD_COUNT_VALUE AS NVARCHAR) + @TEMP_SQL_UPDATE_WHERE_QUERY + CAST(@TEMP_DEVIATION_ID AS NVARCHAR) --PRINT @TEMP_FINAL_SQL -- @TEMP_FINAL_SQL WILL HOLD THE FINAL UPDATE QUERY. EXEC(@TEMP_FINAL_SQL) -- UPDATED THE "TEMP_OUTPUT" TABLE. END-- END FETCH NEXT FROM CUR_LOOP_DEVIATION_REASON INTO @TEMP_DEVIATION_REASON,@TEMP_DEVIATION_ID END
CLOSE CUR_LOOP_DEVIATION_REASON DEALLOCATE CUR_LOOP_DEVIATION_REASON
FETCH NEXT FROM CUR_LOOP_PROD_NAMES INTO @TEMP_PROD_DESC, @TEMP_PROD END
CLOSE CUR_LOOP_PROD_NAMES DEALLOCATE CUR_LOOP_PROD_NAMES
--------------------------------------------------------------------------------------------------------
SELECT * FROM TEMP_OUTPUT DROP TABLE TEMP_OUTPUTEND
GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO
Wednesday, May 14, 2008
Molloys r us : Set page title dynamically in asp.net
Molloys r us : Set page title dynamically in asp.net: "Set page title dynamically in asp.net
There is no page title web control within asp.net so how would you set a page title Programmatically?
You can add an ID and Runat attribute to the existing title tag:
Within your code behind file add a html generic control with the other control declarations:
protected System.Web.UI.HtmlControls.HtmlGenericControl pageTitle;
The title can now be accessed programmatically just like any other web control:"
There is no page title web control within asp.net so how would you set a page title Programmatically?
You can add an ID and Runat attribute to the existing title tag:
Within your code behind file add a html generic control with the other control declarations:
protected System.Web.UI.HtmlControls.HtmlGenericControl pageTitle;
The title can now be accessed programmatically just like any other web control:"
Subscribe to:
Posts (Atom)
