没写过存储过程,SQL也基本是自学的
1:定义的SQL语句变量打印不出来
2:执行后显示命令已成功执行 但是数据库表中没有数据插入和更新代码:
USE [CMP1234]
GO
--==============================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[proc_GetCheckTime](@DATE VARCHAR(10)='')AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT @DATE=convert(varchar(10),getdate(),120)
-- Insert statements for procedure here
DECLARE @pid varchar(20);
DECLARE @time varchar(20);
DECLARE @ctime varchar(20);
DECLARE @sql varchar(4000);
DECLARE @sql1 varchar(4000);
DECLARE @sql2 varchar(4000);
DECLARE rs CURSOR LOCAL SCROLL FOR
select DISTINCT pin from [zktime8.5].[dbo].[checkinout] where convert(varchar(10),checktime,120) = @DATE
OPEN rs
WHILE @@FETCH_STATUS=0--取值
BEGIN
FETCH NEXT FROM rs INTO @pid
SELECT @ctime=CONVERT(varchar(100), GETDATE(), 120);
SET @sql1='INSERT INTO c_table364 (creator,createtime,org,lockstatus,cfield3)'+
'VALUES (''admin'','+@ctime+',''org1'',1,'+@pid+')';
print @sql1;
EXEC (@sql1);
SELECT @time=min(CONVERT(varchar(100), checktime, 24)) from [zktime8.5].[dbo].[checkinout] where pin=@pid and convert(varchar(10),checktime,120) = @DATE;
SET @sql2=' UPDATE c_table364 SET cfield8='+@time;
print @sql2;
EXEC (@sql2);
END
CLOSE rs;
DEALLOCATE rs;
END
GO
1:定义的SQL语句变量打印不出来
2:执行后显示命令已成功执行 但是数据库表中没有数据插入和更新代码:
USE [CMP1234]
GO
--==============================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[proc_GetCheckTime](@DATE VARCHAR(10)='')AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT @DATE=convert(varchar(10),getdate(),120)
-- Insert statements for procedure here
DECLARE @pid varchar(20);
DECLARE @time varchar(20);
DECLARE @ctime varchar(20);
DECLARE @sql varchar(4000);
DECLARE @sql1 varchar(4000);
DECLARE @sql2 varchar(4000);
DECLARE rs CURSOR LOCAL SCROLL FOR
select DISTINCT pin from [zktime8.5].[dbo].[checkinout] where convert(varchar(10),checktime,120) = @DATE
OPEN rs
WHILE @@FETCH_STATUS=0--取值
BEGIN
FETCH NEXT FROM rs INTO @pid
SELECT @ctime=CONVERT(varchar(100), GETDATE(), 120);
SET @sql1='INSERT INTO c_table364 (creator,createtime,org,lockstatus,cfield3)'+
'VALUES (''admin'','+@ctime+',''org1'',1,'+@pid+')';
print @sql1;
EXEC (@sql1);
SELECT @time=min(CONVERT(varchar(100), checktime, 24)) from [zktime8.5].[dbo].[checkinout] where pin=@pid and convert(varchar(10),checktime,120) = @DATE;
SET @sql2=' UPDATE c_table364 SET cfield8='+@time;
print @sql2;
EXEC (@sql2);
END
CLOSE rs;
DEALLOCATE rs;
END
GO
执行一下试下
这个的结果看看。 如果没有输出结果是不会进入游标处理的。
OPEN rs
--此处是不是该fetch一下呢?
FETCH NEXT FROM rs INTO @pid --类似于此类语句
WHILE @@FETCH_STATUS=0--取值
BEGIN
FETCH NEXT FROM rs INTO @pid
@DATE 需要加单引号 应该怎么加啊
谢谢
USE [MyCMP]
GO
/****** Object: StoredProcedure [dbo].[proc_GetCheckTime] Script Date: 11/05/2014 10:21:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[proc_GetCheckTime](@DATE VARCHAR(10)='')AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT @DATE=convert(varchar(10),getdate(),120)
-- Insert statements for procedure here
DECLARE @pid varchar(20);
DECLARE @time varchar(20);
DECLARE @ctime varchar(20);
DECLARE @sql varchar(4000);
DECLARE @sql1 varchar(4000);
DECLARE @sql2 varchar(4000); --set @sql='select DISTINCT pin from [zktime8.5].[dbo].[checkinout] where convert(varchar(10),checktime,120) ='''+@DATE+'''';
--print (@sql);
DECLARE rs CURSOR FOR
SELECT DISTINCT pin from [zktime8.5].[dbo].[checkinout] where convert(varchar(10),checktime,120) = '''+@DATE+''' ;
OPEN rs;
FETCH NEXT FROM rs INTO @pid;
print (@@FETCH_STATUS);
WHILE (@@FETCH_STATUS=0)--取值
BEGIN
SELECT @ctime=CONVERT(varchar(100), GETDATE(), 120);
SET @sql1='INSERT INTO c_table364 (creator,createtime,org,lockstatus,cfield3)'+
'VALUES (''admin'','+@ctime+',''org1'',1,'+@pid+')';
print @sql1;
EXEC (@sql1);
SELECT @time=min(CONVERT(varchar(100), checktime, 24)) from [zktime8.5].[dbo].[checkinout] where pin=@pid and convert(varchar(10),checktime,120) ='''+@DATE+''';
SET @sql2=' UPDATE c_table364 SET cfield8='''+@time+'''';
print @sql2;
EXEC (@sql2);
END
CLOSE rs;
DEALLOCATE rs;
END
执行的结果是-1 是哪里出错了吗?