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 Adaptiver 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 sysobject table to find the required information?
The key to database programming is to work effeciently 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 statments 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 -
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 -
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, thats all for now will be back with more cool facts and topics sometime later...
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 sysobject table to find the required information?
The key to database programming is to work effeciently 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 statments 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 -
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 -
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, thats all for now will be back with more cool facts and topics sometime later...
This comment has been removed by the author.
ReplyDeleteHi Daniel, exec works for Sybase ASE version however for Sybase IQ you need to use the execute immediate to execute dynamic sql. I hope this helps.
ReplyDelete