CREATE FUNCTION dbo.GetSreachSql(@city int,@industry int,@position int ,@jobtype int,@releasetime datetime,@KEYWORD varchar(50))
returns table
AS
BEGIN
DECLARE @sql varchar(1000)
set @sql =' SELECT J.[ID],J.[Name],Enterprise.Mail,J.EnterpriseID, Enterprise.[Name] AS EnterpriseName,Enterprise.[Intro] AS EnterpriseInfo,EnterpriseType.[Name] AS Type, J.Salary,OtherLanguage,TimeLimit,J.Degree, [PubDate],[Amount],[Description],Enterprise.Scale, City.[Name] AS Address, J.Experience,J.AgeRequest,J.Status FROM JOB AS J ,Enterprise,EnterpriseType,City WHERE J.EnterpriseID=Enterprise.[ID] AND Enterprise.TypeID=EnterpriseType.ID AND Enterprise.Mail<>'' AND City.ID IN ( SELECT [CityID] FROM JobCity WHERE JobID = J.ID ) '; set @sql = @sql + 'and City.ID='+@city
set @sql = @sql + 'and Enterprise.ID in (SELECT EnterpriseID FROM EnterpriseIndustry WHERE IndustryID = '+@industry+')'
set @sql = @sql + 'and J.id in (select jobid from jobjobclass where jobclassid in (' + @position +'))'
if(@jobtype<>'')
set @sql = @sql + 'and J.TypeID in ( '+ @jobtype +' ) '
set @sql = @sql + ' and J.PubDate > dateadd(dd,-'+@releasetime+' ,getdate()) and J.PubDate < getdate()'
set @sql = @sql + 'and J.Name like '%'' + @KEYWORD + ''%' '
return exec(@sql)
END这个函数总是提示消息 102,级别 15,状态 31,过程 GetSreachSql,第 17 行
'BEGIN' 附近有语法错误。请问改怎么改一下,就想返回一个表
returns table
AS
BEGIN
DECLARE @sql varchar(1000)
set @sql =' SELECT J.[ID],J.[Name],Enterprise.Mail,J.EnterpriseID, Enterprise.[Name] AS EnterpriseName,Enterprise.[Intro] AS EnterpriseInfo,EnterpriseType.[Name] AS Type, J.Salary,OtherLanguage,TimeLimit,J.Degree, [PubDate],[Amount],[Description],Enterprise.Scale, City.[Name] AS Address, J.Experience,J.AgeRequest,J.Status FROM JOB AS J ,Enterprise,EnterpriseType,City WHERE J.EnterpriseID=Enterprise.[ID] AND Enterprise.TypeID=EnterpriseType.ID AND Enterprise.Mail<>'' AND City.ID IN ( SELECT [CityID] FROM JobCity WHERE JobID = J.ID ) '; set @sql = @sql + 'and City.ID='+@city
set @sql = @sql + 'and Enterprise.ID in (SELECT EnterpriseID FROM EnterpriseIndustry WHERE IndustryID = '+@industry+')'
set @sql = @sql + 'and J.id in (select jobid from jobjobclass where jobclassid in (' + @position +'))'
if(@jobtype<>'')
set @sql = @sql + 'and J.TypeID in ( '+ @jobtype +' ) '
set @sql = @sql + ' and J.PubDate > dateadd(dd,-'+@releasetime+' ,getdate()) and J.PubDate < getdate()'
set @sql = @sql + 'and J.Name like '%'' + @KEYWORD + ''%' '
return exec(@sql)
END这个函数总是提示消息 102,级别 15,状态 31,过程 GetSreachSql,第 17 行
'BEGIN' 附近有语法错误。请问改怎么改一下,就想返回一个表
解决方案 »
- 关于数据重复的筛选问题,求扫盲
- 请问DB-Library for C还能支持SQL Server 2008吗?
- 传递到 RIGHT 函数的长度参数无效。
- 数据库总是死锁,用活动监视器查看进程,发现有阻塞,不知如何查找如何产生的阻塞以及如何解决
- 初学SQL 问个菜鸟问题!!!
- SQL2005查询索引利用中的怪毛病
- 数据表格转换查询问题,请教各位高手!
- 下载的sqlserver2000怎么解密? ??
- 请问sql server里面的view可不可以导入access数据库?
- 主键、主关键字、索引的区别是什么?
- 求一个对我来说是超难的SQL语句
- SQL SERVER 配置ODBC 的问题!!! 高手进~!!!
As
BEGIN
DECLARE @sql varchar(1000)
set @sql =' SELECT J.[ID],J.[Name],Enterprise.Mail,J.EnterpriseID, Enterprise.[Name] AS EnterpriseName,Enterprise.[Intro] AS EnterpriseInfo,EnterpriseType.[Name] AS Type, J.Salary,OtherLanguage,TimeLimit,J.Degree, [PubDate],[Amount],[Description],Enterprise.Scale, City.[Name] AS Address, J.Experience,J.AgeRequest,J.Status FROM JOB AS J ,Enterprise,EnterpriseType,City WHERE J.EnterpriseID=Enterprise.[ID] AND Enterprise.TypeID=EnterpriseType.ID AND Enterprise.Mail<>'''' AND City.ID IN ( SELECT [CityID] FROM JobCity WHERE JobID = J.ID ) '; set @sql = @sql + 'and City.ID='+Cast(@city As Varchar)
set @sql = @sql + 'and Enterprise.ID in (SELECT EnterpriseID FROM EnterpriseIndustry WHERE IndustryID = '+Cast(@industry As Varchar)+')'
set @sql = @sql + 'and J.id in (select jobid from jobjobclass where jobclassid in (' + Cast(@position As Varchar) +'))'
if(@jobtype<>'')
set @sql = @sql + 'and J.TypeID in ( '+ Cast(@jobtype As Varchar) +' ) '
set @sql = @sql + ' and J.PubDate > dateadd(dd,-'+Convert(Varchar(10), @releasetime, 120) +' ,getdate()) and J.PubDate < getdate()'
set @sql = @sql + 'and J.Name like ''%' + @KEYWORD + '%'' '
Print @sql
exec(@sql)
END
GOCREATE PROCEDURE dbo.GetSreachSql(@city int,@industry int,@position int ,@jobtype int,@releasetime datetime,@KEYWORD varchar(50))
As
BEGIN
DECLARE @sql varchar(1000)
set @sql =' SELECT J.[ID],J.[Name],Enterprise.Mail,J.EnterpriseID, Enterprise.[Name] AS EnterpriseName,Enterprise.[Intro] AS EnterpriseInfo,EnterpriseType.[Name] AS Type, J.Salary,OtherLanguage,TimeLimit,J.Degree, [PubDate],[Amount],[Description],Enterprise.Scale, City.[Name] AS Address, J.Experience,J.AgeRequest,J.Status FROM JOB AS J ,Enterprise,EnterpriseType,City WHERE J.EnterpriseID=Enterprise.[ID] AND Enterprise.TypeID=EnterpriseType.ID AND Enterprise.Mail<>'''' AND City.ID IN ( SELECT [CityID] FROM JobCity WHERE JobID = J.ID ) '; set @sql = @sql + 'and City.ID='+Cast(@city As Varchar)
set @sql = @sql + 'and Enterprise.ID in (SELECT EnterpriseID FROM EnterpriseIndustry WHERE IndustryID = '+Cast(@industry As Varchar)+')'
set @sql = @sql + 'and J.id in (select jobid from jobjobclass where jobclassid in (' + Cast(@position As Varchar) +'))'
if(@jobtype<>'')
set @sql = @sql + 'and J.TypeID in ( '+ Cast(@jobtype As Varchar) +' ) '
set @sql = @sql + ' and J.PubDate > dateadd(dd,-'+Convert(Varchar(10), @releasetime, 120) +' ,getdate()) and J.PubDate < getdate()'
set @sql = @sql + 'and J.Name like ''%' + @KEYWORD + '%'' '
Print @sql
exec(@sql)
END
GO目前還有一個問題, 按照你的語句,@releasetime應該是int類型,而不應該是一個datetime類型。
As
BEGIN
DECLARE @sql varchar(1000)
set @sql =' SELECT J.[ID],J.[Name],Enterprise.Mail,J.EnterpriseID, Enterprise.[Name] AS EnterpriseName,Enterprise.[Intro] AS EnterpriseInfo,EnterpriseType.[Name] AS Type, J.Salary,OtherLanguage,TimeLimit,J.Degree, [PubDate],[Amount],[Description],Enterprise.Scale, City.[Name] AS Address, J.Experience,J.AgeRequest,J.Status FROM JOB AS J ,Enterprise,EnterpriseType,City WHERE J.EnterpriseID=Enterprise.[ID] AND Enterprise.TypeID=EnterpriseType.ID AND Enterprise.Mail<>'''' AND City.ID IN ( SELECT [CityID] FROM JobCity WHERE JobID = J.ID ) '; set @sql = @sql + 'and City.ID='+Cast(@city As Varchar)
set @sql = @sql + 'and Enterprise.ID in (SELECT EnterpriseID FROM EnterpriseIndustry WHERE IndustryID = '+Cast(@industry As Varchar)+')'
set @sql = @sql + 'and J.id in (select jobid from jobjobclass where jobclassid in (' + Cast(@position As Varchar) +'))'
if(@jobtype<>'')
set @sql = @sql + 'and J.TypeID in ( '+ Cast(@jobtype As Varchar) +' ) '
set @sql = @sql + ' and J.PubDate > dateadd(dd,-'+Cast(@releasetime As Varchar) +' ,getdate()) and J.PubDate < getdate()'
set @sql = @sql + 'and J.Name like ''%' + @KEYWORD + '%'' '
Print @sql
exec(@sql)
END
GO
1.組合動態語句沒有做類型轉換。2.單引號沒有控制好。3.按照你的語句,@releasetime應該是int類型,而不應該是一個datetime類型, 改為int型。
@jobtype的類型是int,那你使用in的作用是什麼?