SQL SERVER 存储过程动态SQL的实现
中文
这个问题在最近写存储过程时困扰了N久,用BAIDU查遍国内的各网站使用没有一个好的解决方案,最终通过GOOGLE搜索关键字"SQL SERVER DYNAMIC SQ"L,再通过各国外论坛才找到一个不起眼的网站的某页面.
文章的标题是
正如文章中所称,该文是为了解决诸如此类的问题:
SELECT * FROM @tablename
SELECT @colname FROM tbl
SELECT * FROM tbl WHERE x IN (@list)
而我最头疼的就是第一条，试图通过 select @name=name from @tablename来获取某动态表的变量.但文章中明确指出,同时也是我试了N次得到的结论
动态SQL只能通过如
@SQL='select @name=name from '+@tablename
要运行这条，大多数人都是通过EXEC(@SQL),但是，@name类似程序中局部变量,生存期只有在EXEC中.无法在SQL存储过程中使用.
在文章中终于找到解决办法......
范例程序
DECLARE @sql nvarchar(4000), -- nvarchar(MAX) on SQL 2005.
@col sysname,
@min varchar(20)
SELECT @col = N'au_fname'
SELECT @sql = N'SELECT @min = convert(varchar(20), MIN(' + @col +
N')) FROM authors'
EXEC sp_executesql @sql, N'@min varchar(20) OUTPUT', @min OUTPUT
SELECT @min
终于可以解决哩.......但要注意的是,sp_executesql后的参数只能是NVARCHAR,VARCHAR之类的字符型.
终于解决了.......
该文中还有一些防止SQL注入之类的讨论，以及对新手讲解SQL存储过程的意义何在.
做SQL数据库，并转向存储开发过程的人必看.....
技术,还是老外牛X......而且老外更倾向技术的交流，而国内号称有许多高手，真正乐意分享的太少太少......
英文原文
The Curse and Blessings of Dynamic SQL
An SQL text by Erland Sommarskog, SQL Server MVP.
An earlier version of this article is also available in Korean, German, Spanish and Vietnamese. Translations provided by ASP MVP Jongshin Kim, SQL Server MVP Frank Kalis, Simon Hayes and Tam Vu respectively.
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 c
ontinue 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.
Accessing a Data from an Application
Before I describe dynamic SQL, I like to briefly discuss the various ways you can access data from an application to give an overview of what I'll be talking about in this article.
(Note: all through this text I will refer to client as anything that accesses SQL Server from the outside. In the overall application architecture that may in fact be a middle tier or a business layer, but as that is of little interest to this article, I use client in the sake of brevity.)
There are two main roads to go, and then there are forks and sub-forks.
Send SQL statements from the client to SQL Server.
Rely on SQL generated by the client API, using op
tions like CommandType.TableDirect and methods like .Update.
Compose the SQL strings in the client code.
Build the entire SQL string with parameter values expanded.
Use parameterised queries.
Perform access through stored procedures.
Stored procedures in T-SQL
Use static SQL only.
Use dynamic SQL together with static SQL.
Stored procedures in a CLR language such as C# or VB .Net. (SQL 2005 only.)
Fork 1-a may be good for simple tasks, but you are likely to find that you outgrow it as the complexity of your application increases. In any case, this approach falls entirely outside the scope of this article.
Many applications are built along the principles of fork 1-b, and as long as you take the sub-fork 1-b-ii, it does not have to be bad. (Why 1-b-i is bad, is something I will come back to. Here I will just drop two keywords: SQL Injection and Query-Plan Reuse.) Nonetheless, in many shops the mandate is that you should use stored procedures. When you use stored procedures with only static SQL, users do not need direct permissions to access the tables, only permissions to execute the stored procedures, and thus you can use the stored procedure to control what users may and may not do.
The main focus for this text is sub-fork 2-a-ii. When used appropriately, dynamic SQL in stored procedures can be a powerful addition to static SQL. But some of the questions on the newsgroups leads to dynamic SQL in stored procedures that is so meaningless, that these people would be better off with fork 1-b instead.
Finally, fork 2-b, stored procedures in the CLR, is in many regards very similar to fork 1-b, since all data access from CLR procedures is through generated SQL strings, parameterised or unparameterised. If you have settled on SQL procedures for your application, there is little point in rewriting them into the CLR. However, CLR code can be a valuable supplement for tasks that are difficult to perform in T-SQL, but you yet want to perform server-side.
Introducing Dynamic SQL
In this chapter I will first look at some quick examples of dynamic SQL and point out some very important implications of using dynamic SQL. I will then describe sp_executesql and EXEC() in detail, the two commands you can use to invoke dynamic SQL from T-SQL.
A First Encounter
Understanding dynamic SQL itself is not difficult. Au contraire, it's rather too easy to use. Understanding the fine details, though, takes a little longer time. If you start out using dynamic SQL casually, you are bound to face accidents when things do not work as you have anticipated.
One of the problems listed in the introduction was how to write a stored procedure that takes a table name as its input. Here are two examples, based on the two ways to do dynamic SQL in Transact-SQL:
CREATE PROCEDURE general_select1 @tblname sysname,
@key varchar(10) AS
DECLARE @sql nvarchar(4000)
SELECT @sql = ' SELECT col1, col2, col3 ' +
' FROM dbo.' + quotename(@tblname) +
' WHER
E keycol = @key'
EXEC sp_executesql @sql, N'@key varchar(10)', @key
CREATE PROCEDURE general_select2 @tblname nvarchar(127),
@key varchar(10) AS
EXEC('SELECT col1, col2, col3
FROM ' + @tblname + '
WHERE keycol = ''' + @key + '''')
Before I say anything else, permit me to point out that these are examples of bad usage of dynamic SQL. Passing a table name as a parameter is not how you should write stored procedures, and one aim of this article is to explain this in detail. Also, the two examples are not equivalent. While both examples are bad, the second example has several problems that the first does not have. What these problems are will be apparent as you read this text.
Whereas the above looks very simple and easy, there are some very important things to observe. The first thing is permissions. You may know that when you use stored procedures, users do not need permissions to access the tables accessed by the stored procedure. This does not apply when you use dynamic SQL! For the procedures above to execute successfully, the users must have SELECT permission on the table in @tblname. In SQL 2000 and earlier this is an absolute rule with no way around it. SQL 2005 provides alternative ways, something I will come back to in the section The Permission System.