Syntax Error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Syntax Error

Author

Topic

sunilsi
Starting Member

21 Posts

Posted - 2008-12-12 : 02:36:17

Can anyone help to find the syntax error in this query?

@query = 'select empID from EducationDepartment where DateDiff(hh, checkInTime, getdate()) > 2 and userPriority = 1 and status != "closed"'

Thx,
Linus

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-12 : 03:00:01

it should be two single quotes rather than "

@query = 'select empID from EducationDepartment where DateDiff(hh, checkInTime, getdate()) > 2 and userPriority = 1 and status != ''closed'''

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-12 : 03:29:21

quote:
Originally posted by sunilsi

Can anyone help to find the syntax error in this query?

@query = 'select empID from EducationDepartment where DateDiff(hh, checkInTime, getdate()) > 2 and userPriority = 1 and status != "closed"'

Thx,
Linus

Try this Once

@query = 'select empID from EducationDepartment where DateDiff(hh, checkInTime, getdate()) > 2 and userPriority = 1 and status NOT LIKE 'closed''

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-12 : 03:34:58

quote:
Originally posted by bklr

Try this Once

@query = 'select empID from EducationDepartment where DateDiff(hh, checkInTime, getdate()) > 2 and userPriority = 1 and status NOT LIKE 'closed''

Di dyou try it youurself? Or even PRINT the @query variable?

E 12°55'05.63"
N 56°04'39.26"

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-12 : 03:46:33

declare @query varchar(150)

select @query = 'select empID from EducationDepartment where DateDiff(hh, checkInTime, getdate()) 2 and userPriority = 1 and status NOT LIKE "closed"'

print @query

select @query

k try this it will works

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-12 : 03:51:28

No, it will not work.
The use doublequote character (ascii 34) will make SQL Server treat the nameclosed as a column name.
The suggested query will try to return all records where status column is equal to closed column.
And since the closed column does not exists, you get an error. Your suggestion is exactly the same query the original poster had problems with.

Visakh nailed it in his first response.

E 12°55'05.63"
N 56°04'39.26"

thiyagu_rind
Starting Member

46 Posts

Posted - 2008-12-12 : 03:57:19

Dear Linus,

It wont work use '(Single Quotes twice) instead of "(double quotes)
Try the below.

DECLARE @query VARCHAR(MAX)
SET @query = 'select empID from EducationDepartment where DateDiff(hh, checkInTime, getdate()) > 2 and userPriority = 1 and status != ''closed'''
PRINT @query
Regards
Thiyagarajan

sunilsi
Starting Member

21 Posts

Posted - 2008-12-12 : 07:33:23

Thanks Thiyagu & Guys...it worked!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-12 : 09:29:36

quote:
Originally posted by thiyagu_rind

Dear Linus,

It wont work use '(Single Quotes twice) instead of "(double quotes)
Try the below.

DECLARE @query VARCHAR(MAX)
SET @query = 'select empID from EducationDepartment where DateDiff(hh, checkInTime, getdate()) > 2 and userPriority = 1 and status != ''closed'''
PRINT @query
Regards
Thiyagarajan

Did you see what i posted? how's your suggestion different from mine?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources