Incorrect Syntax using IF statement

Hi,

I'm new to SQL Server Programming, I work with ASP a lot, but lately
I've been trying to create Stored Procedures, etc. I'm having a
problem writing a simple IF statement.. I don't seem to understand why
it's giving me this error. I've search around on Google Groups, but I
still don't get it.

=================
USE msdb

IF NOT EXISTS (SELECT * FROM sysjobs WHERE name = 'Scheduled Nightfax')

END
=================

My error is:
Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'END'.

Thanks for any help.you need a BEGIN for every END
example

DECLARE @.v BIT
SELECT @.v = 1

IF @.v = 1
BEGIN
select 'yes'
END
ELSE
BEGIN
select 'No'
END

Or without begin...end
IF @.v = 1
select 'yes'
ELSE
select 'No'

Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Thanks, I played with it a bit and I got the result I was looking for.

==============
USE msdb
DECLARE @.JobName varchar(255)
SELECT @.JobName = name FROM sysjobs WHERE name = 'Scheduled Nightfax'

IF @.JobName = 'Scheduled Nightfax'
PRINT 'YES'
ELSE
PRINT 'NO'
==============

Incorrect syntax near the keyword IF

I am writing a user defined function and I get the Error 156: Incorrect
syntax near the keyword IF. My function looks like this
CREATE FUNCTION dbo.func1(@.var1 varchar(64))
RETURNS @.MaintCost TABLE (@.result1 varchar(64), @.result2 varchar(64),
@.result3 varchar(64))
AS
IF @.var1 = 'I'
BEGIN
INSERT @.MaintCost
SELECT Col1, Col2, Col3
FROM tbl1
WHERE Col3 = 'I'
RETURN
END
ELSE
BEGIN
INSERT @.MaintCost
SELECT Col1, Col2, Col3
FROM tbl1
WHERE Col3 <> 'I'
RETURN
Thanks for the Help
ENDCREATE FUNCTION dbo.func1(@.var1 varchar(64))
RETURNS @.MaintCost TABLE (@.result1 varchar(64), @.result2 varchar(64),
@.result3 varchar(64))
AS
BEGIN
...
END
AMB
"Keith" wrote:

> I am writing a user defined function and I get the Error 156: Incorrect
> syntax near the keyword IF. My function looks like this
> CREATE FUNCTION dbo.func1(@.var1 varchar(64))
> RETURNS @.MaintCost TABLE (@.result1 varchar(64), @.result2 varchar(64),
> @.result3 varchar(64))
> AS
> IF @.var1 = 'I'
> BEGIN
> INSERT @.MaintCost
> SELECT Col1, Col2, Col3
> FROM tbl1
> WHERE Col3 = 'I'
> RETURN
> END
> ELSE
> BEGIN
> INSERT @.MaintCost
> SELECT Col1, Col2, Col3
> FROM tbl1
> WHERE Col3 <> 'I'
> RETURN
> Thanks for the Help
> END|||Try this:
CREATE FUNCTION dbo.func1(@.var1 varchar(64))
RETURNS @.MaintCost TABLE
(
result1 varchar(64),
result2 varchar(64),
result3 varchar(64)
)
AS
BEGIN
IF @.var1 = 'I'
BEGIN
INSERT @.MaintCost
SELECT Col1, Col2, Col3
FROM tbl1
WHERE Col3 = 'I'
END
ELSE
BEGIN
INSERT @.MaintCost
SELECT Col1, Col2, Col3
FROM tbl1
WHERE Col3 <> 'I'
END
RETURN
END

Incorrect syntax near the keyword Declare.

Dear Group,

I am trying to create a view and keep getting the Incorrect syntax near the
keyword 'Declare'" error.

Here is the code I am writing.

Create view fixed_airs (sid, fad_a2, fad_a3) as
Declare @.sid int,
@.fad_a2 int,
@.fad_a3 int
select @.sid=cast(substring(subject_id,1,8)as int) ,
@.fad_a2 =cast (substring(fad_2_4,1,1) as int),
@.fad_a3=cast(substring(fad_2_4,2,1) as int)
from parentpacket.

Thanks for the help in advance.

Jeff MagouirkJeff Magouirk wrote:
> Dear Group,
> I am trying to create a view and keep getting the Incorrect syntax near the
> keyword 'Declare'" error.
> Here is the code I am writing.
> Create view fixed_airs (sid, fad_a2, fad_a3) as
> Declare @.sid int,
> @.fad_a2 int,
> @.fad_a3 int
> select @.sid=cast(substring(subject_id,1,8)as int) ,
> @.fad_a2 =cast (substring(fad_2_4,1,1) as int),
> @.fad_a3=cast(substring(fad_2_4,2,1) as int)
> from parentpacket.
> Thanks for the help in advance.
> Jeff Magouirk

You keep getting syntax errors because you're using illegal syntax in
your CREATE VIEW statement. :D You can't use DECLARE, nor can you pass
in variables to a view. Check Books Online for proper syntax. But, in a
nutshell, you can only use a SELECT statement in a view.

Zachsql

Incorrect results from FTS on multiple columns

I'm writing a FTS query which needs to search on two different columns.

E.g. Table contains "Location" and "LocationDescription" columns.
Both columns are FT indexed.
The query also uses AND/OR operators to filter out the results.

I found the following article which gives the solution to the same problem.
Link: http://support.microsoft.com/default.aspx?scid=kb;en-us;286787
Is this problem associated with SQL Server 2005 also?

Making a third column which hold data from first two column is the
only solution or is there any other way to acheive better results?

Shailesh Patel...

The KB article only applies to SQL Server 7.0 as indicated. If you are having problems with your implementation please post a repro script with some sample data and the expected results.

|||My database contains the following structure:
Table name: Resumes
Columns:
ResumeID bigint identity(1,1),
Title varchar(100),
ResumeText varchar(max),
Skills varchar(250)

The ResumeID is set as primary key and columns Title, ResumeText and Skills
are FT indexed.

A record contains "Software Developer .NET" in Title,
resume text which also contains word "india" in ResumeText and
"C#, ASP.NET and SQL Server" in Skills columns.

If I pass a query as ("software" AND "developer" AND "india" AND "asp.net") in CONTAINSTABLE or CONTAINS which looks for a resume which statisfies this condition.

The queries which I wrote search on all the column which are FT indexed as a
result of which even if I pass the above query it does not returns any result.

It seems that the FT query is working on each columns individually.
That's why if I pass ("software" AND "developer") in a query it locates the record.

Should I create another column and dump the data of
Title, ResumeText and Skills in it and perform a FTS on this new column?
or
Is there any other feasible method to solve this problem?