Having Problem while retrieving multiple table nam

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Having Problem while retrieving multiple table nam

Author

Topic

maifs
Yak Posting Veteran

57 Posts

Posted - 2010-04-20 : 13:56:08

ALter Proc [dbo].[sp_sng_purging_all_transactions] @range_dte DATETIME = NULL

AS

Declare @format_converted_dte varchar(50)
Declare @table_nme varchar(100)
Declare @col_nme varchar(100)
Declare @resulting_year integer
Declare @counter integer

Begin

Declare Getting_All_Tables_Data
Cursor FOr

Select table_name,column_name
From Information_schema.columns
where

upper(column_name) = upper('execution_dte')
AND
upper(table_name) not like upper('%_vw%')
AND
upper(table_name) not like upper('%_view%')
AND
(upper(table_name) not like upper('%security%')
AND
upper(table_name) not like upper('%setup%')
)
OR
upper(table_name) not like upper('%admin%')
)

Open Getting_All_Tables_Data

set @counter = ( Select count (*) From Information_schema.columns

where

upper(column_name) = upper('execution_dte')
AND
upper(table_name) not like upper('%_vw%')
AND
upper(table_name) not like upper('%_view%')
AND
(upper(table_name) not like upper('%security%')
AND
upper(table_name) not like upper('%setup%')
)
OR
upper(table_name) not like upper('%admin%')
)

Fetch Next From Getting_All_Tables_Data into @table_nme, @col_nme

While @@FETCH_STATUS = 0

BEGIN

set @resulting_year = Dateif (year, @range_dte, getdate())

IF (exists (Select * from @table_nme where @col_nme = @range_dte) AND @resulting_year > = 7 AND @counter <= 1)

Begin

set @format_converted_dte = Convert (Date|Time, Convert (varchar, @range_dte,112),112);

exec('Delete From' + @table_nme + 'Where' + @col_nme + '=' + @format_converted_dte + '');

Fetch Next From Getting_ALll_Tables_Data into @table_nme, @col_nme ;

end

Else

Begin

return -1
END

END

Close Getting_All_Tables_Data
DEALLOCATE Getting_All_Tables_Data

return 1

END

Problem is occuring in this statement and duplicate records are coming.

IF (exists (Select * from @table_nme where @col_nme = @range_dte) AND @resulting_year > = 7 AND @counter <= 1)

maifs
Yak Posting Veteran

57 Posts

Posted - 2010-04-20 : 22:12:40

can anybody solve it ?

Problem is occuring in this statement and duplicate records are coming.

IF (exists (Select * from @table_nme where @col_nme = @range_dte) AND @resulting_year > = 7 AND @counter <= 1)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-21 : 01:39:22

what according to you are duplicates? you may be better off explaining your problem with some sample data and then show what you want

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources