CREATE PROCEDURE PRO_Select_GM_DiaryD(
@LoginName varchar(15),
@GmName varchar(10),
@GMGroup varchar(15),
@GameName varchar(20),
@Gserver varchar(20),
@Distributary varchar(25),
@Logindate varchar(30),--登入时间
@Exitdate varchar(30),--登出时间
@GLoginname varchar(15),
@Grole varchar(25),
@Goperate varchar(20),
@IsertDate varchar(30)--写入时间
)
AS
BEGIN
DECLARE @sql varchar(500)
DECLARE @strWhere varchar(500)
set @strWhere=''
IF (@LoginName !='')
SET @strWhere=@strWhere+' and LoginName = '+char(39)+@LoginName+char(39)
IF (@GmName !='')
SET @strWhere=@strWhere+' and GmName = '+char(39)+@GmName+char(39)
IF (@GMGroup !='')
SET @strWhere=@strWhere+' and GMGroup = '+char(39)+@GMGroup+char(39)
if (@GameName!='')
SET @strWhere=@strWhere+' and GameName = '+char(39)+@GameName+char(39)
if (@Gserver!='')
SET @strWhere=@strWhere+' and Gserver = '+char(39)+@Gserver+char(39)
if (@Distributary!='')
SET @strWhere=@strWhere+' and Distributary = '+char(39)+@Distributary+char(39)
if (@Logindate!='')
SET @strWhere=@strWhere+' and Logindate >= '+char(39)+@Logindate+char(39)
if (@Exitdate!='')
SET @strWhere=@strWhere+' and Exitdate <= '+char(39)+@Exitdate+char(39)
if (@GLoginname!='')
SET @strWhere=@strWhere+' and GLoginname = '+char(39)+@GLoginname+char(39)
if (@Grole!='')
SET @strWhere=@strWhere+' and Grole = '+char(39)+@Grole+char(39)
if (@Goperate!='')
SET @strWhere=@strWhere+' and Goperate = '+char(39)+@Goperate+char(39)
if (@IsertDate!='')
SET @strWhere=@strWhere+' and IsertDate >= '+char(39)+@IsertDate+char(39)
SET @sql='select * from dbo.GM_Diary where Statur=0'+@strWhere +' order by IsertDate desc'
EXEC(@sql) END各位大侠,请问我如何拼写登入时间,登出时间,写入时间拼写后可以比较
@LoginName varchar(15),
@GmName varchar(10),
@GMGroup varchar(15),
@GameName varchar(20),
@Gserver varchar(20),
@Distributary varchar(25),
@Logindate varchar(30),--登入时间
@Exitdate varchar(30),--登出时间
@GLoginname varchar(15),
@Grole varchar(25),
@Goperate varchar(20),
@IsertDate varchar(30)--写入时间
)
AS
BEGIN
DECLARE @sql varchar(500)
DECLARE @strWhere varchar(500)
set @strWhere=''
IF (@LoginName !='')
SET @strWhere=@strWhere+' and LoginName = '+char(39)+@LoginName+char(39)
IF (@GmName !='')
SET @strWhere=@strWhere+' and GmName = '+char(39)+@GmName+char(39)
IF (@GMGroup !='')
SET @strWhere=@strWhere+' and GMGroup = '+char(39)+@GMGroup+char(39)
if (@GameName!='')
SET @strWhere=@strWhere+' and GameName = '+char(39)+@GameName+char(39)
if (@Gserver!='')
SET @strWhere=@strWhere+' and Gserver = '+char(39)+@Gserver+char(39)
if (@Distributary!='')
SET @strWhere=@strWhere+' and Distributary = '+char(39)+@Distributary+char(39)
if (@Logindate!='')
SET @strWhere=@strWhere+' and Logindate >= '+char(39)+@Logindate+char(39)
if (@Exitdate!='')
SET @strWhere=@strWhere+' and Exitdate <= '+char(39)+@Exitdate+char(39)
if (@GLoginname!='')
SET @strWhere=@strWhere+' and GLoginname = '+char(39)+@GLoginname+char(39)
if (@Grole!='')
SET @strWhere=@strWhere+' and Grole = '+char(39)+@Grole+char(39)
if (@Goperate!='')
SET @strWhere=@strWhere+' and Goperate = '+char(39)+@Goperate+char(39)
if (@IsertDate!='')
SET @strWhere=@strWhere+' and IsertDate >= '+char(39)+@IsertDate+char(39)
SET @sql='select * from dbo.GM_Diary where Statur=0'+@strWhere +' order by IsertDate desc'
EXEC(@sql) END各位大侠,请问我如何拼写登入时间,登出时间,写入时间拼写后可以比较
SET @strWhere=@strWhere+' and Logindate >= '''+char(39)+@Logindate+char(39)+''
@LoginName varchar(15),
@GmName varchar(10),
@GMGroup varchar(15),
@GameName varchar(20),
@Gserver varchar(20),
@Distributary varchar(25),
@Logindate DATETIME,--登入时间
@Exitdate DATETIME,--登出时间
@GLoginname varchar(15),
@Grole varchar(25),
@Goperate varchar(20),
@IsertDate DATETIME--写入时间
)
ASBEGIN
DECLARE @sql varchar(500)
DECLARE @strWhere varchar(500)
set @strWhere=''
IF (@LoginName !='')
SET @strWhere=@strWhere+' and LoginName = '+char(39)+CONVERT(VARCHAR(30),@LoginName,120)+char(39)
IF (@GmName !='')
SET @strWhere=@strWhere+' and GmName = '+char(39)+@GmName+char(39)
IF (@GMGroup !='')
SET @strWhere=@strWhere+' and GMGroup = '+char(39)+@GMGroup+char(39)
if (@GameName!='')
SET @strWhere=@strWhere+' and GameName = '+char(39)+@GameName+char(39)
if (@Gserver!='')
SET @strWhere=@strWhere+' and Gserver = '+char(39)+@Gserver+char(39)
if (@Distributary!='')
SET @strWhere=@strWhere+' and Distributary = '+char(39)+@Distributary+char(39)
if (@Logindate!='')
SET @strWhere=@strWhere+' and Logindate >= '+char(39)+@Logindate+char(39)
if (@Exitdate!='')
SET @strWhere=@strWhere+' and Exitdate <= '+char(39)+CONVERT(VARCHAR(30),@Exitdate,120)+char(39)
if (@GLoginname!='')
SET @strWhere=@strWhere+' and GLoginname = '+char(39)+@GLoginname+char(39)
if (@Grole!='')
SET @strWhere=@strWhere+' and Grole = '+char(39)+@Grole+char(39)
if (@Goperate!='')
SET @strWhere=@strWhere+' and Goperate = '+char(39)+@Goperate+char(39)
if (@IsertDate!='')
SET @strWhere=@strWhere+' and IsertDate >= '+char(39)+CONVERT(VARCHAR(30),@IsertDate,120)+char(39)
SET @sql='select * from dbo.GM_Diary where Statur=0'+@strWhere +' order by IsertDate desc'
EXEC(@sql)
END
go
日期傳參
'2011-10-13 15:27:20.300'
SET @strWhere=@strWhere+' and Logindate >= '''+char(39)+@Logindate+char(39)+''
if (@Exitdate!='')
SET @strWhere=@strWhere+' and Exitdate <= '''+char(39)+@Exitdate+char(39)+''有错误,报
if (@Logindate!='')
SET @strWhere=@strWhere+' and Logindate >= '''+@Logindate+''
if (@Exitdate!='')
SET @strWhere=@strWhere+' and Exitdate <= '''+@Exitdate+''
if (@GLoginname!='')
去掉了,还报错
SET @strWhere=@strWhere+' and Logindate >= '''+cast(@Logindate as datetime)+''吗?
SET @strWhere=@strWhere+' and Logindate >= '''+cast(@Logindate as datetime) +''
if (@Exitdate!='')
SET @strWhere=@strWhere+' and Exitdate <= '''+cast(@Exitdate
SET @strWhere=@strWhere+' and Logindate >= '''+cast(@Logindate as datetime) +''
@LoginName varchar(15),
@GmName varchar(10),
@GMGroup varchar(15),
@GameName varchar(20),
@Gserver varchar(20),
@Distributary varchar(25),
@Logindate DATETIME,--登入时间
@Exitdate DATETIME,--登出时间
@GLoginname varchar(15),
@Grole varchar(25),
@Goperate varchar(20),
@IsertDate DATETIME--写入时间
)
ASBEGIN
DECLARE @sql varchar(500)
DECLARE @strWhere varchar(500)
set @strWhere=''
IF (@LoginName !='')
SET @strWhere=@strWhere+' and LoginName = '+char(39)+CONVERT(VARCHAR(30),@LoginName,121)+char(39)
IF (@GmName !='')
SET @strWhere=@strWhere+' and GmName = '+char(39)+@GmName+char(39)
IF (@GMGroup !='')
SET @strWhere=@strWhere+' and GMGroup = '+char(39)+@GMGroup+char(39)
if (@GameName!='')
SET @strWhere=@strWhere+' and GameName = '+char(39)+@GameName+char(39)
if (@Gserver!='')
SET @strWhere=@strWhere+' and Gserver = '+char(39)+@Gserver+char(39)
if (@Distributary!='')
SET @strWhere=@strWhere+' and Distributary = '+char(39)+@Distributary+char(39)
if (@Logindate!='')
SET @strWhere=@strWhere+' and Logindate >= '+char(39)+@Logindate+char(39)
if (@Exitdate!='')
SET @strWhere=@strWhere+' and Exitdate <= '+char(39)+CONVERT(VARCHAR(30),@Exitdate,121)+char(39)
if (@GLoginname!='')
SET @strWhere=@strWhere+' and GLoginname = '+char(39)+@GLoginname+char(39)
if (@Grole!='')
SET @strWhere=@strWhere+' and Grole = '+char(39)+@Grole+char(39)
if (@Goperate!='')
SET @strWhere=@strWhere+' and Goperate = '+char(39)+@Goperate+char(39)
if (@IsertDate!='')
SET @strWhere=@strWhere+' and IsertDate >= '+char(39)+CONVERT(VARCHAR(30),@IsertDate,121)+char(39)
SET @sql='select * from dbo.GM_Diary where Statur=0'+@strWhere +' order by IsertDate desc'
EXEC(@sql)
END測試例子
CREATE PROC p
(
@dt DATETIME
)
AS
DECLARE @s NVARCHAR(2000)
IF @dt!=''
SET @s=N' select [顯示]='+CHAR(39)+CONVERT(VARCHAR(30),@dt,121)+CHAR(39)
EXEC(@s)
GOEXEC p @dt='2011-10-13 15:56:04.573'
/*
2011-10-13 15:56:04.573
*/
SET @strWhere=@strWhere+' and Logindate >= '''+cast(@Logindate as datetime) +''
if (@Exitdate!='')
SET @strWhere=@strWhere+' and Exitdate <= '''+cast(@Exitdate as datetime) +''
if (@GLoginname!='')
SET @strWhere=@strWhere+' and GLoginname = '+char(39)+@GLoginname+char(39)
if (@Grole!='')
SET @strWhere=@strWhere+' and Grole = '+char(39)+@Grole+char(39)
if (@Goperate!='')
SET @strWhere=@strWhere+' and Goperate = '+char(39)+@Goperate+char(39)
if (@IsertDate!='')
SET @strWhere=@strWhere+' and IsertDate >= '+char(39)+@IsertDate+char(39)
SET @sql='select * from dbo.GM_Diary where Statur=0' +@strWhere + ' order by IsertDate desc'
EXEC(@sql) END报的错误 从字符串转换为 datetime 时发生语法错误。
IF (@LoginName !='')
SET @strWhere=@strWhere+' and LoginName = '+char(39)+@LoginName+char(39)
IF (@GmName !='')
SET @strWhere=@strWhere+' and GmName = '+char(39)+@GmName+char(39)
IF (@GMGroup !='')
SET @strWhere=@strWhere+' and GMGroup = '+char(39)+@GMGroup+char(39)
if (@GameName!='')
SET @strWhere=@strWhere+' and GameName = '+char(39)+@GameName+char(39)
if (@Gserver!='')
SET @strWhere=@strWhere+' and Gserver = '+char(39)+@Gserver+char(39)
if (@Distributary!='')
SET @strWhere=@strWhere+' and Distributary = '+char(39)+@Distributary+char(39)
if (@Logindate!='')
SET @strWhere=@strWhere+' and Logindate >= '''+@Logindate+''
if (@Exitdate!='')
SET @strWhere=@strWhere+' and Exitdate <= '''+@Exitdate+''
if (@GLoginname!='')
SET @strWhere=@strWhere+' and GLoginname = '+char(39)+@GLoginname+char(39)
if (@Grole!='')
SET @strWhere=@strWhere+' and Grole = '+char(39)+@Grole+char(39)
if (@Goperate!='')
SET @strWhere=@strWhere+' and Goperate = '+char(39)+@Goperate+char(39)
if (@IsertDate!='')
SET @strWhere=@strWhere+' and IsertDate >= '+char(39)+@IsertDate+char(39)
SET @sql='select * from dbo.GM_Diary where Statur=0' +@strWhere + ' order by IsertDate desc'
运行后报:
字符串 '2011-10-13 11:01:00.000 order by IsertDate desc' 之前有未闭合的引号。
第 1 行: '2011-10-13 11:01:00.000 order by IsertDate desc' 附近有语法错误。
@LoginName varchar(15),
@GmName varchar(10),
@GMGroup varchar(15),
@GameName varchar(20),
@Gserver varchar(20),
@Distributary varchar(25),
@Logindate DATETIME,--登入时间
@Exitdate DATETIME,--登出时间
@GLoginname varchar(15),
@Grole varchar(25),
@Goperate varchar(20),
@IsertDate DATETIME--写入时间
)
ASBEGINSET DATEFORMAT YMD;DECLARE @sql varchar(500)
DECLARE @strWhere varchar(500)
set @strWhere=''
IF (ISNULL(@LoginName,'')>'')
SET @strWhere=@strWhere+' and LoginName = '+char(39)+CONVERT(VARCHAR(30),@LoginName,121)+char(39)
IF (ISNULL(@GmName,'')>'')
SET @strWhere=@strWhere+' and GmName = '+char(39)+@GmName+char(39)
IF (ISNULL(@GMGroup,'')>'')
SET @strWhere=@strWhere+' and GMGroup = '+char(39)+@GMGroup+char(39)
if (ISNULL(@GameName,'')>'')
SET @strWhere=@strWhere+' and GameName = '+char(39)+@GameName+char(39)
if (ISNULL(@Gserver,'')>'')
SET @strWhere=@strWhere+' and Gserver = '+char(39)+@Gserver+char(39)
if (ISNULL(@Distributary,'')>'')
SET @strWhere=@strWhere+' and Distributary = '+char(39)+@Distributary+char(39)
if (ISNULL(@Logindate,'')>'')
SET @strWhere=@strWhere+' and Logindate >= '+char(39)+@Logindate+char(39)
if (ISNULL(@Exitdate,'')>'')
SET @strWhere=@strWhere+' and Exitdate <= '+char(39)+CONVERT(VARCHAR(30),@Exitdate,121)+char(39)
if (ISNULL(@GLoginname,'')>'')
SET @strWhere=@strWhere+' and GLoginname = '+char(39)+@GLoginname+char(39)
if (ISNULL(@Grole,'')>'')
SET @strWhere=@strWhere+' and Grole = '+char(39)+@Grole+char(39)
if (ISNULL(@Goperate,'')>'')
SET @strWhere=@strWhere+' and Goperate = '+char(39)+@Goperate+char(39)
if (ISNULL(@IsertDate,'')>'')
SET @strWhere=@strWhere+' and IsertDate >= '+char(39)+CONVERT(VARCHAR(30),@IsertDate,121)+char(39)
SET @sql='select * from dbo.GM_Diary where Statur=0'+@strWhere +' order by IsertDate desc'
EXEC(@sql)
END
参数Logindate ="2011-10-10 00:01:00.000",
Exitdate ="2011-10-13 11:01:00.000" 其他的都是空CREATE PROCEDURE PRO_Select_GM_DiaryD(
@LoginName varchar(15),--登入名
@GmName varchar(10), --姓名
@GMGroup varchar(15),--分组
@GameName varchar(20),--游戏名称
@Gserver varchar(20),--服务器
@Distributary varchar(25),--分流
@Logindate varchar(30),--登入时间
@Exitdate varchar(30),--登出时间
@GLoginname varchar(15),--巡服账号
@Grole varchar(25),--巡服角色
@Goperate varchar(20),--巡服操作
@IsertDate varchar(30)--写入时间
)
AS
BEGIN SET DATEFORMAT YMD; DECLARE @sql varchar(500)
DECLARE @strWhere varchar(500)
set @strWhere=''
IF (@LoginName !='')
SET @strWhere=@strWhere+' and LoginName = '+char(39)+@LoginName+char(39)
IF (@GmName !='')
SET @strWhere=@strWhere+' and GmName = '+char(39)+@GmName+char(39)
IF (@GMGroup !='')
SET @strWhere=@strWhere+' and GMGroup = '+char(39)+@GMGroup+char(39)
if (@GameName!='')
SET @strWhere=@strWhere+' and GameName = '+char(39)+@GameName+char(39)
if (@Gserver!='')
SET @strWhere=@strWhere+' and Gserver = '+char(39)+@Gserver+char(39)
if (@Distributary!='')
SET @strWhere=@strWhere+' and Distributary = '+char(39)+@Distributary+char(39)
if (@Logindate!='')
SET @strWhere=@strWhere+' and Logindate >= '+char(39)+CONVERT(VARCHAR(30),@Logindate,121)+char(39)
if (@Exitdate!='')
SET @strWhere=@strWhere+' and Exitdate <= '+char(39)+CONVERT(VARCHAR(30),@Exitdate,121)+char(39)
if (@GLoginname!='')
SET @strWhere=@strWhere+' and GLoginname = '+char(39)+@GLoginname+char(39)
if (@Grole!='')
SET @strWhere=@strWhere+' and Grole = '+char(39)+@Grole+char(39)
if (@Goperate!='')
SET @strWhere=@strWhere+' and Goperate = '+char(39)+@Goperate+char(39)
if (@IsertDate!='')
SET @strWhere=@strWhere+' and IsertDate >= '+char(39)+@IsertDate+char(39)
SET @sql='select * from dbo.GM_Diary where Statur=0' +@strWhere + ' order by IsertDate desc'
EXEC(@sql) END
不行CREATE PROCEDURE PRO_Select_GM_DiaryD(
@LoginName varchar(15),--登入名
@GmName varchar(10), --姓名
@GMGroup varchar(15),--分组
@GameName varchar(20),--游戏名称
@Gserver varchar(20),--服务器
@Distributary varchar(25),--分流
@Logindate varchar(30),--登入时间
@Exitdate varchar(30),--登出时间
@GLoginname varchar(15),--巡服账号
@Grole varchar(25),--巡服角色
@Goperate varchar(20),--巡服操作
@IsertDate varchar(30)--写入时间
)
AS
BEGIN SET DATEFORMAT YMD; DECLARE @sql varchar(500)
DECLARE @strWhere varchar(500)
set @strWhere=''
IF (ISNULL(@LoginName,'') >'')
SET @strWhere=@strWhere+' and LoginName = '+char(39)+@LoginName+char(39)
IF (ISNULL(@GmName,'') >'')
SET @strWhere=@strWhere+' and GmName = '+char(39)+@GmName+char(39)
IF (ISNULL(@GMGroup,'') >'')
SET @strWhere=@strWhere+' and GMGroup = '+char(39)+@GMGroup+char(39)
if (ISNULL(@GameName,'') >'')
SET @strWhere=@strWhere+' and GameName = '+char(39)+@GameName+char(39)
if (ISNULL(@Gserver,'') >'')
SET @strWhere=@strWhere+' and Gserver = '+char(39)+@Gserver+char(39)
if (ISNULL(@Distributary,'') >'')
SET @strWhere=@strWhere+' and Distributary = '+char(39)+@Distributary+char(39)
if (ISNULL(@Logindate,'')>'')
SET @strWhere=@strWhere+' and Logindate >='+char(39)+CONVERT(VARCHAR(30),@Logindate,121)+char(39)
if (ISNULL(@Exitdate,'')>'')
SET @strWhere=@strWhere+' and Exitdate <= '+char(39)+CONVERT(VARCHAR(30),@Exitdate,121)+char(39)
if (ISNULL(@GLoginname,'')>'')
SET @strWhere=@strWhere+' and GLoginname = '+char(39)+@GLoginname+char(39)
if (ISNULL(@Grole,'')>'')
SET @strWhere=@strWhere+' and Grole = '+char(39)+@Grole+char(39)
if (ISNULL(@Goperate,'')>'')
SET @strWhere=@strWhere+' and Goperate = '+char(39)+@Goperate+char(39)
if (ISNULL(@IsertDate,'')>'')
SET @strWhere=@strWhere+' and IsertDate >= '+char(39)+@IsertDate+char(39)
SET @sql='select * from dbo.GM_Diary where Statur=0' +@strWhere + ' order by IsertDate desc'
EXEC(@sql) END
GO
USE tempdb
GO
Create PROCEDURE PRO_Select_GM_DiaryD(
@LoginName varchar(15),
@GmName varchar(10),
@GMGroup varchar(15),
@GameName varchar(20),
@Gserver varchar(20),
@Distributary varchar(25),
@Logindate DATETIME,--登入时间
@Exitdate DATETIME,--登出时间
@GLoginname varchar(15),
@Grole varchar(25),
@Goperate varchar(20),
@IsertDate DATETIME--写入时间
)
ASBEGINSET DATEFORMAT YMD;DECLARE @sql varchar(2000)
DECLARE @strWhere varchar(2000)set @strWhere=''
IF (ISNULL(@LoginName,'')>'')
SET @strWhere=@strWhere+' and LoginName = '+char(39)+@LoginName+char(39)
IF (ISNULL(@GmName,'')>'')
SET @strWhere=@strWhere+' and GmName = '+char(39)+@GmName+char(39)IF (ISNULL(@GMGroup,'')>'')
SET @strWhere=@strWhere+' and GMGroup = '+char(39)+@GMGroup+char(39)
if (ISNULL(@GameName,'')>'')
SET @strWhere=@strWhere+' and GameName = '+char(39)+@GameName+char(39)
if (ISNULL(@Gserver,'')>'')
SET @strWhere=@strWhere+' and Gserver = '+char(39)+@Gserver+char(39)
if (ISNULL(@Distributary,'')>'')
SET @strWhere=@strWhere+' and Distributary = '+char(39)+@Distributary+char(39)
if (ISNULL(@Logindate,'')>'')
SET @strWhere=@strWhere+' and Logindate >= '+char(39)+CONVERT(VARCHAR(30),@Logindate,121)+char(39)
if (ISNULL(@Exitdate,'')>'')
SET @strWhere=@strWhere+' and Exitdate <= '+char(39)+CONVERT(VARCHAR(30),@Exitdate,121)+char(39)
if (ISNULL(@GLoginname,'')>'')
SET @strWhere=@strWhere+' and GLoginname = '+char(39)+@GLoginname+char(39)
if (ISNULL(@Grole,'')>'')
SET @strWhere=@strWhere+' and Grole = '+char(39)+@Grole+char(39)
if (ISNULL(@Goperate,'')>'')
SET @strWhere=@strWhere+' and Goperate = '+char(39)+@Goperate+char(39)
if (ISNULL(@IsertDate,'')>'')
SET @strWhere=@strWhere+' and IsertDate >= '+char(39)+CONVERT(VARCHAR(30),@IsertDate,121)+char(39)SET @sql='select * from dbo.GM_Diary where Statur=0'+@strWhere +' order by IsertDate desc'EXEC(@sql)
END
GOEXEC PRO_Select_GM_DiaryD @LoginName='',
@GmName ='',
@GMGroup ='',
@GameName ='',
@Gserver ='',
@Distributary ='',
@Logindate='2011-10-10 00:01:00.000',--登入时间
@Exitdate ='2011-10-13 11:01:00.000',--登出时间
@GLoginname ='',
@Grole ='',
@Goperate ='',
@IsertDate =''--写入时间
go
--生成的腳本
select * from dbo.GM_Diary where Statur=0 and Logindate >= '2011-10-10 00:01:00.000' and Exitdate <= '2011-10-13 11:01:00.000' order by IsertDate desc
可以了。
也感谢大家!结贴回家