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 [email protected]_Code1 AND [email protected]_Hot) UNION (SELECT TOP 1 News_Id,News_Title,News_SmallPicUrl,News_AddDate FROM News,News_Class WHERE NewsClass_Id=Class_Id AND [email protected]_Code2 AND [email protected]_Hot) ORDER BY News_AddDate DESC
END
GO
[email protected]，该怎么改呀！
------解决方案--------------------
需要使用動態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 [email protected]_Code1 AND [email protected]_Hot) UNION (SELECT TOP 1 News_Id,News_Title,News_SmallPicUrl,News_AddDate FROM News,News_Class WHERE NewsClass_Id=Class_Id AND [email protected]_Code2 AND [email protected]_Hot) ORDER BY News_AddDate DESC
set rowcount 0
END
GO
------解决方案--------------------
如果你用的Sql2005就不会有这个麻烦了，Top后面可以直接写变量的
如果是Sql2000，那么你要写动态Sql语句了，把所有的语句放到字符串里去执行
------解决方案--------------------
用动态sql语句如 echiynn(寶琲)所说
------解决方案--------------------
sql 2005 支持变量
------解决方案--------------------
动态SQL语句就可以了