★o★大家来看看/ 存储过程 /的问题★

★o★大家来看看/ 存储过程 /的问题★

CREATE PROCEDURE proName
(
@ShowNum int,
@SortID int
)

AS

DECLARE @SQL VARCHAR(100)
DECLARE @NUM INT
SET @NUM = @ShowNum


SET @SQL = 'SELECT TOP '+CAST(@NUM AS VARCHAR(20))+ ' * From News where SortID =@SortID or SortID IN (Select ID from NewsSort where ParentID = @SortID) order by ID desc '

EXEC(@SQL)
GO

-------------
VS.net 2005 中使用 SqlDataSource绑定时

报错信息：

执行查询时出错。请检查命令的语法以及参数的类型和值（如果有）。确保他们正确。
必须声明变量 '@SortID '。
第1行： 'ParentID '附近有语法错误。

高手指点
---------------------
把后面where条件去掉时，这个存储过程是可以使用的。




[解决办法]

CREATE PROCEDURE proName
(
@ShowNum int,
@SortID int
)

AS

DECLARE @SQL VARCHAR(100)
DECLARE @NUM INT
SET @NUM = @ShowNum


SET @SQL = 'SELECT TOP '+CAST(@NUM AS VARCHAR(20))+ ' * From News where SortID = '+@SortID+ ' or SortID IN (Select ID from NewsSort where ParentID = '+ @SortID+ ') order by ID desc '

EXEC(@SQL)
GO


[解决办法]
SET @SQL = 'SELECT TOP '+CAST(@NUM AS VARCHAR(20))+ ' * From News where SortID = '+ltrim(@SortID)+ ' or SortID IN (Select ID from NewsSort where ParentID = '+ltrim(@SortID)) + ' order by ID desc '
[解决办法]
SET @SQL = 'SELECT TOP '+CAST(@NUM AS VARCHAR(20))+ ' * From News where SortID = ' + @SortID+ ' or SortID IN (Select ID from NewsSort where ParentID = ' +@SortID + ') order by ID desc '

句改成
[解决办法]
CREATE PROCEDURE proName
(
@ShowNum int,
@SortID int
)

AS

DECLARE @SQL NVARCHAR(4000)
DECLARE @NUM INT
SET @NUM = @ShowNum


SET @SQL = 'SELECT TOP '+CAST(@NUM AS VARCHAR(20))+ ' * From News where SortID =@SortID or SortID IN (Select ID from NewsSort where ParentID = @SortID) order by ID desc '

EXEC sp_execute @SQL,N '@SortID int ',@SortID
GO

[解决办法]
拼句的候，後面的量有理

CREATE PROCEDURE proName
(
@ShowNum int,
@SortID int
)

AS

DECLARE @SQL VARCHAR(100)
DECLARE @NUM INT
SET @NUM = @ShowNum


SET @SQL = 'SELECT TOP '+CAST(@NUM AS VARCHAR(20))+ ' * From News where SortID = '+CAST(@SortID AS VARCHAR(20))+ ' or SortID IN (Select ID from NewsSort where ParentID = '+CAST(@SortID AS VARCHAR(20))+ ') order by ID desc '

EXEC(@SQL)
GO