How can we help you today? How can we help you today?

Local variable bug

on the sql below, try replacing the @... 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
DanAvni
0

Comments

3 comments

Sort by
  • 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.
    Ben Pinchen
    0
  • 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 @... with any other local variable. (e.g try to write instead of @... the term @StatusMask=1) you will not get any help from sql prompt in the variable name
    DanAvni
    0
  • 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
    Ben Pinchen
    0

Add comment

Please sign in to leave a comment.