Google Maps trial

Loading...

/** Use WHILE and BREAK statements alongwith IF blocks to form flow similar to Switch Statements in Procedures and Functions **/

/* With successful use of while block and break statements,
I came up with an thought of acheving a flow similar to Switch Case statements, from programming langauge, in SQL.
Yes there is a case statement that we can use for Selecting, updating etc. But we cannot use the same for builing a business logic.
That is we cannot use a Switch or Case block that channels the flow of a business logic in Procedures, functions or mere scripts.
Hush!!! its hard to explain for me, but please try to imagine that I want to Select 'A' and then update table t1 if a @i is 1.
And Select 'B' and then delete table t3 if @i is 5. This, as far as I am aware, we cannot acheive with a single case Block in SQL.
Thus the following code.
*/



Declare @caseFlag Varchar(20)

SET @caseFlag = 'A'

WHILE 1 = 1
BEGIN

.....IF @caseFlag = 'A'
.....BEGIN
..........Print ' GOT A '
........../* Some other logic */
..........BREAK;
..... END

.....IF @caseFlag = 'B'
.....BEGIN
..........Print ' GOT B'
........../* Some other logic */
..........BREAK;
..... END

.....PRINT 'Got else'

.....BREAK ; -- REMOVING OR COMMENTING THIS BREAK WILL CAUSE AN INFINITE . .....WHILE LOOP.
END
/* I have use a true condition just to enter the block.
I check for conditions with the help of if statements.
Then break fom the block after the objective is acheived.
Pls note that if the last Break Statement is removed or commented, it would result in an infinite while loop.
One more advantage of this code would be that we can use any expression that evaluate to TRUE OR FALSE as Cases.
*/
---


/*
After testing the code above I just thought,
it would be great if could use it just like a switch statements that we use in Programming langauges.
Then I realised that I would need the flow to fall down from one case to another if I dont use a Break statement.
To acheive this, I have commented out the BREAK statements in the IF blocks and added the exepression to the blocks below it.
This would allow the flow to fall through the cases just as in Switch statements in programming languages.

*/
Declare @caseFlag Varchar(20)

SET @caseFlag = 'A'

WHILE 1 = 1
BEGIN

.....IF @caseFlag = 'A'
.....BEGIN
..........Print ' GOT A '
........../* Some other logic */
..........-- BREAK;
.....END

.....IF @caseFlag = 'B' OR @caseFlag = 'A'
.....BEGIN
..........Print ' GOT B'
.......... /* Some other logic */
..........-- BREAK;
.....END

.....PRINT 'Got else'

.....BREAK ; -- REMOVING OR COMMENTING THIS BREAK WILL CAUSE AN INFINITE . .....WHILE LOOP.
END

/* This just a basic idea and can be twisted and turned to acheive more custom flows.
May be something like Falling through case 'A' but not going to 'B' and then still go through a case 'C'
that could be introduced. I can think of many more such examples but I sure you got the basic idea and can play more with it.
Please do let me know if this blog helped you or in what way I could improve it.
Please do suggest any improvement or any other method you can think of to acheive this.
All suggestions are warmly welcome.
*/

/* Using While FOR conditional flow instead of IF */

/* This is not something I would do usually.
I dont break rules unless absolutely justified. (Though, I do love to do so.)
So was the reason here.

I had to execute the below complicated logic in somewhat better and easy manner.
The task I had to acheive was to import data into a table and append it if @append is 1.
Else truncate the table and populate the fresh data. But I had to keep history before doing so.
So I must populate the history table only if @append is 0 and @keepHistory is 1 and then truncate the original table.
If @append and @keepHistory were both 0 I must not keep a history but still truncate the table.
This can be acheived with If block as shown below.
( Pls note I have not used actual tables to protect client Information. But I still would like to present the flow)
*/


DECLARE @append BIT
DECLARE @successful BIT
DECLARE @keepHistory BIT
DECLARE @err INT

