January 26, 2012 at 5:50 am
Hi all,
I have the following query:
-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
alter PROCEDURE T
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @GARRISON nvarchar(255)
DECLARE @ASSETTYPE nvarchar(255)
DECLARE @INTERRUPT int
DECLARE @SCPCat nvarchar(255)
DECLARE @ACCUMTIME int
DECLARE @FIVal nvarchar(255) RETURN
-- Insert statements for procedure here
IF ((@GARRISON <> 'Netheravon'
AND @GARRISON <> 'Ludgershall')
AND @INTERRUPT >= 5 AND (@SCPCat = 'Electricity' OR @SCPCat = 'Assets'))
BEGIN
RETURN 'FI-1'
END
ELSE ((@GARRISON <> 'Netheravon'
AND @GARRISON <> 'Ludgershall') AND @ACCUMTIME > 25
AND @INTERRUPT < 5 AND (@SCPCat = 'Electricity' OR @SCPCat = 'Assets'))
BEGIN
RETURN 'FI-2'
END
END
GO
But keep getting the error Incorrect syntaxc near '@GARRISON' (line 28, which is the declaration). What is wrong with the syntax?
Thanks
January 26, 2012 at 5:59 am
You have a condition after the ELSE. ELSE statements don't take conditions. The syntax is
IF <condition>
<statement block>
ELSE
<statement block>
If you need to test one set of conditions then another different one, you need two IF blocks.
p.s. Line 28 is the line that starts ELSE (double click on the error and it will show you that), it's the line number of the batch, not the entire query window
Larger issue is that you are declaring variables and then using them in the IF without ever having assigned them values. At the point that you use @GARRISON, @ASSETTYPE, @INTERRUPT, etc they have no values. They are NULL. Hence they will never return true for any of the comparisons you have.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 26, 2012 at 9:44 am
That helped, thanks.
One more problem.
I want to call a stored proc from within another stored proc. Here is my code:
ELECT @OUT = EXEC [MUJV].[FailPassPI9]
GarrisonName,
(CONVERT (NVARCHAR (50), DateDiff(minute, CONVERT (CHAR (24), [PI9_StartDT], 108), CONVERT (CHAR (24), [PI9_EndDT], 108)) / 60)) + ' ' + 'Hrs' + ' ' + SUBSTRING(CONVERT (NVARCHAR (50), (DateDiff(minute, CONVERT (CHAR (24), [PI9_StartDT], 108), CONVERT (CHAR (24), [PI9_EndDT], 108)))), CHARINDEX('.', CONVERT (NVARCHAR (50), (DateDiff(minute, CONVERT (CHAR (24), [PI9_StartDT], 108), CONVERT (CHAR (24), [PI9_EndDT], 108)) / 60)), 2), 3) + ' ' + 'mins' ,
, SCPCategory,
(CONVERT (NVARCHAR (50), DateDiff(minute, CONVERT (CHAR (24), [PI9_StartDT], 108), CONVERT (CHAR (24), [PI9_EndDT], 108)) / 60)) + ' ' + 'Hrs' + ' ' + SUBSTRING(CONVERT (NVARCHAR (50), (DateDiff(minute, CONVERT (CHAR (24), [PI9_StartDT], 108), CONVERT (CHAR (24), [PI9_EndDT], 108)))), CHARINDEX('.', CONVERT (NVARCHAR (50), (DateDiff(minute, CONVERT (CHAR (24), [PI9_StartDT], 108), CONVERT (CHAR (24), [PI9_EndDT], 108)) / 60)), 2), 3) + ' ' + 'mins' ,
These are parameters in the order as set in the stored proc code. This fails, though, with syntax errors:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'EXEC'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '('.
January 26, 2012 at 9:55 am
You can't put expressions in the calling of a procedure. Declare variables, set the variables to the results of the expressions, use the variables in the EXEC statement.
Also, you don't use SELECT before the EXEC. It's
EXEC @ReturnValue = <stored procedure name> @ParameterName = <value>, ....
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 26, 2012 at 9:59 am
Apologies for all the questions!
How about this syntax?
SELECT @s-2 = (CONVERT (NVARCHAR (50), DateDiff(minute, CONVERT (CHAR (24), [PI9_StartDT], 108), CONVERT (CHAR (24), [PI9_EndDT], 108)) / 60)) + ' ' + 'Hrs' + ' ' + SUBSTRING(CONVERT (NVARCHAR (50), (DateDiff(minute, CONVERT (CHAR (24), [PI9_StartDT], 108), CONVERT (CHAR (24), [PI9_EndDT], 108)))), CHARINDEX('.', CONVERT (NVARCHAR (50), (DateDiff(minute, CONVERT (CHAR (24), [PI9_StartDT], 108), CONVERT (CHAR (24), [PI9_EndDT], 108)) / 60)), 2), 3) + ' ' + 'mins'
exec @OUT = [MUJV].[FailPassPI9] GarrisonName, @s-2, SCPCategory, @s-2
January 26, 2012 at 10:07 am
You know, the syntax is described in Books Online...
You've got unquoted string literals - GarrisonName, SCPCategory. They're not variables (no @ in front) and if they're supposed to be string literals they need to be wrapped in ''
Your select is referencing columns it appears, but without any table specified. How's SQL supposed to know where they come from?
What's that date manipulation trying to do anyway?
Also, how likely is it that a date is going to have unicode characters in it?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 26, 2012 at 10:56 am
There's another problem with the stored procedure definition in the first post. "Return" in procs can only work with integer values. It can't return strings, but it's defined to do so in the proc.
If it gets to any of those Return statements, it will give an error about not being able to convert to an integer.
Is all of this written in T-SQL, for Microsoft SQL Server, or is this for some other version of SQL?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply