Script 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)

Script Error

Author

Topic

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2007-11-15 : 15:15:38

Hi
I'm fairly new T-sql, I've produced a stored procedure to run two different 'Select' statements depending on the parameter entered, but I cannot get the script to work.

The script is as follows:

CREATE PROCEDURE usp_ECG_CallLogByVenueORBB

(@inVAr int)

AS
IF (@inVAr = 0) THEN
BEGIN

SELECT

0
,b.Username
,b.Forename
,b.Surname
,cl.Extension
,cl.CallTime
,cl.CallType
,cl.CallNumber
,cl.CallCost
,cl.CallDuration
,cl.EXT2
,cl.DDI
FROM [EC.CallInfo] cl
INNER JOIN tblBod AS b ON cl.CallNumber = b.Phone

UNION ALL

SELECT
0
,b1.username
,b1.forename
,b1.surname
,cl1.Extension
,cl1.CallTime
,cl1.CallType
,cl1.CallNumber
,cl1.CallCost
,cl1.CallDuration
,cl1.EXT2
,cl1.DDI

FROM [EC.CallLogs] AS cl1
INNER JOIN tblBod AS b1 ON cl1.CallNumber = b1.Mobile

IF (@inVAr = 1) THEN
BEGIN
SELECT
1,
ven.VenueID
,ven.[Name]
,ven.ContactName
,cl.Extension
,cl.CallTime
,cl.CallType
,cl.CallNumber
,cl.CallCost
,cl.CallDuration
,cl.EXT2
,cl.DDI

FROM [EC.CallLogs] AS cl
INNER JOIN tblVenue AS ven ON cl.CallNumber = ven.Telephone

GO

Does anyone know what ther errors are?

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-15 : 15:18:01

Your IF statements aren't formed correctly. You are missing END statements.

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

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2007-11-15 : 15:28:50

As in?

CREATE PROCEDURE usp_ECG_CallLogByVenueORBB

(@inVAr int)

AS
IF (@inVAr = 0) THEN
BEGIN

SELECT

0
,b.Username
,b.Forename
,b.Surname
,cl.Extension
,cl.CallTime
,cl.CallType
,cl.CallNumber
,cl.CallCost
,cl.CallDuration
,cl.EXT2
,cl.DDI
FROM [EC.CallInfo] cl
INNER JOIN tblBod AS b ON cl.CallNumber = b.Phone

UNION ALL

SELECT
0
,b1.username
,b1.forename
,b1.surname
,cl1.Extension
,cl1.CallTime
,cl1.CallType
,cl1.CallNumber
,cl1.CallCost
,cl1.CallDuration
,cl1.EXT2
,cl1.DDI

FROM [EC.CallLogs] AS cl1
INNER JOIN tblBod AS b1 ON cl1.CallNumber = b1.Mobile

END

IF (@inVAr = 1) THEN
BEGIN
SELECT
1,
ven.VenueID
,ven.[Name]
,ven.ContactName
,cl.Extension
,cl.CallTime
,cl.CallType
,cl.CallNumber
,cl.CallCost
,cl.CallDuration
,cl.EXT2
,cl.DDI

FROM [EC.CallLogs] AS cl
INNER JOIN tblVenue AS ven ON cl.CallNumber = ven.Telephone

END

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-15 : 15:30:55

Remove "THEN" from your code. Also read up on how to construct proper IF statements in SQL Server Books Online. This is very basic syntax.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources