Local variable bug

DanAvni

on the sql below, try replacing the @XXX variable with another local variable. sql prompt does not show the local variables. however if you go to another location on the sql (probable outside of the braces) then you can see the local variables

CREATE PROCEDURE [dbo].[GetAllTaskRoutesByCompanyGuidStartDateRegionGuid]
@CompanyGuid UNIQUEIDENTIFIER = NULL,
@StartDateFrom DATETIME = NULL,
@StartDateTo DATETIME = NULL,
@RegionGuid UNIQUEIDENTIFIER = NULL,
@STATUSMASK TINYINT
AS
SET NOCOUNT ON

SELECT *
FROM TaskRoute tr
WHERE tr.CompanyGuid = @CompanyGuid
AND ( tr.Status & @STATUSMASK ) > 0
AND [PlannedStartTime] BETWEEN @StartDateFrom
AND @StartDateTo
AND ( ([RegionGUID] = @RegionGuid AND @XXX)
OR @RegionGuid IS NULL
)
RETURN 0

GO

Ben Pinchen

Could you just confirm that when you manually type the variable in your code i.e.

AND ( ([RegionGUID] = @RegionGuid AND @XXX)

, you can successfully parse the SQL code using SQL Server? I've recreated this and when I have the following line as

..
AND ( ([RegionGUID] = @RegionGuid)
..

it parses successfully. However, as soon as I add the 'AND' and the second variable I get an 'Incorrect syntax' returned.

DanAvni

yes i can because the whole thing is corrent and was compiled and saved successfully. to reproduce just take the code and try to replace the @XXX with any other local variable. (e.g try to write instead of @XXX the term @StatusMask=1) you will not get any help from sql prompt in the variable name

Ben Pinchen

Ok I apologise, I've tried it again and I do get the same outcome as you (I'm not sure why it wasn't working for me first of all. Probably a silly typing error!).

Once you've typed the variable manually, SQL Prompt does recognise it and give you the object hint information. So there is a part of SQL Prompt that does recognise it, after it's been typed.

The developers are working on SQL Prompt 4 at the moment and completely rewriting the parser. These inconsistencies are what they're concentrating on ironing out. So I've logged this as a bug in our bug tracking software and this will be tested against the SQL Prompt 4 build.

For your reference the bug tracking code is: SP-1482