One very beautiful feature of sql is the dynamic nature. Dynamic sql referes to code/script which can be used to opereate on different data-sets based on some dynamic values supplied by front-end applications. Imagine you are on a website like 2smart4school and you enter some comments in the form below. What do you think happens to the text you entered? and how come this text will always be on here unless deleted. Is is magic? off course, it’s not magic, it’s simply gets stored in the database. How does it get stored? simply, the front end application, which is the website will execute an insert statement on the database under the hood. How does the front end knows where to put what and which column receives what? This all bores down to the dynamic nature of sql. The next database object which can use to answer all this crap l’m asking is stored procedures or functions.
We’ll talk about this later but now, let’s concentrate on just Dynamic Sql. It can be used run a template sql query against different tables/columns/conditions. This is how it works. Declare variables: Which makes sql code dynamic, then construct strings consisting of query languages and execute the constructed string. Main disadvantage of DSQL is that we are opening SQL server for SQL injection attacks.
Let’s go into technical side of this game
DECLARE @col1 varchar(50), @col2 varchar(50), @table varchar(50),@columns varchar(255)
SET @col1 = ’emailaddress’
SET @col2 = ‘firstname’
SET @ col3 = ‘lastname’
SET @table = ‘person’
SET @columns = ‘@col1,@col2,@col3’
EXEC(‘SELECT ‘ +@col1 + ‘ , ‘ + @col2 + ‘ FROM ‘ + @table )
This is a dynamic query constructed and the EXEC function can be used to execute it on the fly. So the columns can change and the tables can change but this query will always work.
Look at another form below
EXEC(‘SELECT ‘ +@columns + ‘ FROM ‘ + @table )
This makes life so easy.
To see how your query looks before executing it, use the print function which will print the way the query looks from the construction. If you issue the command below,
Print ‘select ‘ +@col1 + ‘ , ‘ + @col2 + ‘ from ‘ + @table
This will show in the message column as
SELECT emailaddresss, firstname
FROM person
Remember you have to run this together with the variable declaration and Setting values section to get to to work or your server will be yelling that you didn’t declare the variables.
Dynamic SQL is pretty easy and please remember you have to use the EXEC function to run the query after concatenating all the strings. You can concatenate the enter query into one variable and then call the execute function on that variable.
Next tutorial will be on control structures. keep in touch. Peace.