IF @append = 0
BEGIN
........................Set @successful = 1;
........................IF @keepHistory = 1
........................BEGIN
...............................Insert into TblHistory
..............................SELECT * FROM tablename

..............................SET @err = @@error;

..............................IF ( @err <> 0 )
..............................BEGIN
............................................................ --ON ERROR do not truncate table.
................................... SET @successful = 0;
.............................. END
........................END
/* if successful truncate the table */
........................IF @successful = 1;
........................ BEGIN
.............................. truncate table tablename;
........................ END
END

/* The above code looks cumbersome and complicated to me.
And this might invite some bugs if someone tries to change the code after several days.
To avoid this I needed some structure similar to a Case statement or BREAK in Programming Langauges like JAVA, C++ etc.
I devised similar logic with WHILE block and BREAK statements in SQL.
Please suggest if there is a better or even if there is any other way to acheive this that you can think of.
I would be more than delighted to know it.

*/


---

DECLARE @append BIT
DECLARE @successful BIT
DECLARE @keepHistory BIT
DECLARE @err INT

WHILE @append = 0
BEGIN
........IF @keepHistory = 1
........BEGIN
................ Insert into TblHistory
................ SELECT * FROM tablename

................ SET @err = @@error;

................ IF ( @err <> 0 )
................ BEGIN
........................ SET @successful = 0;
........................BREAK; -- terminate without truncating the table.
................ END
........ END

........ TRUNCATE TABLE tablname;
........BREAK; -- REMOVING OR COMMENTING THIS BREAK WILL CAUSE AN INFINITE WHILE LOOP.
END

/* With the use of while block and break statements I had to use less number of variables for acheiving the flow.
I have still used @successful even though it was unneccessary because I need it in the rest of the flow after the while block.
In this logic the table would not be truncated if there was an error while transfering data to history.
It is somewhat similar to returning from a function/procedure in SQl
or other programming langauges without executing some part of code on error.
Yes and I try to avoid all the GOTO statements for all the obvious reasons.
Also please note that commenting or removing the last break statement will result in an infinite while loop.
I hope this was helpful to you.
All suggestions are warmly welcome.
*/

Working with xml output in SQL Server.

Today I came across a nice article on MSSQLtips.com .
It says "Running a Dynamic Query against SQL Server without using Dynamic SQL".
This introduced me to the idea, that I could create xml output from a select query that I fire on a SQl Server (2000 & above).

Off-course this was not enough and I googled for more and found out many other links. A few noteworthy are as follows :

1) Nice Blog for XML Explicit : This blog shows the basic usage of 'For XML Explicit' syntax and example used for retrieving XML output from a simple Select query.

2) Retrieving XML from SQL Server Using SELECT ... FOR XML : Very good article. Explains Raw Mode, Auto Mode and Explicit Mode. Simple to understand.

3) How to export data from SQL Server 2000 to XML : Explains how to write the output to an Xml File. The idea is to create view based on the query used and then use "BCP QUERYOUT" to write the ourpur to an xml file with the help of "FOR XML" command.

4) XML in SQL Server 2000 and SQLXML :
Good explaination of the "For XML" Syntax. Along with some code in .NET with ADO objects.
I will say its a must read.

After going through the database I created some output with in my database. The Best query I found was on link number 4. I used bcp Queryout to create xml file. But I also took two approaches 1) to use FOR XML EXPLICIT and 2) to use FOR XML Auto, Bith of these create different outputs, though I am sure we can modify the query for EXPLICIT for a desired ouput.

Assume that there is a table which stores if the days of the current month are holidays in a particular country.
Now assume that there are three columns which are used to store this information. Namely "CountryId", "DAYID" and "Holiday"
Then using

1)
Create view XMlOutput as
(
Select 1 as [TAG], NULL as [Parent] , NULL as [Countries!1!],
NULL as [Country!2!CountryID] ,
NULL as [DAY!3!DAYID],
NULL as [DAY!3!Holiday!Element],

Union

(SELECT 2 as [TAG] , 1 as [Parent], NULL, CountryID , NULL , NULL, NULL
from CountryHolidays )

UNION
(SELECT 3 as [TAG] , 2 as [Parent], NULL, NULL , DAYID , Holiday from CountryHolidays )
)

