What Is Dynamic SQL?

All topics about programming / development in T-SQL or other languages for SQL Server.
Post Reply
DaveP5
Posts: 29
Joined: Sat Jun 22, 2019 7:58 pm
Answers: 1

Mon Jun 24, 2019 2:32 pm

Many times database developers are required to write queries based on data which is not present at the time of creation of scripts/stored procedures etc and Dynamic SQL is a way by which the Adaptive Server Enterprise allows us to write such queries.

Well you might be thinking whether such situation may actually occur or is it just another R&D work we are trying to do here? So, now we will try to explain when you might be hit on with such a condition. Lets say, we are asked to find which database a particular table name or stored procedure exists and lets say we have around 10 databases to work on including default Sybase databases, so what will you do in such a simple case. Will you try to run a query on sysobjects table for one and for all databases one at a time, or you will try to specifically create 10 different queries to prefix database name in front of each database's sysobjects table to find the required information?

The key to database programming is to work efficiently so that the required information is at hand there and then... Never too late, never too early but just in time... So to get this information we will use Dynamic SQL statements to hit on our system tables at runtime with appropriate amendments and we are done with this job.

In case we would have been required to extract this information from a single database then the required sql would have been -

Code: Select all

select * from sysobjects where name like '%ObjectName%' and type in ('U', 'P')
go
Here ObjectName is the database object which we want to find using this sql. We have used like in the statement to just insure that we find all the database objects which also match the required name (just to make sure that our user is not forgetting the exact database object name).

Now as we have to use this query for all the different databases hence we will write the following dynamic sql within a stored procedure to get the required result -

Code: Select all

create procedure rsp_ObjectFinder
@ObjectName varchar(35)
as
begin

declare
@DBName varchar(35),
@MySQL varchar(255),
@Counter int,
@CountLimit int

create table #MyTable
(
Id numeric(10,0) Identity,
DBName varchar(35)
)

insert into #MyTable (DBName)
select Name from master..sysdatabases

select
@CountLimit = @@rowcount,
@Counter = 1

while (@Counter <= @CountLimit)
begin

select @DBName = DBName from #MyTable where Id = @Counter

select @MySQL = 'select ''' + @DBName + ''' DatabaseName,* from ' + @DBName + '..'+'sysobjects where name like ''%' + @ObjectName + '%''and type in (''P'', ''U'')'

exec(@MySQL)

select @Counter = @Counter + 1

end

end
go
As we can see from the above example we have been able to query all the databases for certain database object. This example stored procedure can be further refined to incorporate many more features. Well, that's all for now will be back with more cool facts and topics sometime later...
_skye.kaptin_
Posts: 1
Joined: Fri Jul 19, 2019 6:59 am

Fri Jul 19, 2019 7:27 am

This is a good article. However, I would point out a couple of problems.

First, this code is injectable. The @ObjectName parameter is never validated to ensure that it does not have executable SQL code in it. This is the famous "Little Bobby Tables" problem of xkcd fame - SQL injection.

There are two ways to address this problem. First, you can use the QUOTENAME function to escape the @ObjectName argument. This will ensure that it will be interpreted as a valid object identifier. Second, and in my opinion preferably, you can pass the @ObjectName argument directly to sp_executesql as a string parameter, using prepared SQL instead of dynamic SQL. You should still use QUOTENAME to sanitize the names of the databases before concatenating them into @MySQL.

The second thing I want to point out is that the proper data type for any object identifier in SQL Server is sysname, which currently is the same as nvarchar(128). That would be better to use for object names and database names. Otherwise if you want to find an object with a name longer than 35 characters, or if one of your database names is more than 35 characters, bad things may happen. Just use sysname and your scripts will work properly, even if the definition of sysname changes (which had happened in the past).

Please don't take my comments as nitpicking. I only took this much time to respond because I thought it was worth it - your writing style is excellent.
DaveP5
Posts: 29
Joined: Sat Jun 22, 2019 7:58 pm
Answers: 1

Fri Jul 19, 2019 7:43 am

Hey @_skye.kaptin_ thank you very much for your contribution, all great valid points!
jayapriya
Posts: 1
Joined: Tue Aug 27, 2019 9:31 am

Tue Aug 27, 2019 9:32 am

Dynamic SQL is a programming technique that enables you to build SQL statements dynamically at runtime. You can create more general purpose, flexible applications by using dynamic SQL because the full text of a SQL statement may be unknown at compilation.
AWS online training | AWS certification training
Post Reply

Social Media