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