March 25, 2013 at 2:58 pm
I have the follwing line generating a where condition for me in my dynamic sql statement
(select cast(sys.all_columns.name as nvarchar) + ' = ''1900-01-01 00:00:00.000'' or ' from sys.all_columns where sys.all_columns.object_id = ac.object_id and sys.all_columns.system_type_id = 61 for xml path ('')) as Conditions
which generates something like
if exists (select top 1 * from [Address] where createdDT = '1900-01-01 00:00:00.000' or lastModifiedDT = '1900-01-01 00:00:00.000')
Seem good so far?
If I change the code to a less than or equal to like this:
(select cast(sys.all_columns.name as nvarchar) + ' <= ''1900-01-01 00:00:00.000'' or ' from sys.all_columns where sys.all_columns.object_id = ac.object_id and sys.all_columns.system_type_id = 61 for xml path ('')) as Conditions
I get THIS in my now NON-Executable statement
if exists (select top 1 * from [Address] where createdDT lt;= '1900-01-01 00:00:00.000' or lastModifiedDT = '1900-01-01 00:00:00.000')
What am I missing?
March 25, 2013 at 3:05 pm
The FOR XML is tokenizing the greater than sign (>). Would need to see all the code to actually help you.
March 25, 2013 at 3:16 pm
Here you go Lynn.
declare @strSQL nvarchar(max)
declare @tblName nvarchar(255)
declare @columnList nvarchar(255)
declare @conditions nvarchar(1000)
declare queryList cursor local static forward_only read_only for
select source.name, left(source.dtcolumns, len(source.dtcolumns)-1) dtColumns, left(source.conditions, len(source.conditions)-3) dtConditions
from
(
select st.name
, (select sys.all_columns.name + ', ' from sys.all_columns where sys.all_columns.object_id = ac.object_id and sys.all_columns.system_type_id = 61 for xml path ('')) as dtColumns
, (select cast(sys.all_columns.name as nvarchar) + ' = ''1900-01-01 00:00:00.000'' or ' from sys.all_columns where sys.all_columns.object_id = ac.object_id and sys.all_columns.system_type_id = 61 for xml path ('')) as Conditions
from sys.all_columns ac
inner join sys.tables st
on ac.object_id = st.object_id
where ac.system_type_id = 61
) source
group by source.name, source.dtcolumns, source.conditions
open queryList
Fetch next from queryList into @tblName, @columnList, @conditions
while @@fetch_status = 0
begin
set @strSQL = 'if exists (select top 1 * from [' + @tblName + '] where ' + @conditions + ') select top 1 *, ''' + @tblname + ''' from [' + @tblName + '] where ' + @conditions + ';'
print @strSQL
-- exec (@strSQL)
Fetch next from queryList into @tblName, @columnList, @conditions
end
close queryList
deallocate queryList
So far I've gone with the simplest brute force solution.
set @strSQL = 'if exists (select top 1 * from [' + @tblName + '] where ' + replace(@conditions,'<','<') + ') select top 1 *, ''' + @tblname + ''' from [' + @tblName + '] where ' + replace(@conditions,'<','<') + ';'
And of course it's detokenizing my less than sign here in my post.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy