存储过程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语句就可以了
