★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