One of the most common questions we get involves Dynamic SQL. We have some articles that cover it but none that really start with the basics. So
Merkin sat down and wrote this introduction to dynamic SQL. Using dynamic SQL you can put a SQL statement inside a variable and execute that statement. It's what you have to do when you're trying to run Select * from @TableName. Thanks
Merkin!
Laptop Battery Dynamic SQL is a term used to mean SQL code that is generated programatically (in part or fully) by your program before it is executed. As a result it is a very flexable and powerful tool. You can use dynamic sql to accomplish tasks such as adding where clauses to a search based on what fields are filled out on a form or to create tables with varying names.
Get all the benefits of Microsoft SQL Server 7 with none of the headaches. in wizards, the Enterprise Manager, and the Query Analyzer. use stored procedures, save time by automating common tasks with Jobs and Alerts, import and export data to and from an SQL Server database, and keep your data secure and backed up with the tips, tricks, and techniques you'll discover inside Microsoft SQL Server 7 For Dummies. have SQL Server tools, too, including programs for automatically building multitier applications, creating SQL Server objects, friendly Visual Basic code.
Thinkpad In part one of this two part series I will introduce you to dynamic SQL and give you some simple examples. In part two I will explain some more advanced uses for it and answer a lot of the questions we get in the forums. Dynamic SQL on the client
Computer memory is the quickest, cheapest, and easiest way to improve the performance of your system. Find RAM memory upgrades for desktops, laptops, servers, and printers all backed by a lifetime warranty and guaranteed compatible with your computer. Shipping is an everyday low price of $1.99! Computer Memory Outlet sells memory compatible with all leading computer manufacturers like Dell, Apple, Compaq, HP, Sony, IBM, Lenovo, and many more.”
Microsoft If you are an ASP developer you would be already familiar with the concept of dynamic SQL. How may times have you done something like this:
dim sql sql = "Select ArticleTitle, ArticleBody FROM Articles WHERE ArticleID = " sql = sql & request.querystring("ArticleID") set results = objConn.execute(sql)
sqlservr.exe is the main executable relating to Microsoft's SQL Server Suite. This server application provides industry standard and flexible SQL server services. This program is important for the stable and secure running of your computer and should not be terminated. Check that sqlservr.exe is stable on your computer.
Laptop Computers or slightly more elaborate
dim sql sql = "Insert into Users (UserName, FirstName, LastName, EMailAddress) " sql = sql & "Values('" & request.form("UserName") & "', '" & request.form("FirstName") sql = sql & "', '" & request.form("LastName") & "', '" & request.form("EmailAddress") & "')" objConn.execute(sql)
He runs the local SQL Server users group in Jacksonville (JSSUG) and was on the Board of Directors of the Professional Association for SQL Server (PASS).
Laptop Computer Or for a generic table viewer
dim sql sql = "Select * from " & request.querystring("TableName") set results = objConn.execute(sql)
Errata Description Chapter (PDF) Table of Contents (PDF) Index (PDF) Author Information Complete. Authoritative. Practical. The only SQL Server book you need. Mastering SQL Server 2000 is the one indispensable resource for anyone working with the latest version of SQL Server. Whether you build or administer SQL Server databases or write applications that communicate with them, you'll find the background knowledge and the practical instruction you need to accomplish any task, from the most basic to the most advanced.
Desktop Computer In each case, you are building your sql statement as a string, then executing that statement against an active database connection. Dynamic SQL in a stored procedure
Notebooks Once you move into the realm of stored procedures, you move away from this style of coding. Instead you would create a procedure with an input parameter.
Create Procedure GetArticle @ArticleID int AS Select ArticleTitle, ArticleBody FROM Articles WHERE ArticleID = @ArticleID GO
Lenovo However, SQL Server doesn't like certain things being passed as parameters, object names are a good example. If you try the third example in a stored proc such as:
Create Procedure GenericTableSelect @TableName VarChar(100) AS SELECT * FROM @TableName GO
Hard Drive You will get an error. To get around such restrictions we can use dynamic SQL. We will follow the same logic here, build a string, then execute it.
Create Procedure GenericTableSelect @TableName VarChar(100) AS Declare @SQL VarChar(1000) SELECT @SQL = 'SELECT * FROM ' SELECT @SQL = @SQL + @TableName Exec ( @SQL) GO
Travelstar Try that. That should do it.
Gateway The downside of this method is twofold. Firstly, and most importantly, your stored procedure can not cache the execution plan for this dynamic query. So, for complex queries you will lose a the performance boost that you usually gain with stored procedures.
Laptop Parts The other downside, IMHO, is that you lose the nice formating you are able to achieve with stored procedures that you were not able to do when you were building queries in ASP.
Software The advantage is, of course, that you are able to achive a flexability in your code that you can not get with standard SQL.
Hard Drives That wraps up part one. Hopefully you now have an idea of what dynamic SQL is and why you would want to use it. In part two I will demonstrate some more complex and real world examples as well as some techniques for caching and speeding up dynamic queries.
Electronics Until then have fun.
[ Comment, Edit or Article Submission ]
