执行以下命令时出现错误:
将 varchar 值 'fdsf' 转换为数据类型为 int 的列时发生语法错误,当senduser列中有非int类型时就会出错。exec prSmsQuery @dStartDate = '04 15 2007 12:00:00:000AM', @dEndDate = '04 15 2008 12:00:00:000AM',
@iViewType = 0, @chvMobile = NULL,
@nchvContent = NULL, @chvUser = '21002'
sms表格内容:
CREATE TABLE [SMS] (
[SysNo] [int] IDENTITY (1, 1) NOT NULL ,
[sendUser] [varchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
[CellNumber] [nvarchar] (11) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[SMSContent] [nvarchar] (70) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Priority] [int] NOT NULL CONSTRAINT [DF_SMS_Priority] DEFAULT (0),
[RetryCount] [int] NOT NULL CONSTRAINT [DF_SMS_RetryCount] DEFAULT (0),
[CreateTime] [datetime] NOT NULL CONSTRAINT [DF_SMS_CreateTime] DEFAULT (getdate()),
[HandleTime] [datetime] NULL ,
[Status] [int] NOT NULL CONSTRAINT [DF_SMS_Status] DEFAULT (0),
CONSTRAINT [PK_SMS] PRIMARY KEY CLUSTERED
(
[SysNo]
) ON [PRIMARY]
) ON [PRIMARY]
GO
存储过程如下:
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOALTER PROCEDURE prSmsQuery
(
@dStartDate datetime,@dEndDate datetime,@iViewType int,@chvMobile varchar(11),@nchvContent nvarchar(70),@chvUser varchar(5)
)
AS
--1:知信;0:通知;
--@iVIewType 查询类型 0:发送1:接收2:全部
declare @chvSendSms varchar(3000)--发送文本SQL
declare @chvRecvSms varchar(3000)--接收文本SQL
set @chvSendSms='SELECT CellNumber as id, SMSContent as content , CreateTime ,''发送'' as type FROM dbo.SMS WHERE (sendUser =' + @chvUser + ') AND (CreateTime >=''' + cast(@dStartDate as varchar) + ''' AND CreateTime <''' +
cast(DATEADD(d, 1, @dEndDate) as varchar) +''' )'if @chvMobile<> '' and @chvMobile is not null
begin
set @chvSendSms=@chvSendSms + ' and CellNumber=''' + @chvMobile + ''''
set @chvRecvSms=@chvRecvSms + ' and dbo.SmsTransmit.id=''' + @chvMobile + ''''
end
if @nchvContent<> '' and @nchvContent is not null
begin
set @chvSendSms=@chvSendSms + ' and SMSContent like ''%' + @nchvContent + '%'''
set @chvRecvSms=@chvRecvSms + ' and dbo.SmsTransmit.content like ''%' + @nchvContent + '%'''
end
if @iViewType=2
set @chvSendSms=@chvSendSms + 'union all ' + @chvRecvSms
if @iViewType=1
set @chvSendSms=@chvRecvSms
execute (@chvSendSms)
RETURN GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
将 varchar 值 'fdsf' 转换为数据类型为 int 的列时发生语法错误,当senduser列中有非int类型时就会出错。exec prSmsQuery @dStartDate = '04 15 2007 12:00:00:000AM', @dEndDate = '04 15 2008 12:00:00:000AM',
@iViewType = 0, @chvMobile = NULL,
@nchvContent = NULL, @chvUser = '21002'
sms表格内容:
CREATE TABLE [SMS] (
[SysNo] [int] IDENTITY (1, 1) NOT NULL ,
[sendUser] [varchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
[CellNumber] [nvarchar] (11) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[SMSContent] [nvarchar] (70) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Priority] [int] NOT NULL CONSTRAINT [DF_SMS_Priority] DEFAULT (0),
[RetryCount] [int] NOT NULL CONSTRAINT [DF_SMS_RetryCount] DEFAULT (0),
[CreateTime] [datetime] NOT NULL CONSTRAINT [DF_SMS_CreateTime] DEFAULT (getdate()),
[HandleTime] [datetime] NULL ,
[Status] [int] NOT NULL CONSTRAINT [DF_SMS_Status] DEFAULT (0),
CONSTRAINT [PK_SMS] PRIMARY KEY CLUSTERED
(
[SysNo]
) ON [PRIMARY]
) ON [PRIMARY]
GO
存储过程如下:
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOALTER PROCEDURE prSmsQuery
(
@dStartDate datetime,@dEndDate datetime,@iViewType int,@chvMobile varchar(11),@nchvContent nvarchar(70),@chvUser varchar(5)
)
AS
--1:知信;0:通知;
--@iVIewType 查询类型 0:发送1:接收2:全部
declare @chvSendSms varchar(3000)--发送文本SQL
declare @chvRecvSms varchar(3000)--接收文本SQL
set @chvSendSms='SELECT CellNumber as id, SMSContent as content , CreateTime ,''发送'' as type FROM dbo.SMS WHERE (sendUser =' + @chvUser + ') AND (CreateTime >=''' + cast(@dStartDate as varchar) + ''' AND CreateTime <''' +
cast(DATEADD(d, 1, @dEndDate) as varchar) +''' )'if @chvMobile<> '' and @chvMobile is not null
begin
set @chvSendSms=@chvSendSms + ' and CellNumber=''' + @chvMobile + ''''
set @chvRecvSms=@chvRecvSms + ' and dbo.SmsTransmit.id=''' + @chvMobile + ''''
end
if @nchvContent<> '' and @nchvContent is not null
begin
set @chvSendSms=@chvSendSms + ' and SMSContent like ''%' + @nchvContent + '%'''
set @chvRecvSms=@chvRecvSms + ' and dbo.SmsTransmit.content like ''%' + @nchvContent + '%'''
end
if @iViewType=2
set @chvSendSms=@chvSendSms + 'union all ' + @chvRecvSms
if @iViewType=1
set @chvSendSms=@chvRecvSms
execute (@chvSendSms)
RETURN GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
解决方案 »
- pivot-bit类型不能使用sum等聚集函数,应该怎么办?
- 有没有简单的语句返回前几条id加逗号合并成的字符串?
- 如何取随机数,比较好?
- 请问如果安装SQLSERVER选择的是windows登陆,该如何写conn语句..
- Visual Studio 2005 出现这样的错误怎么办
- 100分寻求恢复数据表,解决问题立即给分。
- 系统环境变化,像ADOQuery属性数据链接属性中如何再自动识别SQL服务器名
- 两个表去除数据的问题 高手帮忙 通过立即给分!!
- 这个存储过程怎么写
- 一个小问题,高分奉上,有什么办法使Delphi与SQL Server数据库的连接速度快一点
- AVG 函数问题
- sp_adddistributor中的@password是什么密码??
(sendUser =' + @chvUser + ') 上,但我不知道怎么改,哪位高手看一下啊为什么这里要有转换呢