You must Sign In to post a response.
  • Category: ASP.NET

    How to fetch row one by one in sql

    I am fetching tablename from perticular database like this

    SELECT name FROM sys.Tables where name like 'some pattern'

    Output :
    Name
    sampletable_123,
    sampletable_456,
    sample_789.
    It can give me more than one row like above output. How to fetch row one by one. Because I want to find out columnname/date from obtained table name.If date is less than current date,I want to delete/drop that table.

    select top 1 Udate from sampletable_123
    where Udate < convert(varchar(10),getdate(),101)

    delete table sampletable_123.How to do that?
  • #762532
    Hi,

    If return the data more than one row then use do, while and iterate each and every row, and against row(tablename) you can get the result if the date is less than today then drop the table like below.

    What is the datatype of "Udate".?

    If that is DATETIME, then use below sample


    declare @tab table
    (
    Empid int,
    sal int,
    DOJ Datetime
    )

    insert into @tab
    values(1,23444,getdate()-4),(2,34324,getdate()-5),(3,34234,getdate()+4),(4,4343,getdate())

    Declare @cnt int=0;

    select @cnt = COUNT(*) from @tab
    where convert(Date,DOJ) < Convert(Date,getdate())
    Having Count(*)=0

    select @cnt

    if @cnt = 0
    --drop table here


    While compare any type of data, in both left & right hand side should be in same format.

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/


Sign In to post your comments