An SQL text by Erland Sommarskog, SQL Server MVP.
If you follow the various newsgroups on Microsoft SQL Server, you often see people asking why they can't do:
SELECT * FROM @tablename
SELECT @colname FROM tbl
SELECT * FROM tbl WHERE x IN (@list)
For all three examples you can expect someone to answer Use dynamic SQL and give a quick example on how to do it. Unfortunately, for all three examples above, dynamic SQL is a poor solution. On the other hand, there are situations where dynamic SQL is the best or only way to go.
In this article I will discuss the use of dynamic SQL in stored procedures and to a minor extent from client languages. To set the scene, I start with a very quick overview on application architecture for data access. I then proceed to describe the feature dynamic SQL as such, with a quick introduction followed by the gory syntax details. Next, I continue with a discussion on SQL injection, a security issue that you absolutely must have good understanding of when you work with dynamic SQL. This is followed by a section where I discuss why we use stored procedures, and how that is affected by the use of dynamic SQL. I carryon with a section on good practices and tips for writing dynamic SQL. I conclude by reviewing a number of situations where you could use dynamic SQL and whether it is a good or bad idea to do it.
The article covers all versions of SQL Server from SQL 6.5 to SQL 2005, with emphasis on SQL 2000 and SQL 2005.
Contents:
Accessing a Data from an Application
Introducing Dynamic SQL
A First Encounter
sp_executesql
EXEC()
SQL Injection – a Serious Security Issue
Dynamic SQL and Stored Procedures
The Permission System
Caching Query Plans
Reducing Network Traffic
Encapsulating Logic
Keeping Track of what Is Used
Easiness of Writing SQL Code
Addressing Bugs and Problems
Good Coding Practices and Tips for Dynamic SQL
Use Debug Prints!
Nested Strings
Spacing and Formatting
Dealing with Dynamic Table and Column Names
Quotename, Nested Strings and Quotestring
QUOTED_IDENTIFIER
sp_executesql and Long SQL Strings in SQL 2000
Dynamic SQL in User-Defined Functions
Cursors and Dynamic SQL
EXEC() at Linked Server
Common Cases when to (Not) Use Dynamic SQL
SELECT * FROM @tablename
SELECT * FROM sales + @yymm
UPDATE tbl SET @colname = @value WHERE keycol = @keyval
SELECT * FROM @dbname + '..tbl'
SELECT * FROM tbl WHERE col IN (@list)
SELECT * FROM tbl WHERE @condition
Dynamic Search Conditions
SELECT * FROM tbl ORDER BY @col
SELECT TOP @n FROM tbl
CREATE TABLE @tbl
CREATE TABLE with Unknown Columns
Linked Servers
OPENQUERY
Dynamic Column Widths
Dynamic SQL and Maintenance Tasks
Acknowledgements and Feedback
Revision History
Note: many of the code samples in this text works against the pubs and Northwind databases that ship with SQL 2000 and SQL 7, but not with SQL 2005. You can download these databases from Microsoft's web site.
Thursday, March 19, 2009
Thursday, April 24, 2008
World’s most dangerous bird
I know, it looks like something that came out of an ostrich-impregnated turkey, but it’s actually very dangerous.
According to the Guinness Book of Records, the Cassowaries are the world’s most dangerous birds, capable of dealing fatal blows. They are very unpredictable, aggressive creatures, especially if wounded or cornered. The Cassowary lives in the rain forests of Australia and New Guinea and are actually pretty shy animals if undisturbed, but if you get to close and it thinks you’re a threat you could receive a bone-breaking kick or get sliced by its dagger-like sharp claws. During WWII, soldiers stationed in New Guinea were warned to stay away from these birds, but some of them still became victims.
The Cassowary is also one of the most difficult animals to keep in the Zoo because of the frequent injuries suffered by Zoo keepers that look after them. I like a bird that can take care of itself, but, unfortunately, this doesn’t seem to help it very much against human cruelty and it is on the endangered species list, along with so many others…
Beginners Guide To ASP.NET
What is ASP.Net?
ASP.Net is Microsoft’s latest Web development platform that provides you all the foundation and services necessary to develop Enterprise class Web applications. It is definitely the next-generation ASP, with a total shift in the way you do web development compared to ASP. Initially called as ASP+, Microsoft further renamed it to ASP.Net.
Why ASP.Net?
ASP.Net has introduced several enhancements and improvements over classical ASP which makes it a compelling platform to look at for all ASP professionals as well non-ASP software professionals.
Server controls make it simpler to do html style declarative programming with relatively less code.
You can leverage your current programming skills by using languages like C#, VB.Net for coding the pages which are languages used in all types of programming unlike VBScript, JavaScript which are just scripting languages.
Event driven model just like Windows programming helps write Event handlers easily with one click. Server side event handlers unlike ASP where all event handlers are written on the client side make the code safer from script hackers.
Object oriented model versus Procedural model of ASP that separates code from the HTML pages making the project extremely structured and clean; hence reducing maintenance headaches.
The code is compiled dynamically versus interpreted as in classical ASP which improves performance of the application.
ViewState which automatically remembers the state of the page on post back and reconstructs the page taking the onus off of the developer to write explicitly hidden fields.
Improved session state functionality-ASP.NET now supports moving sessions not only out of process, but also out of machine.
Straight forward no-touch just copy deployment without having to register any components; components can be updated without having to restart the web server.
Improved caching capabilities that enhance application performance.
Automatic recovery of application from memory leaks and crashes.
Getting Started with ASP.Net
The first thing a novice starting to get acquainted with ASP.Net development needs to download the .Net framework SDK itself. The Microsoft .NET Framework 1.1 Software Development Kit (SDK) includes the .NET Framework 1.1, as well as everything you need to write, build, test, and deploy applications using the .NET Framework 1.1, including documentation, samples, and command-line tools and compilers. This also includes ASP.Net 1.1.
Get the .NET Framework 1.1 SDK from our tool center.
ASP.Net involves also a lot of GUI programming on the client side- hence an IDE that enables GUI programming will be required. Visual Studio 2003 is the most recommended latest version for ASP.Net development. Visual Studio 2003 can be obtained from Microsoft and other resources like Amazon. For those of you who cannot afford to have Visual Studio 2003 there are few options to choose from.
SharpDevelop- a free, open source IDE for .Net development.
trial version of Visual Studio.
WebMatrix a free IDE for ASP.Net development.
C#Builder from Borland, their Personal Edition for non commercial development is free.
Novel’s Mono for Cross platform development in .Net, an open source IDE.
Please install IIS 5.x if you have not already done so from your components wizard setup. You need IIS 5.x web server to run ASP.Net applications.
ASP.Net is Microsoft’s latest Web development platform that provides you all the foundation and services necessary to develop Enterprise class Web applications. It is definitely the next-generation ASP, with a total shift in the way you do web development compared to ASP. Initially called as ASP+, Microsoft further renamed it to ASP.Net.
Why ASP.Net?
ASP.Net has introduced several enhancements and improvements over classical ASP which makes it a compelling platform to look at for all ASP professionals as well non-ASP software professionals.
Server controls make it simpler to do html style declarative programming with relatively less code.
You can leverage your current programming skills by using languages like C#, VB.Net for coding the pages which are languages used in all types of programming unlike VBScript, JavaScript which are just scripting languages.
Event driven model just like Windows programming helps write Event handlers easily with one click. Server side event handlers unlike ASP where all event handlers are written on the client side make the code safer from script hackers.
Object oriented model versus Procedural model of ASP that separates code from the HTML pages making the project extremely structured and clean; hence reducing maintenance headaches.
The code is compiled dynamically versus interpreted as in classical ASP which improves performance of the application.
ViewState which automatically remembers the state of the page on post back and reconstructs the page taking the onus off of the developer to write explicitly hidden fields.
Improved session state functionality-ASP.NET now supports moving sessions not only out of process, but also out of machine.
Straight forward no-touch just copy deployment without having to register any components; components can be updated without having to restart the web server.
Improved caching capabilities that enhance application performance.
Automatic recovery of application from memory leaks and crashes.
Getting Started with ASP.Net
The first thing a novice starting to get acquainted with ASP.Net development needs to download the .Net framework SDK itself. The Microsoft .NET Framework 1.1 Software Development Kit (SDK) includes the .NET Framework 1.1, as well as everything you need to write, build, test, and deploy applications using the .NET Framework 1.1, including documentation, samples, and command-line tools and compilers. This also includes ASP.Net 1.1.
Get the .NET Framework 1.1 SDK from our tool center.
ASP.Net involves also a lot of GUI programming on the client side- hence an IDE that enables GUI programming will be required. Visual Studio 2003 is the most recommended latest version for ASP.Net development. Visual Studio 2003 can be obtained from Microsoft and other resources like Amazon. For those of you who cannot afford to have Visual Studio 2003 there are few options to choose from.
SharpDevelop- a free, open source IDE for .Net development.
trial version of Visual Studio.
WebMatrix a free IDE for ASP.Net development.
C#Builder from Borland, their Personal Edition for non commercial development is free.
Novel’s Mono for Cross platform development in .Net, an open source IDE.
Please install IIS 5.x if you have not already done so from your components wizard setup. You need IIS 5.x web server to run ASP.Net applications.
The Art of writing Technical Articles
Many believe that they are not writers despite writing articles for newspapers and publications. I have a broad definition for writers. I believe anyone who can read or write is a writer. A favourite question asked to me is: “How do I become a writer?” I do not know what is the attraction. Glamour? Money? The urge to communicate? Recently, a student wrote to me and asked me this question and hence this piece.
My advice for those wish to become writers: Write! Write! Write! I have always maintained that great writers are born, and professional writers are made. In the born writers there is an unquenchable thirst for writing, a passion for writing. Writing is a mission. Writing is the soul of the person. The professional writer does it for a living. There is a deadline and the writer can churn out the required number of words.
You can gauge a writer from the quality of the writing. In the case of the born writer, the words flow from the depth of the heart. In the case of the “made writer”, words surge from the power of the pen and the power of the mind. Mind you, both can be equally effective.
I believe there is always a demand for writers, especially writers with talent, and those with domain knowledge. If you are an expert in any field, you can become a writer in that field. The tragedy is that students undergoing college and doing some computer diploma wish to become columnists overnight on subjects which they are just learning. This is somewhat akin to a medical intern performing a heart surgery.
Specialisation
While I recommend that you hone your writing skills, I feel you should also find your niche area. Concentrate on a domain area where there are a few writers.
In India, science journalism and information technology journalism is still not a well-developed field. I am sure it is still difficult to get good writers to write on technology. So the same specialist writes for all the papers. The same person is interviewed on all the TV channels. Obviously, the same answers are spouted all the time. If you are good, entry would be relatively easy.
Start writing
So start writing and look for avenues for publishing. The Internet affords you several opportunities. You can start writing Blogs and get noticed. Who knows, an editor may notice your powerful prose and get in touch with you to write for his/her paper? The more you write the better will be your writing style.
Start reading
Of course, those who have writing in their blood need not read. Wonderful prose will flow from their keyboards anyway. New writers can benefit a lot from reading other writers.
How do they start the column? How many words do they write? What is so sacred about 750 words? What kind of headings are effective? Which columnist do I like? Why do I think that this columnist is a fake? Once you read several columns, you will begin to appreciate style and content. You will be able to judge why columns sell and sizzle.
In a journalism workshop, we were told to read all the Sunday newspapers. For several years, I used to buy several Sunday newspapers. So I got exposed to a variety of writing styles. I learned about formatting and presentation. By the way, I started my apprenticeship (for my Bhavan’s Diploma in Journalism) with Sunday Standard and even wrote two pieces for that paper.
I think there are hundreds of good journals and magazines on information technology, science, and technology. Study them. Read them. Aspire to write for them. Wired has a great blow-your-mind style. Study this publication for a trendy way to write and communicate.
If you have access to American and British publications, you have a gold mine. Just study them for a year before you write your first word! Read at least six issues of a publication to get a hang of its content.
I am sure this very publication is the alma mater of many good writers and columnists. I confess to being one of the early products of this school of journalism.
Journalism course
In Mumbai, you have a Bachelor’s Degree in Journalism. There is a short course in Science Journalism too. Check out the newspaper for announcements. There are workshops on journalism. Such education may prove helpful for those who wish to be writers.
Hope these tips and tricks will help you become a writer. I look forward to seeing your articles soon.
My advice for those wish to become writers: Write! Write! Write! I have always maintained that great writers are born, and professional writers are made. In the born writers there is an unquenchable thirst for writing, a passion for writing. Writing is a mission. Writing is the soul of the person. The professional writer does it for a living. There is a deadline and the writer can churn out the required number of words.
You can gauge a writer from the quality of the writing. In the case of the born writer, the words flow from the depth of the heart. In the case of the “made writer”, words surge from the power of the pen and the power of the mind. Mind you, both can be equally effective.
I believe there is always a demand for writers, especially writers with talent, and those with domain knowledge. If you are an expert in any field, you can become a writer in that field. The tragedy is that students undergoing college and doing some computer diploma wish to become columnists overnight on subjects which they are just learning. This is somewhat akin to a medical intern performing a heart surgery.
Specialisation
While I recommend that you hone your writing skills, I feel you should also find your niche area. Concentrate on a domain area where there are a few writers.
In India, science journalism and information technology journalism is still not a well-developed field. I am sure it is still difficult to get good writers to write on technology. So the same specialist writes for all the papers. The same person is interviewed on all the TV channels. Obviously, the same answers are spouted all the time. If you are good, entry would be relatively easy.
Start writing
So start writing and look for avenues for publishing. The Internet affords you several opportunities. You can start writing Blogs and get noticed. Who knows, an editor may notice your powerful prose and get in touch with you to write for his/her paper? The more you write the better will be your writing style.
Start reading
Of course, those who have writing in their blood need not read. Wonderful prose will flow from their keyboards anyway. New writers can benefit a lot from reading other writers.
How do they start the column? How many words do they write? What is so sacred about 750 words? What kind of headings are effective? Which columnist do I like? Why do I think that this columnist is a fake? Once you read several columns, you will begin to appreciate style and content. You will be able to judge why columns sell and sizzle.
In a journalism workshop, we were told to read all the Sunday newspapers. For several years, I used to buy several Sunday newspapers. So I got exposed to a variety of writing styles. I learned about formatting and presentation. By the way, I started my apprenticeship (for my Bhavan’s Diploma in Journalism) with Sunday Standard and even wrote two pieces for that paper.
I think there are hundreds of good journals and magazines on information technology, science, and technology. Study them. Read them. Aspire to write for them. Wired has a great blow-your-mind style. Study this publication for a trendy way to write and communicate.
If you have access to American and British publications, you have a gold mine. Just study them for a year before you write your first word! Read at least six issues of a publication to get a hang of its content.
I am sure this very publication is the alma mater of many good writers and columnists. I confess to being one of the early products of this school of journalism.
Journalism course
In Mumbai, you have a Bachelor’s Degree in Journalism. There is a short course in Science Journalism too. Check out the newspaper for announcements. There are workshops on journalism. Such education may prove helpful for those who wish to be writers.
Hope these tips and tricks will help you become a writer. I look forward to seeing your articles soon.
Subscribe to:
Posts (Atom)
