菜鸟函数问题

[Small__Wolf]

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' 附近有语法错误。

请问改怎么改一下,就想返回一个表

[Small__Wolf]

sqlserver2005中

[paoluo]

函數中，不能使用動態SQL語句，改用存儲過程吧。

[paoluo]

代碼有很多問題，暫時修改為如下

1.組合動態語句沒有做類型轉換。

2.單引號沒有控制好。



CREATE 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
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類型。

[paoluo]

怎麼復制重復了，重新發一次。

代碼有很多問題，暫時修改為如下

CREATE PROCEDURE dbo.GetSreachSql(@city int,@industry int,@position int ,@jobtype int,@releasetime Int,@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,-'+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型。

[paoluo]

還有疑問就是，

set @sql = @sql + 'and J.TypeID in ( '+ Cast(@jobtype As Varchar) +' ) '


@jobtype的類型是int，那你使用in的作用是什麼？