I have found many solutions to pass a list parameter from ColdFusion to a stored procedure,
and this one is a very simple and efficient way to convert a string to a table. Thanks to Amit Gaur posting to SQL ServerCentral.
I modified his original function which returns only integers (forcing you to pass only a list of integers).
Suppose you pass the string ‘2A354,FB452,8896′ in the argument @itemList as a list of item codes you want to retrieve from your inventory table.
We are constantly implementing new tasks and developing web sites for about 12 customers. We have a development server at our office and our live servers are at our datacenter. Many times we have to rewrite or modify stored procedures and then after testing, we need to copy them to the live server.
Read the rest of this entry »
Jul 29
My friend Pedro Claudio posted a link on my Facebook Wall about this presentation made by Bernardo D. A. Guimarães at the Black Hat Briefings Europe, Amsterdam in April 2009. I found it interesting and here is the slide presentation:
I was following the SQL list and came across a post from Jim Gotwald where he showed his solution to convert a time string into a decimal value (ex: ‘10:30′ to 10.5).
So I decided to write a custom function where you can either pass a complete date as a string or just the time and it will return a float value representing the time in decimal.
You may call the function like this:
select dbo.timeToDecimal(cast(getDate()) as varchar(20))
select dbo.timeToDecimal(‘2008-06-18 10:35:43′)
select dbo.timeToDecimal(‘06/18/2009 10:22:00′)
select dbo.timeToDecimal(‘10:42′)
In my previous post, I discussed the function used to split a string into a table of tokens. Very useful instead creating a cursor.
Now, with SQL 2005/2008, it became easier and faster to do the same job using XML parsing.
I found this function online, written by Jacob Sebastian which is much faster than my old one:
Read the rest of this entry »
Feb 26
For those who are jumping into MS SQL 2008, here are two goo books to purchase. The first one, Microsoft SQL Server 2008 Management and Administration, is a complete, fast-paced, task-based reference you’ve been searching for. Ross Mistry presents proven techniques for SQL Servere 2008 installation, upgrades, backup/restore, data transfer, indexing and much more.
Another good one is the Pro SQL Server 2008 XML, where Michael Coles shows how to store XML using SQL Server’s built-in XML data type.
Everybody knows that dynamic queries take a lot more of work and time to process. I had last week a case in which I had a variable number of parameters to pass to a stored procedure and one of the parameters was the “order by” clause. Also, I needed to do some pagination on my results and show only 20 rows at a time.
I spent some time researching on the net, reading several blogs about that, and one of the solutions for the dynamic “where clause” came from a blog in Brazil which unfortunately I do not have the address anymore to credit, and the solution for the order by clause came from SQL Team.
Since I was using MS SQL 2000, I could not make use of the new ROW_NUMBER() function, so I created a temporary table with the passed criteria and selected from it within the range of rows also passed.
Read the rest of this entry »
I received a comment in my other article with the same title from Larry C. Lyons indicating me a better solution by Stuart Langridge making use of a Javascript library he wrote called sorttable.js, which sorts the HTML table by sortable headers, without having to send another request to the database.
Very well done, congratulations Stuart.
Dec 10
My reasoning behind the title of this blog entry is to discuss how to be confident in your skill set as an IT professional and to avoid that feeling of “Where am I going or am I doing the right thing with this solution?” Recently I was questioned by a team member on my approach to the database design I architected for my current application to be released to NASA for administration of their award processes. One might think an application geared toward awards administration would be a straight forward project with minimal requirements. This proved not to be the case. The requirements document alone for Phase I if printed could have contributed significantly to the planet’s deforestation.
To dig deeper into proper database design and the questions surrounding why I designed the database in the fashion I did, it became readily prevalent in the conversation with the team member and fellow coder that he didn’t have a full understanding of proper hierarchical approaches to db design and the pitfalls that can be encountered when taking an all encompassing table approach with a type table to differentiate each entry in this table. Not to speak of the issues that would be encountered with deletion of data (multiple delete statements instead of cascading) and self table joins with convoluted where clauses associated with multiple aliases. When designing databases it is important to understand that if the attributes of an entity (that may be thought of as similar but with changing types) differ in count, leaving entries with null values where they don’t apply requires a new entity in the db.
An example of this is organizations and divisions. Proper db design would enforce integrity by creating a table for each of the entities in question. They each have different characteristics other than being a different type and one belongs to the next (parent, child is immediately prevalent here). For coders who are not comfortable with joining tables I can relate with the type approach in question. To make coding easier for them in regard to retrieving data, views should be created. This all falls in-line with the title of this entry. Be a student of your craft. In discussing the issue I knew that he needed to do some reading on the subject and that my responses were not going to change his thinking because he didn’t understand all the benefits a properly architected database can provide. Under the guise of his confusion behind multiple joins to retrieve the data he needed for a lookup he didn’t understand that the type approach would require joins as well to present the hierarchical data, but with a table that would have multiple keys or unique constraints that would hinder editing and deleting of data.
There is something to be said for experience here, but experience can be compensated for by becoming a student of your craft. We’ve all been there when it comes to the “Is there a better way?” question when embarking down undiscovered territory. The only way to compensate for this uncertainty is to research and educate oneself on the issue in question.
Last Friday we upgraded our two database servers to MS SQL 2005. Our co-worker Matt Horrell found some scripts online that helped us a lot, like the one that reads all the users and generates another script to create them back on the new server, and the other that reads all the linked servers and do the same.
The whole process took 2:30 hs and it was smoothly done.
I’m posting here the steps to help anybody else.
As I told before, it took about 2.5 hours total, dealing with around 65 databases.
Read the rest of this entry »
You are currently browsing the archives for the Database category.
