By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,856 Members | 2,179 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,856 IT Pros & Developers. It's quick & easy.

Where is error-I need exec in function - result is without error,but not return data

P: 1
create function dbo.fnc_varchar_columns_alltrim
()
returns @vysledek table
( tabulka sysname null,
sloupec sysname null,
pocet_zaznamu int null,
datovy_typ varchar(256) null
)
as

begin

declare @sql_text nvarchar(2000),
@table_name varchar(256),
@column_name varchar(256),
@dat_typ varchar(256),
@celkem int,
@int_objSQL int,
@int_erros int,
@int_objSelectCountResult int,
@bint_SelectCount bigint


declare @cislo table
( pocet int
)

declare @pom table
( table_name sysname,
column_name sysname,
datovy_typ varchar(256)
)

insert into @pom
select o.name as table_name, c.name as column_name, t.name as datovy_typ
from sys.objects o
left join sys.columns c on c.object_id = o.object_id
left join sys.types t on t.user_type_id = c.user_type_id
where o.type = 'U' and o.name not in ('cVykazPriloha','cVysledovka','cRozvaha','dtprope rties') and o.name not like 'rep%'
and c.user_type_id in (167,231) and c.name not in ('key')
and o.name = 'adm_DB_version' and c.name = 'version'
order by o.name

while exists (select 1 from @pom)
begin
select top 1 @table_name=table_name,@column_name=column_name,@d at_typ=datovy_typ from @pom
delete from @cislo
exec @int_erros = sp_OACreate 'SQLDMO.SQLServer', @int_objSQL OUTPUT
exec @int_erros = sp_OASetProperty @int_objSQL, 'LoginSecure', TRUE
exec @int_erros = sp_OAMethod @int_objSQL, 'Connect', null, '.'
set @sql_text = 'select count(*) from dbo.' + @table_name + ' where len(' + @column_name+ ') <> len(ltrim(rtrim(' + @column_name+ ')))'
set @sql_text = 'ExecuteWithResults("' + @sql_text + '")'
exec @int_erros = sp_OAMethod @int_objSQL, @sql_text, @int_objSelectCountResult OUTPUT
exec @int_erros = sp_OAMethod @int_objSelectCountResult, 'GetRangeString(1, 1)', @bint_SelectCount OUT
exec @int_erros = sp_OADestroy @int_objSQL
set @celkem = @bint_SelectCount
if @int_erros <> 0
exec sp_OAGetErrorInfo @int_objSQL
if @celkem > 0
insert into @vysledek (tabulka,sloupec,pocet_zaznamu,datovy_typ) values (@table_name,@column_name,@celkem,@dat_typ)
delete top (1) from @pom
end

return

end

go
Sep 3 '09 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.