Plz help me i am unable to execute this Stored pro

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)

Plz help me i am unable to execute this Stored pro

Author

Topic

pericherla
Starting Member

3 Posts

Posted - 2007-11-05 : 15:40:57

Where ever statement starts with AND irts saying as error

If @Rid = '' BEGIN
If @clerkCode <> '-1'
BEGIN
AND clerk_ID in (@clrCode) -- getAssignClerkStr(clerkCode)
If @strStatus <> '-1' BEGIN
If @strStatus = 'A' Or @strStatus = 'W'
AND STATUS = @strStatus AND AP_REVIEW_DATE IS NULL
END
IF @strStatus = 'R'
AND AP_REVIEW_DATE IS NOT NULL
END
END
ELSE
AND STATUS != 'A' AND STATUS != 'W'
End
End
If @strTime <> '-1' BEGIN
If @strTime = '1' BEGIN
and datediff(month, input_date, getdate()) < 1
END
If @strTime = '2'BEGIN
and datediff(month, input_date, getdate()) < 3
END
If @strTime = '3' BEGIN
and datediff(month, input_date, getdate()) < 6
END
If @strTime = '4' BEGIN
and datediff(month, input_date, getdate()) < 12
END
If @strTime = '5' BEGIN
and datediff(year, input_date, getdate()) > 1
End
End
ORDER BY NAME
END
Else
AND M.ID = @Rid
End

Will H
Yak Posting Veteran

56 Posts

Posted - 2007-11-05 : 15:48:43

Your syntax is just... wrong. You shouldn't have to nest everything into Begin/End blocks like that. Also, most of your statements could be combined into a single logical check instead of shotgunned everywhere. I'd suggest trying to start with just one conditional check and work your way from there.

Some boolean zen examples:

IF 1=1
BEGIN
IF 2=2
BEGIN
PRINT ':)'
END
END

is exactly equivalent to:

IF 1=1 AND 2=2
BEGIN
PRINT ':)'
END

But the latter is far easier to understand and maintain.

---------------------------------------------------------
SSRS Kills Kittens.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-05 : 15:48:56

Your syntax is way-off. You can't begin a statement with AND.

For instance:

If @strTime = '5' BEGIN
and datediff(year, input_date, getdate()) > 1

Should become:

If @strTime = '5' and datediff(year, input_date, getdate()) > 1
BEGIN

But at any rate, you can't do what you think you can do with your query. It appears you are attempty a form of dynamic SQL.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-05 : 16:01:13

I strongly believe that all "ands" are supposed to dynamically build the where clause.
Use CASE in favor of IF.

E 12°55'05.25"
N 56°04'39.16"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-11-05 : 16:26:00

The syntax isn't right. It's not even wrong.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources