In a follow up to the Introduction to Dynamic SQL we take you through some of the tricks and pitfalls of this technique. We'll cover Create Table, sp_executesql, permissions, scope and more.
Laptop Battery Welcome to Part 2 of my Introduction to Dynamic SQL.
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 I explained what Dynamic SQL is and how to use it. In this article I will show some more useful applications for it and a few tricks. The IN Clause
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 The IN clause is a good example of a use for Dynamic SQL. A lot of SQL Server developers use ASP or a similar web scripting language.
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 If in an asp page you have a Select list with multiple allowed values, the value of request.form("myList") on the processing page might look like this "1,3,4,6".
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 So we try to write a stored proc around this
Create Procedure Search @strIDs VarChar(100) AS SELECT * FROM Products WHERE ProductID in (@strIDs) GO
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 Oooops! No Go.
Notebooks This will work
Create Procedure Search @strIDs VarChar(100) AS Declare @SQL VarChar(1000) Select @SQL = 'SELECT * FROM Products ' Select @SQL = @SQL + 'WHERE ProductID in (' + @strIDs +')' Exec ( @SQL) GO
Lenovo N.B. This can also be solved using a technique like this . Aliases
Hard Drive Giving a table or column a dynamic alias is a use for dynamic SQL.
Travelstar This will not work
Select UserName FROM Table as @Alias
Gateway This will
Exec('Select UserName FROM Table as ' @Alias) DDL
Laptop Parts A common question asked of SQL Team is "How do I write a stored procedure that will create a table/database. I want to pass in the name"
Software SQL Server will not allow this
Create Table @TableName ( ID int NOT NULL Primary Key, FieldName VarChar(10) )
Hard Drives Once again, dynamic SQL to the rescue
Declare @SQL VarChar(1000) SELECT @SQL = 'Create Table ' + @TableName + '(' SELECT @SQL = @SQL + 'ID int NOT NULL Primary Key, FieldName VarChar(10))' Exec (@SQL)
Electronics Similarly, the code to create a database would look like this:
Exec('Create Database ' + @myDBName) sp_executesql
Canon sp_executesql is a system stored procedure that you can use in place of "exec" to execute your dynamic sql.
Desktop Pc This allows you to have parameters in your dynamic query and pass them in. The end result is that SQL Server will try to cache the execution plan for your query giving you some of the advantages of a fully compiled query.
Desktop Computers An example
Declare @SQL nVarChar(1000) --N.B. string must be unicode for sp_executesql SELECT @SQL = 'SELECT * FROM pubs.DBO.Authors WHERE au_lname = @AuthorName' Exec sp_executesql @SQL, N'@AuthorName nVarChar(50)', @AuthorName = 'white'
Think Pad The first parameter here is the SQL statement, then you must declare the parameters, after that you pass the in parameters as normal, comma separated.
Repair sp_executesql is also useful when you want to execute code in another database as it will run code in the context of it's database, rather than the one it was called from.
Data Recovery Try this from a database that is not Pubs
Create View pubs.dbo.Auths AS (SELECT au_id, au_lname, au_fname FROM Authors)
Cisco You will get this error: 'CREATE VIEW' does not allow specifying the database name as a prefix to the object name.
Keyboard So you build the dynamic sql, then run it in Pub's copy of sp_executesql
Monitor I.E.
Declare @SQL nVarChar(1000) Select @SQL = 'Create View Auths AS (SELECT au_id, au_lname, au_fname FROM Authors)' Execute pubs.dbo.sp_executesql @sql
Permissions
Desktop When executing dynamic SQL from a stored procedure, keep in mind that the SQL is executed in the permission context of the user, not the calling procedure. This means that if your user has no rights to the tables, only to the procedure, you may run into problems. Scope
Infosys When you run dynamic sql, it runs in it's own scope.
Refurbished Laptops This
exec('set rowcount 3') Select * from Authors exec('set rowcount 0')
Wipro Will have no effect on the result set returned from Authors. This is because by the rowcount statements have gone out of scope by the time the Select occurs.
Lap Top This would be solved by this
exec('set rowcount 3 Select * from Authors Set rowcount 0')
Refurbished Declaring variables inside a dynamic SQL batch will also not be available outside the batch and vice versa. As a result, this would also not work.
declare @i int Exec ('Select @i = 1')
Memory Temp tables can be used to interact between batches of standard SQL and dynamic SQL. A temp table created within a dynamic SQL batch will be destroyed when the batch completes, however a temp table created before the batch will be available to it.
Create Table #tempauth( au_id VarChar(100), au_fname VarChar(100), au_lname VarChar(100) ) declare @SQL VarChar(1000) Select @SQL = 'Insert into #tempauth Select au_id, au_fname, au_lname FROM Authors' exec(@SQL) Select * from #tempauth drop table #tempauth
Summary
Intel That wraps up my intro to dynamic SQL. I hope it is a little clearer now than it was before. Dynamic SQL is a very powerful tool to have in your arsenal as long as it doesn't substitute for bad application design. If you can avoid it with better data modelling, then that is the best way to go as your code will end up neater and generally faster.
As400 Until next time
Averatec Happy coding.
[ Comment, Edit or Article Submission ]
