调了一小时了 不知道哪错了。。。。

[sheixiu]

ALTER PROCEDURE ZYDA__selectRecordByRecordID
@records nvarchar(200),
@type int,
@power int,
@department int,
@id int
as
declare @sql nvarchar(4000)
set @sql='SELECT ZYDA_Records.id, ZYDA_Records.records_no, ZYDA_Records.superscription, ZYDA_Records.mulu, 

      ZYDA_Records.remark, ZYDA_Records.quanzong,ZYDA_Records.type,ZYDA_FileType.type
      FROM ZYDA_Records INNER JOIN
      ZYDA_FileType ON ZYDA_Records.type = ZYDA_FileType.id  where (1=1) '

-- 如果只有类型 没有案卷 仅搜索类型
if( @type <> '0')
set @sql=@sql+' and ( ZYDA_Records.type like ''%'+@type+'%'')'

like''%'+@records+'%'')'

exec(@sql)

显示错误：
正在运行 [dbo].[ZYDA__selectRecordByRecordID] ( @records = , @type = 102, @power = 1, @department = 1, @id = 1 ).

将 nvarchar 值 'SELECT ZYDA_Records.id, ZYDA_Records.records_no, ZYDA_Records.superscription, ZYDA_Records.mulu,

ZYDA_Records.remark, ZYDA_Records.quanzong,ZYDA_Records.type,ZYDA_FileType.type
FROM ZYDA_Records INNER JOIN
ZYDA_FileType ON ZYDA_Records.type = ZYDA_FileType.id where (1=1) and ( ZYDA_Records.type like '%' 转换为数据类型为 int 的列时发生语法错误。
没有行受影响。

[fwacky]

exec(@sql)
pint(@sql)

[fwacky]

print(@sql)
打出来看看！

[playwarcraft]

@type 是int 怎麼能直接加？
改下
if( @type <> 0)
set @sql=@sql+' and ( ZYDA_Records.type like ''%'+Rtrim(@type)+'%'')'

[playwarcraft]

if( @type <> 0)
set @sql=@sql+' and ( ZYDA_Records.type like ''%'+Rtrim(@type)+'%'')'

[feixianxxx]

if( @type <> '0')
set @sql=@sql+' and ( ZYDA_Records.type like ''%'+convert(varchar(40),@type)+'%'')'

[fwacky]

like''%'+@records+'%'')'
这个事多余的么？

[feixianxxx]

LZ注意：
在动态语句里 所有的变量 必须都转化成字符类型
楼主的@TYPE是Int类型的

[sheixiu]

谢谢大哥了。。。他妈的自己搞了1小时

[xyworkroom]

if( @type <> '0')
set @sql=@sql+' and ( ZYDA_Records.type like ''%'+CAST ( @type AS VARCHAR(40) )+'%'')'

[sheixiu]

[Quote=引用 6 楼 fwacky 的回复:]
SQL codelike''%'+@records+'%'')'
这个事多余的么？
[/Quote]

是多余的 我为让了让大家看清爽没删除干净

[feixianxxx]

if( @type <> '0') 
这里的'0'不改没关系 系统自动转换的

declare @n int
set @n=19
if(@n='19')
select 8
/*
 8
*/

[feixianxxx]

[Quote=引用 8 楼 sheixiu 的回复:]
谢谢大哥了。。。他妈的自己搞了1小时


[/Quote]
这些细节楼主静下心 就可以做好了 的

[昵称被占用了]

[Quote=引用 3 楼 playwarcraft 的回复:]
@type 是int 怎麼能直接加？
改下
if( @type <> 0)
set @sql=@sql+' and ( ZYDA_Records.type like ''%'+Rtrim(@type)+'%'')'

[/Quote]

你的语句没写全

[fwacky]

alter PROCEDURE ZYDA__selectRecordByRecordID
@records nvarchar(200),
@type int,
@power int,
@department int,
@id int
as
declare @sql nvarchar(4000)
set @sql='SELECT ZYDA_Records.id, ZYDA_Records.records_no, ZYDA_Records.superscription, ZYDA_Records.mulu, 

      ZYDA_Records.remark, ZYDA_Records.quanzong,ZYDA_Records.type,ZYDA_FileType.type
      FROM ZYDA_Records INNER JOIN
      ZYDA_FileType ON ZYDA_Records.type = ZYDA_FileType.id  where (1=1) '

-- 如果只有类型 没有案卷 仅搜索类型
if( @type <> '0')
set @sql=@sql+' and ( ZYDA_Records.type like ''%'+Rtrim(@type)++'%'')'

  --records like''%'+@records+'%'')'

print (@sql)


exec  ZYDA__selectRecordByRecordID '0',1,2,3,4
---试
SELECT ZYDA_Records.id, ZYDA_Records.records_no, ZYDA_Records.superscription, ZYDA_Records.mulu, 

      ZYDA_Records.remark, ZYDA_Records.quanzong,ZYDA_Records.type,ZYDA_FileType.type
      FROM ZYDA_Records INNER JOIN
      ZYDA_FileType ON ZYDA_Records.type = ZYDA_FileType.id  where (1=1)  and ( ZYDA_Records.type like '%1%')

[sheixiu]

下回半小时搞不定的问题一定要问一下，不然这辈子也不知道错哪了

[feixianxxx]

[Quote=引用 15 楼 sheixiu 的回复:]
下回半小时搞不定的问题一定要问一下，不然这辈子也不知道错哪了
[/Quote]
。。。。。人就这么一辈子。。。。。

[sheixiu]

眼睛一睁一闭一辈子过去了，好儿~~~~结贴了。。。。