存储过程Top+变量问题
CREATE   PROC   Proc_News
@Num   INT,
@Class_Code1   VARCHAR(50),
@Class_Code2   VARCHAR(50),
@News_Hot   VARCHAR(50)
AS
BEGIN
(SELECT   TOP   1   News_Id,News_Title,News_SmallPicUrl,News_AddDate   FROM   News,News_Class   WHERE   NewsClass_Id=Class_Id   AND   Class_Code=@Class_Code1   AND   News_Hot=@News_Hot)   UNION   (SELECT   TOP   1   News_Id,News_Title,News_SmallPicUrl,News_AddDate   FROM   News,News_Class   WHERE   NewsClass_Id=Class_Id   AND   Class_Code=@Class_Code2   AND   News_Hot=@News_Hot)   ORDER   BY   News_AddDate   DESC
END
GO

我现在想把Top后面的1改为变量@Num，该怎么改呀！

------解决方案--------------------
需要使用動態SQL語句

CREATE PROC Proc_News
@Num INT,
@Class_Code1 VARCHAR(50),
@Class_Code2 VARCHAR(50),
@News_Hot VARCHAR(50)
AS
BEGIN
declare @s varchar(8000)

set @s = isnull(@s, ' ') +
'(SELECT TOP ' + ltrim(@Num) + 'News_Id,News_Title,News_SmallPicUrl,News_AddDate
FROM News,News_Class
WHERE NewsClass_Id=Class_Id AND Class_Code= ' ' ' + @Class_Code1 +
' ' ' AND News_Hot= ' ' ' + @News_Hot +
' ' ') UNION (SELECT TOP ' + ltrim(@Num) + ' ' ' News_Id,News_Title,News_SmallPicUrl,News_AddDate
FROM News,News_Class
WHERE NewsClass_Id=Class_Id
AND Class_Code= ' ' ' + @Class_Code2 +
' ' ' AND News_Hot= ' ' ' + @News_Hot +
' ' ') ORDER BY News_AddDate DESC '

exec(@s)
END
GO
------解决方案--------------------
CREATE PROC Proc_News
@Num INT,
@Class_Code1 VARCHAR(50),
@Class_Code2 VARCHAR(50),
@News_Hot VARCHAR(50)
AS
BEGIN
set rowcount @num
(SELECT News_Id,News_Title,News_SmallPicUrl,News_AddDate FROM News,News_Class WHERE NewsClass_Id=Class_Id AND Class_Code=@Class_Code1 AND News_Hot=@News_Hot) UNION (SELECT TOP 1 News_Id,News_Title,News_SmallPicUrl,News_AddDate FROM News,News_Class WHERE NewsClass_Id=Class_Id AND Class_Code=@Class_Code2 AND News_Hot=@News_Hot) ORDER BY News_AddDate DESC
set rowcount 0
END
GO

------解决方案--------------------
如果你用的Sql2005就不会有这个麻烦了，Top后面可以直接写变量的

如果是Sql2000，那么你要写动态Sql语句了，把所有的语句放到字符串里去执行

------解决方案--------------------
用动态sql语句如 echiynn(寶琲)所说
------解决方案--------------------
sql 2005 支持变量

------解决方案--------------------
动态SQL语句就可以了