now use this view to bcp Queryout the result to a XML file.

bcp "select * from TABLENAME..XMlOutput FOR XML EXPLICIT" queryout "D:\Akshay\data.xml" -w -r "" -T

Please find the output of the file here.

2) Using the For XML Auto command

bcp "Select 1 as TAG ,* from Country..CountryHolidays FOR XML AUTO, XMLDATA, BINARY BASE64, ELEMENTS" queryout "D:\Akshay\data.xml" -w -r "" -T
This will consider each row as a tag and each column will be a tag with the row as a parent.

Retrieve the days of a month which are not populated in a column

The following query retrieves the days of a particular month which are not used in a particular column.
Please change the column name and tablename alongwith the month and year to give expected results. Also this can be used to create a function or procedure by replacing the variables by parameters.


Create table #t1
(
checkdate datetime
)

DECLARE @checkdate AS DATETIME;
DECLARE @month AS varchar(2);
DECLARE @year AS varchar(4);
DECLARE @tablename AS varchar(100);
DECLARE @columnname AS varchar(100);

set @month = '06'
set @year = '2008'
set @tablename = 'tablename'
set @columnname = 'columndate'
select @checkdate = Convert(DATETIME,(select @year + '-' + @month + '-01'))


--select @checkdate

WHILE MONTH(@checkdate) = @month
BEGIN
insert into #t1
select @checkdate ;
select @checkdate = dateadd(dd,1,@checkdate) ;
END

execute ('select DAY(checkdate) , checkdate from #t1' +
' LEFT JOIN ('+
'select distinct DAY('+ @columnname+') as [d1] from '+ @tablename +'
where YEAR('+ @columnname+') = '+ @year +' and MONTH('+ @columnname+') = '+ @month +
') split '+
'ON ( day(#t1.checkdate) = split.[d1] ) '+
'where split.[d1] is NULL '+
'order by checkdate' )


/*
select DAY(checkdate), checkdate from #t1
where DAY(checkdate) not in (
select distinct DAY(columnname) from tblsplitmthis
where YEAR(columnname) = @year and MONTH(columnname) = @month
) from #t1
*/

drop table #t1

Finding and listing columns with default values in a database

This one is a handy script I found on a blog.


SELECT SO.NAME AS "Table Name", SC.NAME AS "Column Name", SM.TEXT AS "Default Value"
FROM dbo.sysobjects SO INNER JOIN dbo.syscolumns SC ON SO.id = SC.id
INNER JOIN dbo.syscomments SM ON SC.cdefault = SM.id
WHERE SO.xtype = 'U'
ORDER BY SO.[name], SC.colid


Read more at mssqltips.com

The power of Cross (Cartesian) Joins

This is a very good example of where the Cartesian (Cross) Join may be very helpful. Though proper understanding and enough experimentation should be done to try it out.
Jeff @ SQLTeam.com

It explains an example where a Join may be required to return some value ['0'] in this case when there are no matching records found. for e.g. return 0 for sales of all combination of products and stores which dont have a value i.e. null.


SELECT S.Store, P.Product, ISNULL(C.TotalSales,0) as TotalSales
FROM Stores S
CROSS JOIN Products P
LEFT OUTER JOIN
(SELECT Store, Product, SUM(Sales) as TotalSales
FROM Sales
GROUP BY Store, Product) C
ON
S.Store = C.Store AND
P.Product = C.Product

Also Refer:
Join Clause in SQl

Image representation of Joins in SQL

Found a similar blog with name
Visual Explanation of SQL JOINS

Deleting duplicate records.

Its a nice post on deleting duplicate records. She has mentioned 3 different methods with their advantages and disadvantages.

SQLteam.com

Object Oriented approach for SQL

Implementing Table inheritance in SQL Server.
SQlTeam.com

Implementing Table Interfaces
SQLTeam.com