473,216 Members | 1,363 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,216 software developers and data experts.

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 1293
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

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

Similar topics

1
by: Steve | last post by:
I just spent waaaaaaaaaaaayy too much time trying to track down an error that was incorrectly reported just now, and I would like to see if someone can explain to me why it was reported that way. ...
1
by: Donald Canton | last post by:
Hi, I'm using Bjarne's book to learn C++ and am stuck on the Calc program in Section 6. Everything works fine except when I try to use istringstream to parse a token from the command line. I...
5
by: r.nikhilk | last post by:
Hi, Currently, we are porting C++ applications from 32 bit to 64 bit on AIX platform. (The current version of AIX is 5.3 and xlC verison is 8.0). We are able to compile the applications by...
2
by: david | last post by:
Anyone could give me a hand about this syntax error? Thank you. David Source Code: Dim conn As New SqlConnection(strConn) Dim daAngio As New SqlDataAdapter(strSelectStatement, conn) 'Create a...
3
by: Manuel | last post by:
I'm trying to compile glut 3.7.6 (dowbloaded from official site)using devc++. So I've imported the glut32.dsp into devc++, included manually some headers, and start to compile. It return a very...
1
by: Hari Sekhon | last post by:
I've written an except hook into a script as shown below which works well for the most part and catches exceptions. import sys def myexcepthook(type,value,tb): do something ...
7
by: Josh | last post by:
I have a lot of except Exception, e statements in my code, which poses some problems. One of the biggest is whenever I refactor even the triviallest thing in my code. I would like python to...
7
by: bryant | last post by:
Hi all. I am new to ASP and working in Expression Web. The following query displays the information I need in the gridview for a single record. SELECT "OE_HDR"."ORD_NO", "OE_HDR"."CUST_NAM",...
6
by: muby | last post by:
Hi everybody :) I'm modifying a C++ code in VC++ 2005 my code snippet void BandwidthAllocationScheduler::insert( Message* msg, BOOL* QueueIsFull,
5
Banfa
by: Banfa | last post by:
So I have a little problem, I have a template class and that class contains a template function; now what I want to do is declare that function in the class (or indeed the entire class) as a friend...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.