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'.
大家帮帮忙
@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)你再试试
exec(@sql1)
上面的好像没有什么问题了
现在出了另外一个问题
Syntax error converting the varchar value 'SELECT TOP(' to a column of data type int.
sp4你帮忙看看。谢谢
也可以写成cast(@PageSize as varchar)你用的是动态SQL,要记得动态SQL组织起来要都用字符格式连接起来,转成varchar类型是必须的