表tbluser
ID Name ExpireDate
-----------------------------------
1 小A 2010-12-08
2 小B 2010-02-19
3 小C 2011-01-30
4 小D 2010-09-19
5 小E 2010-03-04我想用一个存储过程,判断ExpireDate是否小于今天,如果是,就插入到另一张表
老师要求用循环做,循环内用insert into动作分别插入,列只有Name,大虾们帮帮我吧。感激不尽!!
表tblexp
Name
------
小B
小D
小E
ID Name ExpireDate
-----------------------------------
1 小A 2010-12-08
2 小B 2010-02-19
3 小C 2011-01-30
4 小D 2010-09-19
5 小E 2010-03-04我想用一个存储过程,判断ExpireDate是否小于今天,如果是,就插入到另一张表
老师要求用循环做,循环内用insert into动作分别插入,列只有Name,大虾们帮帮我吧。感激不尽!!
表tblexp
Name
------
小B
小D
小E
解决方案 »
- SQL SERVER2005当前最新的版本号
- SQL查询语句,困扰我大半天了,求帮助
- select 姓名,cast(身份证号 as char(18)) as [身份证号]在查询分析器中操作,然后导成csv文件,可是身份证号变成科学计数法了,如何办?
- 发生错误2 系统找不到指定文件 mssqlserver server服务上执行该服务操作???
- 求一SQL语句的写法,.....................................
- 不启动SQL服务,如何更改密码 ,谢谢。。
- 用什么排序规则可以支持中文比较?
- [SQL Server]已将数据库上下文改为'test'什么意思?
- sql server 2000设置什么参数可以是100万个用户同时登陆它? 参数设什么值?
- 如何使用SQL Server的Full-Text Search?
- 高手们,讨论个数据表结构
- 帮忙改下sql语句:mssql收缩用户数据库日志
--> 数据库版本:
--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
--> 测试数据:[TB]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB]')
AND type in (N'U'))
DROP TABLE [TB]
GO---->建表
create table [TB]([ID] int,[Name] varchar(3),[ExpireDate] datetime)
insert [TB]
select 1,'小A','2010-12-08' union all
select 2,'小B','2010-02-19' union all
select 3,'小C','2011-01-30' union all
select 4,'小D','2010-09-19' union all
select 5,'小E','2010-03-04'
GO--> 查询结果
SELECT Name FROM [TB] WHERE ExpireDate<GETDATE()
--> 删除表格
--DROP TABLE [TB]我不会循环
--> 数据库版本:
--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
--> 测试数据:[TB]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB]')
AND type in (N'U'))
DROP TABLE [TB]
GO---->建表
create table [TB]([ID] int,[Name] varchar(3),[ExpireDate] datetime)
insert [TB]
select 1,'小A','2010-12-08' union all
select 2,'小B','2010-02-19' union all
select 3,'小C','2011-01-30' union all
select 4,'小D','2010-09-19' union all
select 5,'小E','2010-03-04'
GOdrop table TName
create table TName(name varchar(20))
go
--> 查询结果
declare @name varchar(20)
declare ds cursor
for
SELECT Name FROM [TB] WHERE ExpireDate<GETDATE()
open ds
fetch ds into @name
while @@FETCH_STATUS=0
begin
insert into TName(name) select @name
fetch ds into @name
end
close ds
deallocate ds
--> 删除表格
--DROP TABLE [TB]select * from TName
AS
BEGIN
DECLARE @name nvarchar(30)
DECLARE @date datetime
DECLARE cur CURSOR FOR SELECT Name ,ExipreDate from tbluser
OPEN cur
FETCH NEXT FROM cur INTO @name,@date
WHILE @@FETCH_STATUS = 0
BEGIN
IF ( DATEDIFF(day,@date,GETDATE())>0 )
INSERT INTO tblexp (Name) values (@Name)
FETCH NEXT FROM cur INTO @name,@date
END
CLOSE cur
DEALLOCTE cur
END;
create proc p_1
as
begin
set nocount on
declare @i int
declare @rowcount int
select @i=1
select @rowcount = count(1) from tbluser
begin try begin tran ts while(@i <= @rowcount)
begin
insert into [tablename](name) select name from tbluser where id = @i select @i = @i + 1 end commit tran ts end try
begin catch
rollback tran ts
end catchend
go
create proc p_1
as
begin
declare @i int
declare @n int
select @i=1
select @n= max(id) from tbluser
while(@i <= @n)
begin
if exists(select 1 from tbluser where id = @i and ExpireDate<getdate())
begin
insert into tblexp select name from tbluser where id = @i
end
select @i = @i + 1
end
end
我照着fpzgm写了一个
create proc p_2
as
begin
declare @i int
set @i=select count(name) from tbluser where expiredate<getdate()
--为什么上面这句不行?把得到的值赋给i
select name from tbluser where expiredate<getdate()
--执行循环到i的操作
end
(
id int,
name nvarchar(20),
expiredate datetime
)
insert into tbluser values(1,'小A','2010-12-08')
insert into tbluser values(2,'小B','2010-02-19')
insert into tbluser values(3,'小C','2011-01-30')
insert into tbluser values(3,'小D','2010-09-19')
insert into tbluser values(3,'小E','2010-03-04')
create proc p_2
as
begin
declare @i int
select @i=count(name) from tbluser where expiredate<getdate()
--为什么上面这句不行?把得到的值赋给i
select name from tbluser where expiredate<getdate()
--执行循环到i的操作
end
as
begin
declare @i int
set @i=(select count(name) from tbluser where expiredate<getdate())
--为什么上面这句不行?把得到的值赋给i
select name from tbluser where expiredate<getdate()
--执行循环到i的操作
end或者这样
select name from tbluser where expiredate<getdate()
老师布置的作业 不会啊 大哥有什么好办法吗?
create table TName(name varchar(20))
go
--> 查询结果
declare @name varchar(20)
declare ds cursor
for
SELECT Name FROM [TB] WHERE ExpireDate<GETDATE()
open ds
fetch ds into @name
while @@FETCH_STATUS=0
begin
exec sp_aaa @name
fetch ds into @name
end
close ds
deallocate ds
--> 删除表格
--DROP TABLE [TB]select * from TName
create proc p_2
as
begin
declare @i int
select @i=count(name) from tbluser where expiredate<getdate()
select name from tbluser where expiredate<getdate()while(@i>0)
--i为总条数,name列为结果,可是计数器变量怎么设定呢?
select @i = @i - 1end
exec sp_aaa @name 这里不知道写什么了 name[@i]
select @i = @i - 1
找不到方法啊
游标不会用