关于存储过程参数传递的问题

[laeep]

我要实现的功能是
if ClassEName <> "" then
HotNewsSql = "Select *,NewsClass.FileExtName as ClassFileExtName,News.FileExtName as NewsFileExtName from News,NewsClass where News.ClassID=NewsClass.ClassID and DelTF=0 and News.AuditTF=1 and NewsClass.ClassEName='" & ClassEName & "' order by News.ClickNum Desc"
else
HotNewsSql = "Select *,NewsClass.FileExtName as ClassFileExtName,News.FileExtName as NewsFileExtName from News,NewsClass where News.ClassID=NewsClass.ClassID and DelTF=0 and News.AuditTF=1 order by News.ClickNum Desc"
end if
我的存储过程是这样写的

CREATE Procedure FS_ReFunHotNews
(@ClassEName varchar(100))
as
begin transaction
declare @TempSql varchar(1500)
Set @TempSql='Select *,NewsClass.FileExtName as ClassFileExtName,News.FileExtName as NewsFileExtName from News,NewsClass where News.ClassID=NewsClass.ClassID and DelTF=0 and News.AuditTF=1'+@ClassEName+' order by News.ClickNum Desc'
exec(@TempSql)
if @@error=0
commit transaction
else
rollback transaction
return
GO
我要问的是
我怎么把参数 and NewsClass.ClassEName='" & ClassEName & "'传进去

或者改存储过程也行，怎么改？

[laeep]

单引号 怎么传进去？就是那个变量ClassEName

[paoluo]

存储过程改为这样即可

CREATE Procedure FS_ReFunHotNews
(@ClassEName varchar(100))
as
begin transaction
declare @TempSql varchar(1500)
If ClassEName <> ""
Set @TempSql='Select *,NewsClass.FileExtName as ClassFileExtName,News.FileExtName as NewsFileExtName from News,NewsClass where News.ClassID=NewsClass.ClassID and DelTF=0 and News.AuditTF=1 and NewsClass.ClassEName='''+@ClassEName+''' order by News.ClickNum Desc'
Else
Set @TempSql='Select *,NewsClass.FileExtName as ClassFileExtName,News.FileExtName as NewsFileExtName from News,NewsClass where News.ClassID=NewsClass.ClassID and DelTF=0 and News.AuditTF=1 order by News.ClickNum Desc'
exec(@TempSql)
if @@error=0
commit transaction
else
rollback transaction
return
GO

[paoluo]

传入的时候，直接传 ClassEName 的值，不用做处理了。

[laeep]

看着好长呀，不舒服呀

[laeep]

NewsClass.ClassEName='''+@ClassEName+''' order by News.ClickNum Desc'
为什么这么多的单引号
这样不行吗
NewsClass.ClassEName='+@ClassEName+' order by News.ClickNum Desc'
或者
NewsClass.ClassEName='+cast(@ClassEName as varchar(100))+' order by News.ClickNum Desc'

[laeep]

鱼 你写错了，忘了加@了 呵呵

[vivianfdlpw]

两个单引号相当于一个普通的单引号