一个表
USE [ReportServer]
GO
/****** Object: Table [dbo].[RevisionHistory] Script Date: 11/14/2008 14:46:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[RevisionHistory](
[ID] [int] IDENTITY(1,1) NOT NULL,
[RevisionType] [int] NULL,
[RevisionDesc] [nvarchar](4000) NULL,
[RevisionDate] [datetime] NULL,
[RequestUser] [nvarchar](100) NULL,
[RequestDate] [datetime] NULL,
[CreateDT] [datetime] NULL,
[ChangeDT] [datetime] NULL,
[Display] [bit] NULL,
CONSTRAINT [PK_RevisionHistory] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]一个存储过程
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO--exec sp_RevisionNews '20080101','20081105',3,'sd'
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_RevisionNews]
@RevDateFm nvarchar(8) = null,
@RevDateTo nvarchar(8) = null,
@RevType int = null,
@Desc nvarchar(4000) = null,@strstrsql nvarchar(MAX) = null output
as
begin
declare @sql nvarchar(MAX);
set @sql = '';
set @sql = @sql +
' SELECT RevisionDate,RevisionDesc,RevisionType,RequestUser,RequestDate,ID FROM RevisionHistory RH
'; set @sql = @sql + ' where 1 = 1 '; if ((@RevDateFm <> '' and @RevDateFm is not null) or (@RevDateTo <> '' and @RevDateTo is not null))
begin
if ((@RevDateFm <> '' and @RevDateFm is not null) and (@RevDateTo <> '' and @RevDateTo is not null))
begin
set @sql = @sql + ' and convert(varchar,RH.RevisionDate,112) >= ''' + @RevDateFm + ''' and convert(varchar,RH.RevisionDate,112) <= ''' + @RevDateTo + '''';
end
else if(@RevDateFm <> '' and @RevDateFm is not null)
begin
set @sql = @sql + ' and convert(varchar,RH.RevisionDate,112) = ''' + @RevDateFm + '''';
end
else if(@RevDateTo <> '' and @RevDateTo is not null)
begin
set @sql = @sql + ' and convert(varchar,RH.RevisionDate,112) = ''' + @RevDateTo + ''''
end
end
if(@RevType<>''and @RevType is not null)
begin
set @sql = @sql + ' and RH.RevisionType = ' + @RevType
end
if(@Desc<>''and @Desc is not null)
begin
set @sql = @sql + ' and RH.RevisionDesc LIKE ''%' + @Desc + '%'''
end
--====================
--==================== set @sql = @sql + ' ORDER BY RH.RevisionDate DESC'
print @sql;
set @strstrsql = @sql;
execute (@sql);end
可以用注释的exec sp_RevisionNews '20080101','20081105',3,'sd' 测试
报这个错
消息 245,级别 16,状态 1,过程 sp_RevisionNews,第 43 行
在将 nvarchar 值 ' SELECT RevisionDate,RevisionDesc,RevisionType,RequestUser,RequestDate,ID FROM RevisionHistory RH
where 1 = 1 and convert(varchar,RH.RevisionDate,112) >= '20080101' and convert(varchar,RH.RevisionDate,112) <= '20081105' and RH.RevisionType = ' 转换成数据类型 int 时失败。我不知道哪里错了 声明 用的SQL SERVER 2005
USE [ReportServer]
GO
/****** Object: Table [dbo].[RevisionHistory] Script Date: 11/14/2008 14:46:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[RevisionHistory](
[ID] [int] IDENTITY(1,1) NOT NULL,
[RevisionType] [int] NULL,
[RevisionDesc] [nvarchar](4000) NULL,
[RevisionDate] [datetime] NULL,
[RequestUser] [nvarchar](100) NULL,
[RequestDate] [datetime] NULL,
[CreateDT] [datetime] NULL,
[ChangeDT] [datetime] NULL,
[Display] [bit] NULL,
CONSTRAINT [PK_RevisionHistory] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]一个存储过程
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO--exec sp_RevisionNews '20080101','20081105',3,'sd'
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_RevisionNews]
@RevDateFm nvarchar(8) = null,
@RevDateTo nvarchar(8) = null,
@RevType int = null,
@Desc nvarchar(4000) = null,@strstrsql nvarchar(MAX) = null output
as
begin
declare @sql nvarchar(MAX);
set @sql = '';
set @sql = @sql +
' SELECT RevisionDate,RevisionDesc,RevisionType,RequestUser,RequestDate,ID FROM RevisionHistory RH
'; set @sql = @sql + ' where 1 = 1 '; if ((@RevDateFm <> '' and @RevDateFm is not null) or (@RevDateTo <> '' and @RevDateTo is not null))
begin
if ((@RevDateFm <> '' and @RevDateFm is not null) and (@RevDateTo <> '' and @RevDateTo is not null))
begin
set @sql = @sql + ' and convert(varchar,RH.RevisionDate,112) >= ''' + @RevDateFm + ''' and convert(varchar,RH.RevisionDate,112) <= ''' + @RevDateTo + '''';
end
else if(@RevDateFm <> '' and @RevDateFm is not null)
begin
set @sql = @sql + ' and convert(varchar,RH.RevisionDate,112) = ''' + @RevDateFm + '''';
end
else if(@RevDateTo <> '' and @RevDateTo is not null)
begin
set @sql = @sql + ' and convert(varchar,RH.RevisionDate,112) = ''' + @RevDateTo + ''''
end
end
if(@RevType<>''and @RevType is not null)
begin
set @sql = @sql + ' and RH.RevisionType = ' + @RevType
end
if(@Desc<>''and @Desc is not null)
begin
set @sql = @sql + ' and RH.RevisionDesc LIKE ''%' + @Desc + '%'''
end
--====================
--==================== set @sql = @sql + ' ORDER BY RH.RevisionDate DESC'
print @sql;
set @strstrsql = @sql;
execute (@sql);end
可以用注释的exec sp_RevisionNews '20080101','20081105',3,'sd' 测试
报这个错
消息 245,级别 16,状态 1,过程 sp_RevisionNews,第 43 行
在将 nvarchar 值 ' SELECT RevisionDate,RevisionDesc,RevisionType,RequestUser,RequestDate,ID FROM RevisionHistory RH
where 1 = 1 and convert(varchar,RH.RevisionDate,112) >= '20080101' and convert(varchar,RH.RevisionDate,112) <= '20081105' and RH.RevisionType = ' 转换成数据类型 int 时失败。我不知道哪里错了 声明 用的SQL SERVER 2005
begin
set @sql = @sql + ' and RH.RevisionType = ' + RTRIM(@RevType)
end
if(@RevType <>''and @RevType is not null)
begin
-- set @sql = @sql + ' and RH.RevisionType = ' + @RevType
set @sql = @sql + ' and RH.RevisionType = ' + cast(@RevType as nvarchar(20))
end
看看是什么
要进行字符串的拼接。一个字符型和一个整形数据是没有办法拼接的。所以两个修改方案
1、@RevType定义成字符型的
2、and RH.RevisionType = ' +cast( @RevType as varchar)