首页 新闻 赞助 找找看

SQL SERVER为什么报这个错啊Incorrect syntax near

0
悬赏园豆:20 [已解决问题] 解决于 2010-07-26 13:27

这个是我的存储过程

CREATE PROCEDURE [dbo].[Pagination]
@Columns VARCHAR(500), -- The columns to be displayed, divide by comma
@Tablename VARCHAR(100), -- The name of the table to be searched
@OrderColumnName VARCHAR(100), -- The name of the column to be used in order
@Order VARCHAR(50), -- The order method, ASC or DESC
@Where VARCHAR(100), -- The where condition, if there is not conditon use 1=1
@PageIndex INT, -- Current page index
@PageSize INT, -- The size of the page
@PageCount INT OUTPUT -- The total page count,define as output parameter
AS
BEGIN
DECLARE @SqlRecordCount NVARCHAR(100) -- The SQL Statement to get the total count of the records
DECLARE @SqlSelect NVARCHAR(1000) -- The SQL SELECT statment
SET @SqlRecordCount = N'SELECT @RecordCount = COUNT(*) FROM' + @Tablename + ' WHERE ' +@Where
DECLARE @RecordCount INT
EXEC sp_executesql @SqlRecordCount, N'@RecordCount INT OUTPUT',@RecordCount OUTPUT -- Transfer the parameter dynamic

IF(@RecordCount % @PageSize = 0)
SET @PageCount = @RecordCount / @PageSize
ELSE
SET @PageCount = @RecordCount / @PageSize + 1

SET @SqlSelect = N'SELECT ' + @Columns + ' FROM(SELECT ROW_NUMBER() OVER (ORDER BY ' + @OrderColumnName
    +' ' + @Order + ') AS tempid, * FROM ' 
    + @Tablename + ' WHERE ' + @Where + ') AS tempTableName WHERE tempid
    BETWEEN ' + STR((@PageIndex - 1)*@PageSize + 1) + ' AND ' + STR(@PageIndex * @PageSize)
EXEC(@SqlSelect)
END

 

运行下面这个的时候

DECLARE @PageCount int
EXEC Pagination    'fname','employee','fname','DESC','1=1',1,20,@PageCount

 

SQL SERVER 报这个错误

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'FROMemployee'. 

(20 row(s) affected)

有Result 返回,但是为什么报这么一个错误啊。

 

Debug 了下

这个是动态的生成的SQL语句

SELECT fname FROM(SELECT ROW_NUMBER() OVER (ORDER BY fname DESC) AS tempid, * FROM employee WHERE 1=1) AS tempTableName WHERE tempid
    BETWEEN          1 AND         20

 

这句话我单独在SQL SERVER分析查询器里,运行没有错误,但是在存储过程里为什么有这个错误(⊙o⊙)?

类型安全的心的主页 类型安全的心 | 初学一级 | 园豆:147
提问于:2010-07-26 11:28
< >
分享
最佳答案
0

错误是Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'FROMemployee'.

在这句 SET @SqlRecordCount = N'SELECT @RecordCount = COUNT(*) FROM' + @Tablename + ' WHERE ' +@Where

FROM' + @Tablename + ' ,from后面少了个空格,加个空格试试:FROM ' + @Tablename + '

收获园豆:10
kyo-yo | 大侠五级 |园豆:5587 | 2010-07-26 11:54
我检查过,那里是有空格的 你看下动态生成的SQL 语句就知道,那里空格是有的
类型安全的心 | 园豆:147 (初学一级) | 2010-07-26 12:59
其他回答(1)
0

SET @SqlRecordCount = N'SELECT @RecordCount = COUNT(*) FROM' + @Tablename + ' WHERE ' +@Where
from后面差一个空格

收获园豆:10
dege301 | 园豆:2825 (老鸟四级) | 2010-07-26 12:50
我检查过,那里是有空格的 你看下动态生成的SQL 语句就知道,那里空格是有的
支持(0) 反对(0) 类型安全的心 | 园豆:147 (初学一级) | 2010-07-26 12:57
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册