create proc Proc_QueryTableInfoByInfo
@CompanyName varchar(100),
@CompanyKindID intas
declare @sqlStr varchar(1000)if @CompanyName IS NOT NULL
begin
if @sqlStr IS NOT NULL
set @sqlStr=@sqlStr+' and CompanyName like '+''''+@CompanyName+''''
else
set @sqlStr=' where CompanyName like '+'''%'+@CompanyName+'%'''
end
if @CompanyKindID is not null
begin
if @sqlStr IS NOT NULL
set @sqlStr=@sqlStr+' and CompanyKindID ='+''''+@CompanyKindID+''''
else
set @sqlStr=' where CompanyKindID = '+''''+@CompanyKindID+''''
endEXEC( 'SELECT * FROM CompanyAllInfo'+ @sqlStr)以上是存储过程
但是我执行 exec Proc_QueryTableInfoByInfo null
提示在将 varchar 值 ' where CompanyKindID = '' 转换成数据类型 int 时失败。
哪个地方写错了,第二个begin那个地方,写法肯定错了,但是我不知道该怎么写,请告诉解决
@CompanyName varchar(100),
@CompanyKindID intas
declare @sqlStr varchar(1000)if @CompanyName IS NOT NULL
begin
if @sqlStr IS NOT NULL
set @sqlStr=@sqlStr+' and CompanyName like '+''''+@CompanyName+''''
else
set @sqlStr=' where CompanyName like '+'''%'+@CompanyName+'%'''
end
if @CompanyKindID is not null
begin
if @sqlStr IS NOT NULL
set @sqlStr=@sqlStr+' and CompanyKindID ='+''''+@CompanyKindID+''''
else
set @sqlStr=' where CompanyKindID = '+''''+@CompanyKindID+''''
endEXEC( 'SELECT * FROM CompanyAllInfo'+ @sqlStr)以上是存储过程
但是我执行 exec Proc_QueryTableInfoByInfo null
提示在将 varchar 值 ' where CompanyKindID = '' 转换成数据类型 int 时失败。
哪个地方写错了,第二个begin那个地方,写法肯定错了,但是我不知道该怎么写,请告诉解决
解决方案 »
- 新手上路,请多关照。SQL server中的自增涨主键怎么写
- 本人菜鸟 求救菜鸟问题 关于sql语句!!
- 如何用企业管理器备份远程数据到本地?
- 为什么我SQL server的默认语言改不了呢?
- 怎么能把10万条email记录倒入到一个表中,这个表的email字段是主键
- 求助sql语句!要求记录中,没有重复的A2字段,但要显示所有其他字段的记录!
- 如何快速插入上亿条记录
- 求救:关于时间(hh:mm:ss)查询问题up者有分
- 超困难的多表查询问题,请大侠帮忙啊.急~~~~~
- 下面的查询语句是否可以创建成一个视图??
- SQL Server 数据库引擎的实例此时无法获得 LOCK 资源,各位大大分析分析 谢谢
- sql 2008 编码请教
提示在将 varchar 值 ' where CompanyKindID = '' 转换成数据类型 int 时失败。
(
CompanyName varchar(100),
CompanyKindID int
)insert into CompanyAllInfo
select '123123123',1
--1.
create proc Proc_QueryTableInfoByInfo
@CompanyName varchar(100),
@CompanyKindID int
as
begin
select * from CompanyAllInfo
where CompanyName=ISNULL(@CompanyName,CompanyName) and CompanyKindID=cast(ISNULL(@CompanyKindID,CompanyKindID) as int)
end--2.
ALTER PROCEDURE Proc_QueryTableInfoByInfo
(
@CompanyName varchar(100),
@CompanyKindID int
)
AS
BEGIN
DECLARE @SQL NVARCHAR(MAX)
SET @SQL='SELECT * from CompanyAllInfo WHERE 1=1'
+CASE WHEN @CompanyName IS NOT NULL THEN
' AND CompanyName='+@CompanyName+'' ELSE '' END
+CASE WHEN @CompanyKindID IS NOT NULL THEN
' AND CompanyKindID='+ltrim(@CompanyKindID)+'' ELSE '' END
exec (@SQL)
END
exec Proc_QueryTableInfoByInfo null,null
楼主写法太麻烦
因为是字符串和整型数据相加,所以系统把试着把前边的字符串转换成整数('and CompanyKindID ='),报错。
#2. 逻辑错误:declare @sqlStr varchar(1000)
定义后变量@sqlStr没有初始化,set @sqlStr=@sqlStr+' and CompanyName like '+''''+@CompanyName+''''这行永远不会执行
#3. 写法有些臃肿,请参考下面:
alter proc Proc_QueryTableInfoByInfo
@CompanyName varchar(100),
@CompanyKindID int
as
declare @sqlStr varchar(1000)
set @sqlStr = ' where 1=1 '
if(ISNULL(@CompanyName, '') <> '')
begin
set @sqlStr = @sqlStr + ' and CompanyName like '+'''%'+@CompanyName+'%'''
--或者用下面这行
--set @sqlStr = @sqlStr + ' and CompanyName = '''+@CompanyName+''''
end
if (ISNULL(@CompanyKindID, '') <> '')
begin
set @sqlStr=@sqlStr+' and CompanyKindID =' +CAST(@CompanyKindID AS VARCHAR(100))
endEXEC( 'SELECT * FROM CompanyAllInfo'+ @sqlStr)
if @sqlStr IS NOT NULL
set @sqlStr=@sqlStr+' and CompanyKindID ='+''''+rtrim(@CompanyKindID)+''''
else
set @sqlStr=' where CompanyKindID = '+''''+rtrim(@CompanyKindID)+''''
end