如何在sql中逐个获取行 [英] How to fetch row one by one in sql

问题描述

我从这样的特定数据库中获取表名

I am fetching table-names from particular database like this

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

输出：

Output :

Name
sampletable_123,
sampletable_456,
sample_789.

它可以返回多行，如上面的输出。如何逐个获取行？



因为我想从获得的表名中找出列名/日期。如果date小于当前日期，我想删除该表。

It can return more than one row like above output. How to fetch row one by one?

Because I want to find out column-name/date from obtained table-name. If date is less than current date, I want to drop that table.

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

推荐答案

在您的代码中使用循环，或者如果您想在SQL中执行它，那么您可以使用光标 [ ^ ]或替代游标 [ ^ ]

Either use a loop in your code behind, or if you want to do it within the SQL then you could use a cursor[^] or an alternative to cursor[^]

CHill说的是正确的。我可以从select语句创建一个命令并使用exec sql运行它：

What CHill says is correct. I might create a command from a select statement and run it using exec sql:

declare @Sql nvarchar(max)

set @Sql = null

SELECT  @Sql = coalesce(@Sql + ';'+char(13)+char(10),'')+
                   'if exists(select * from '+name+' where update < convert(varchar(10),getdate(),101)) '+char(13)+char(10)+
				   '    delete table '+ name
FROM sys.Tables
where 1=1

print @Sql

exec @sql

但是有也是你问题的字面答案：

（摘自 MSDN：使用DataReader检索数据 [ ^ ]）

But there is also a literal answer to your question:
(taken from MSDN: Retrieving Data Using a DataReader[^])

static void HasRows(SqlConnection connection)
{
    using (connection)
    {
        SqlCommand command = new SqlCommand(
          "SELECT CategoryID, CategoryName FROM Categories;",
          connection);
        connection.Open();

        SqlDataReader reader = command.ExecuteReader();

        if (reader.HasRows)
        {
            while (reader.Read())
            {
                Console.WriteLine("{0}\t{1}", reader.GetInt32(0),
                    reader.GetString(1));
            }
        }
        else
        {
            Console.WriteLine("No rows found.");
        }
        reader.Close();
    }
}

如果返回数据多行然后使用do，while并迭代每一行，而对于row（tablename），如果日期小于今天，你可以得到结果，然后像下面那样删除表。



Udate的数据类型是什么。？



如果是DATETIME，请使用下面的示例

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.

这篇关于如何在sql中逐个获取行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！