469,076 Members | 1,501 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,076 developers. It's quick & easy.

Sql Syntax Error!

3
Hey guys,

I am trying to create a stored procedure with this statement and it keeps telling me that my syntax is incorrect (enterprise manager). Please help... i am pulling my hair out here!

############################

CREATE PROCEDURE searchcandidate3
-- DECLARE parameters to determine select statement
@keywords varchar(1000),
@recent datetime,
@county varchar(200)

as

BEGIN
SET NOCOUNT ON

DECLARE @SQL varchar(1000)
SET @SQL =
'SELECT *
FROM candidate_search
WHERE (currentJobTitle LIKE '% + @keywords + % ' ) AND ( dateregistered >= '@recent') AND (countyid in '@county') OR
(targetJobTitle LIKE '%+@keywords+%') AND (dateregistered >= '@recent') AND (countyid in '@county') OR
(educationlevel LIKE '%+@keywords+%') AND (dateregistered >= '@recent') AND (countyid in '@county') OR
(language LIKE '%+@keywords+%') AND (dateregistered >= '@recent') AND (countyid in '@county') OR
(countyname LIKE '%+@keywords+%') AND (dateregistered >= '@recent') AND (countyid in '@county') OR
(townname LIKE '%+@keywords+%') AND (dateregistered >= '@recent') AND (countyid in '@county') OR
(skill LIKE '%+@keywords+%') AND (dateregistered >= '@recent') AND (countyid in '@county') OR
(sector LIKE '%+@keywords+%') AND (dateregistered >= '@recent') AND (countyid in '@county') OR
(jbekeywords LIKE '%+@keywords+%') AND (dateregistered >= '@recent') AND (countyid in '@county')'

EXEC(@SQL)
END


GRANT EXEC ON dbo.searchcandidate3 TO WebUser
GO

GRANT SELECT ON dbo.candidate_search TO WebUser
GO

########################################
Apr 2 '07 #1
3 1129
iburyak
1,017 Expert 512MB
Try this:

[PHP]
CREATE PROCEDURE searchcandidate3
-- DECLARE parameters to determine select statement
@keywords varchar(1000),
@recent datetime,
@county varchar(200)

as

BEGIN
SET NOCOUNT ON

DECLARE @SQL varchar(1000)
SET @SQL =
'SELECT *
FROM candidate_search
WHERE (currentJobTitle LIKE ''%' + @keywords + '%'' ) AND ( dateregistered >= ''' + cast(@recent as varchar(20)) + ''') AND (countyid in ''' + @county + ''') OR
(targetJobTitle LIKE ''%' + @keywords + '%'') AND (dateregistered >= ''' + cast(@recent as varchar(20)) + ''') AND (countyid in ''' + @county + ''') OR
(educationlevel LIKE ''%' + @keywords + '%'') AND (dateregistered >= ''' + cast(@recent as varchar(20)) + ''') AND (countyid in ''' + @county + ''') OR
(language LIKE ''%' + @keywords + '%'') AND (dateregistered >= ''' + cast(@recent as varchar(20)) + ''') AND (countyid in ''' + @county + ''') OR
(countyname LIKE ''%' + @keywords + '%'') AND (dateregistered >= ''' + cast(@recent as varchar(20)) + ''') AND (countyid in ''' + @county + ''') OR
(townname LIKE ''%' + @keywords + '%'') AND (dateregistered >= ''' + cast(@recent as varchar(20)) + ''') AND (countyid in ''' + @county + ''') OR
(skill LIKE ''%' + @keywords + '%'') AND (dateregistered >= ''' + cast(@recent as varchar(20)) + ''') AND (countyid in ''' + @county + ''') OR
(sector LIKE ''%' + @keywords + '%'') AND (dateregistered >= ''' + cast(@recent as varchar(20)) + ''') AND (countyid in ''' + @county + ''') OR
(jbekeywords LIKE ''%' + @keywords + '%'') AND (dateregistered >= ''' + cast(@recent as varchar(20)) + ''') AND (countyid in ''' + @county + ''')'

EXEC(@SQL)
END

GO
GRANT EXEC ON dbo.searchcandidate3 TO WebUser
GO

GRANT SELECT ON dbo.candidate_search TO WebUser
GO[/PHP]



You know, when I use procs like this to make sure I concatenate string correctly I replace

[PHP]EXEC(@SQL)
to
Select @SQL[/PHP]

Then I copy select statement and try to execute it. This way I can see result query and can detect my errors and see error messages. When query runs smoothly I change statement back to Execute.

In your case values of your variables where not seen inside your @SQL.
Also you missed GO at the end of your stored proc.

Good Luck.
Apr 2 '07 #2
olud
3
Bless you iburyak ... thanks a lot , it worked
Apr 2 '07 #3
iburyak
1,017 Expert 512MB
Thank you.... :)
I count my blessings you are first... LOL

Irina.
Apr 2 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Steve | last post: by
1 post views Thread by Donald Canton | last post: by
3 posts views Thread by Manuel | last post: by
1 post views Thread by Hari Sekhon | last post: by
7 posts views Thread by Josh | last post: by
Banfa
5 posts views Thread by Banfa | last post: by
1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.