大家帮忙看看这段存储过程，老是报错。找了半天了高手指点。
ALTER PROCEDURE dbo.sp_jobinfo_query_by_companyid
@CompanyID INT=0,
@CategoryID INT=0,
@Status INT=0,
@PageSize INT=15,
@StartPage INT=1,
@RecordCount INT=0
AS
SET @RecordCount=0
IF(@StartPage <1)
SET@StartPage=1
DECLARE @sql1 varchar(100)
DECLARE @sql varchar(8000)
DECLARE @sqlwhere varchar(4000)
CREATE TABLE #emp_table
(
[ID] [int]IDENTITY(1,1),
[JobID] [int],
[JobTitle][nvarchar](100),
[ExpeYear][nvarchar](50),
[CompanyID][int],
[EmpCount][int],
[PayCount][nvarchar](50),
[EduGrade][nchar](50),
[CategoryTxt][nvarchar](50),
[CompanyName][nvarchar](50),
[PublishDate][datetime],
[Sex][nvarchar](50)
)
SET @sql= 'INSERT INTO #emp_table(JobID,JobTitle,ExpeYear,CompanyID,EmpCount,EduGrade,PayCount,CategoryTxt,CompanyName,PublishDate,Sex)
SELECT
A.JobID,A.JobTitle,A.ExpeYear,A.CompanyID,A.EmpCount,A.EduGrade,A.PayCount,B.CategoryTxt,C.CompanyName,A.PublishDate,A.Sex
FROM JobInfo A INNER JOIN JobCategory B ON A.CategoryID=B.CategoryID INNER JOIN CompanyInfo C ON A.CompanyID=C.CompanyID '
SET @sqlwhere= 'WHERE A.Status> =0 '--A.Status= '+CAST(@Status AS VARCHAR(20))
IF(@CompanyID> 1)
SET @sqlwhere=@sqlwhere+ ' AND A.CompanyID= '+CAST(@CompanyID AS VARCHAR(20))
IF(@CategoryID> 0)
SET @sqlwhere=@sqlwhere+ ' AND A.CategoryID IN (SELECT CategoryID FROM JobCategory WHERE CategoryID= '+@CategoryID+ 'OR ParentID= '+@CategoryID+ ') '
IF(@Status> 0)
SET @sqlwhere=@sqlwhere+ ' AND GETDATE() <=DATEADD(DAY,A.ValueDays,A.PublishDate) '
else
SET @sqlwhere=@sqlwhere+ ' AND GETDATE()> DATEADD(DAY,A.ValueDays,A.PublishDate) '
SET @sqlwhere=@sqlwhere+ 'ORDER BY A.JobID DESC '
print @sqlwhere
SET @sql=@sql+@sqlwhere
IF(@@ERROR <> 0)
RETURN 0
EXEC(@sql)
IF(@@ERROR <> 0)
RETURN 0
IF(@PageSize=0)
SELECT *FROM #emp_table
ELSE
SET @sql1= 'SELECT TOP( '+@PageSize+ ') * FROM #emp_table
WHERE ID NOT IN(SELECT TOP ( '+@PageSize+ '* ( '+@StartPage+ '-1)+1) ID FROM #emp_table) '
exec(@sql1)
RETURN
报错：Line 4: Incorrect syntax near 'A '.
大家帮帮忙
[解决办法]
把表结构也给出来吧，光看代码真乱啊。
而且这里还有错误啊，怎么连临时表用完也不删除啊？
------解决方案--------------------
ALTER PROCEDURE dbo.sp_jobinfo_query_by_companyid
@CompanyID INT=0,
@CategoryID INT=0,
@Status INT=0,
@PageSize INT=15,
@StartPage INT=1,
@RecordCount INT=0
AS
SET @RecordCount=0
IF(@StartPage <1)
SET@StartPage=1
DECLARE @sql1 varchar(100)
DECLARE @sql varchar(8000)
DECLARE @sqlwhere varchar(4000)
CREATE TABLE #emp_table
(
[ID] [int]IDENTITY(1,1),
[JobID] [int],
[JobTitle][nvarchar](100),
[ExpeYear][nvarchar](50),
[CompanyID][int],
[EmpCount][int],
[PayCount][nvarchar](50),
[EduGrade][nchar](50),
[CategoryTxt][nvarchar](50),
[CompanyName][nvarchar](50),
[PublishDate][datetime],
[Sex][nvarchar](50)
)
SET @sql= 'INSERT INTO #emp_table(JobID,JobTitle,ExpeYear,CompanyID,EmpCount,EduGrade,PayCount,CategoryTxt,CompanyName,PublishDate,Sex)
SELECT
A.JobID,A.JobTitle,A.ExpeYear,A.CompanyID,A.EmpCount,A.EduGrade,A.PayCount,B.CategoryTxt,C.CompanyName,A.PublishDate,A.Sex
FROM JobInfo A INNER JOIN JobCategory B ON A.CategoryID=B.CategoryID INNER JOIN CompanyInfo C ON A.CompanyID=C.CompanyID '
SET @sqlwhere= 'WHERE A.Status> =0 '--A.Status= '+CAST(@Status AS VARCHAR(20))
IF(@CompanyID> 1)
SET @sqlwhere=@sqlwhere+ ' AND A.CompanyID= ' ' '+CAST(@CompanyID AS VARCHAR(20)) + ' ' ' ' --字符没加引号
IF(@CategoryID> 0)
SET @sqlwhere=@sqlwhere+ ' AND A.CategoryID IN (SELECT CategoryID FROM JobCategory WHERE CategoryID= '+@CategoryID+ ' OR ParentID= '+@CategoryID+ ') ' --or 前加空格
IF(@Status> 0)
SET @sqlwhere=@sqlwhere+ ' AND GETDATE() <=DATEADD(DAY,A.ValueDays,A.PublishDate) '
else
SET @sqlwhere=@sqlwhere+ ' AND GETDATE()> DATEADD(DAY,A.ValueDays,A.PublishDate) '
SET @sqlwhere=@sqlwhere+ 'ORDER BY A.JobID DESC '
print @sqlwhere
SET @sql=@sql+@sqlwhere
IF(@@ERROR <> 0)
RETURN 0
EXEC(@sql)
IF(@@ERROR <> 0)
RETURN 0
IF(@PageSize=0)
SELECT *FROM #emp_table
ELSE
SET @sql1= 'SELECT TOP( '+@PageSize+ ') * FROM #emp_table
WHERE ID NOT IN(SELECT TOP ( '+@PageSize+ '* ( '+@StartPage+ '-1)+1) ID FROM #emp_table) '
exec(@sql1)
你再试试
[解决办法]
將@PageSize ==> rtrim(@PageSize)
