动态sql

动态sql

 

you often see peopl ask why thei can't doIf you follow the variou newsgroup on Microsoft SQL Server.:

for all three exampl above,SELECT * FROM @tablenameSELECT @colnam FROM tblSELECT * FROM tbl WHERE x IN @list For all three exampl you can expect someon to answer Use dynam SQL and give a quick exampl on how to do it. Unfortunately. dynam SQL is a poor solution. On the other hand, there ar situat where dynam SQL is the best or onli wai to go.

I start with a veri quick overview on applic architectur for data access. I then proce to describ the featur dynam SQL as such,In thi articl I will discuss the us of dynam SQL in store procedur and to a minor extent from client languages. To set the scene. with a quick introduct follow by the gori syntax details. Next, I continu with a discuss on SQL injection, a secur issu that you absolut must have good understand of when you work with dynam SQL. Thi is follow by a section where I discuss why we us store procedures, and how that is affect by the us of dynam SQL. I carryon with a section on good practic and tip for write dynam SQL. I conclud by review a number of situat where you could us dynam SQL and whether it is a good or bad idea to do it.

with emphasi on SQL 2000 and SQL 2005. Th articl cover all version of SQL Server from SQL 6.5 to SQL 2005.

Contents:

   Access a Data from an Application

   Introduc Dynam SQL

      A First Encounter

      sp_executesql

      EXEC

   SQL Injection – a Seriou Secur Issue

   Dynam SQL and Store Procedures

      The Permiss System

      Cach Queri Plans

      Reduc Network Traffic

      Encapsul Logic

      Keep Track of what Is Used

      Easi of Write SQL Code

      Address Bug and Problems

   Good Code Practic and Tip for Dynam SQL

      Use Debug Prints!

      Nest Strings

      Space and Formatting

      Deal with Dynam Tabl and Column Names

Nest String and Quotestrin      Quotename.g

      QUOTED_IDENTIFIER

      sp_executesql and Long SQL String in SQL 2000

      Dynam SQL in User-Defin Functions

      Cursor and Dynam SQL

   EXEC at Link Server

   Common Case when to Not Use Dynam SQL

      SELECT * FROM @tablename

      SELECT * FROM sale + @yymm

      UPDA TE tbl SET @colnam = @valu WHERE keycol = @keyval

      SELECT * FROM @dbname + '..tbl'

      SELECT * FROM tbl WHERE col IN @list

      SELECT * FROM tbl WHERE @condition

      Dynam Search Conditions

      SELECT * FROM tbl ORDER BY @col

      SELECT TOP @n FROM tbl

      CREA TE TA BLE @tbl

      CREA TE TA BLE with Unknown Columns

      Link Servers

      OPENQUERY

      Dynam Column Widths

      Dynam SQL and Mainten Tasks

   Acknowledg and Feedback

   Revis Histori

but not with SQL 2005. You can download these databas from Microsoft' web site. Note: mani of the code sampl in thi text work against the pub and Northwind databas that ship with SQL 2000 and SQL 7.

A ccess a Data from an Application

I like to briefli discuss the variou wai you can access data from an applic to give an overview of what I'll be talk about in thi articleBefor I describ dynam SQL..

but as that is of littl interest to thi article, Note: all through thi text I will refer to client as anyth that access SQL Server from the outside. In the overal applic architectur that mai in fact be a middl tier or a busi layer. I us client in the sake of brevity.

and then there ar fork and sub-forksThere ar two main road to go..

Send SQL statement from the client to SQL Server.

us option like CommandType.TableDirect and method like .Update. Reli on SQL gener by the client API.

Compos the SQL string in the client code.

Build the entir SQL string with paramet valu expanded.

Use parameteris queries.

Perform access through store procedures.

Store procedur in T-SQL

Use static SQL only.

Use dynam SQL togeth with static SQL.

Store procedur in a CLR languag such as C# or VB .Net. SQL 2005 only.

but you ar like to find that you outgrow it as the complex of your applic increases. In ani case,Fork 1-a mai be good for simpl tasks. thi approach fall entir outsid the scope of thi article.

and as long as you take the sub-fork 1-b-ii,Mani applic ar built along the principl of fork 1-b. it doe not have to be bad. Why 1-b-i is bad, is someth I will come back to. Here I will just drop two keywords: SQL Injection and Query-Plan Reuse. Nonetheless, in mani shop the mandat is that you should us store procedures. When you us store procedur with onli static SQL, user do not need direct permiss to access the tables, onli permiss to execut the store procedures, and thu you can us the store procedur to control what user mai and mai not do.

dynam SQL in store procedur can be a power addit to static SQL. But some of the question on the newsgroup lead to dynam SQL in store procedur that is so meaningless,Th main focu for thi text is sub-fork 2-a-ii. When us appropriately. that these peopl would be better off with fork 1-b instead.

fork 2-b,Finally. store procedur in the CLR, is in mani regard veri similar to fork 1-b, sinc all data access from CLR procedur is through gener SQL strings, parameteris or unparameterised. If you have settl on SQL procedur for your application, there is littl point in rewrit them into the CLR. However, CLR code can be a valuabl supplement for task that ar difficult to perform in T-SQL, but you yet want to perform server-side.

Introduc Dynam SQL

the two command you can us to invok dynam SQL from T-SQLIn thi chapter I will first look at some quick exampl of dynam SQL and point out some veri import implic of us dynam SQL. I will then describ sp_executesql and EXEC in detail..

A First Encounter

it' rather too easi to use. Understand the fine details,Understand dynam SQL itself is not difficult. Au contraire. though, take a littl longer time. If you start out us dynam SQL casually, you ar bound to face accid when thing do not work as you have anticipated.

base on the two wai to do dynam SQL in Transact-SQLOn of the problem list in the introduct wa how to write a store procedur that take a tabl name as it input. Here ar two examples.:

                                 @key     varchar 10 ASDECLA RE @sql nvarchar 4000 SELECT @sql = ' SELECT col1,CREA TE PROCEDURE general_select1 @tblname sysname. col2, col3 ' +              ' FROM dbo.' + quotenam @tblname +              ' WHERE keycol = @key'EXEC sp_executesql @sql, N'@kei varchar 10 ', @keyCREA TE PROCEDURE general_select2 @tblname nvarchar 127 ,                                 @key     varchar 10 ASEXEC 'SELECT col1, col2, col3      FROM ' + @tblname + '      WHERE keycol = ''' + @kei + '''' Befor I sai anyth else, permit me to point out that these ar exampl of bad usag of dynam SQL. Pass a tabl name as a paramet is not how you should write store procedures, and on aim of thi articl is to explain thi in detail. Also, the two exampl ar not equivalent. While both exampl ar bad, the second exampl ha sever problem that the first doe not have. What these problem ar will be appar as you read thi text.

there ar some veri import thing to observe. The first thing is permissions. You mai know that when you us store procedures,Wherea the abov look veri simpl and easy. user do not need permiss to access the tabl access by the store procedure. Thi doe not appli when you us dynam SQL! For the procedur abov to execut successfully, the user must have SELECT permiss on the tabl in @tblname. In SQL 2000 and earlier thi is an absolut rule with no wai around it. SQL 2005 provid altern ways, someth I will come back to in the section The Permiss System.