Options

Table varaible suggestion enclosed in square brackets

rldawgrldawg Posts: 35 Bronze 3
edited June 1, 2016 12:21PM in SQL Prompt
I am declaring a table variable and then when I use the table variable, the suggestion is enclosing it in square brackets erroneously. When a variable is enclosed in brackets, then SQL thinks I am referring to a column in a table. Invalid column name
	DECLARE @AssignmentTypes Job.udtbl_AssignmentType  -- User defined table

-- Column in select statement
				CASE
					WHEN EmpConflict.ConflictExists = 1
						THEN Job.fn_GetJobConflictList_Employee(@ClientId, @EmployeeId, 
								JESD.EmployeeScheduleDate, JESD.EmployeeScheduleId, 
								[@AssignmentTypes])    -- suggestion puts the variable @AssignmentTypes in square brackets, bad
					ELSE ''
				END AS ResourceDayInfo,
Tagged:
· Share on Twitter

Comments

  • Options
    Ali DAli D Posts: 56 New member
    edited August 1, 2017 11:01AM
    Hi

    I'm sorry to hear you're having trouble with adding square brackets. I wasn't able to reproduce the issue using your script. Are you highlighting the entire script then running "Add square brackets" (Ctrl + B, Ctrl + B )?

    What version of Prompt are you using? If you're not on the latest version (7.2.2.273), could you upgrade and see if the behaviour is the same?

    Thanks
    Ali
    · Share on Twitter
  • Options
    rldawgrldawg Posts: 35 Bronze 3
    1. I am using the latest version of SQL prompt
    2. Ctrl+B Ctrl+B removes square brackets, it does not add them.
    3. Ctrl+B Ctrl+B does not remove the square brackets around my table variable because that's what it has in its suggestion list.
    4. That's what I am trying to convey to you is that SQL prompt shows @AssignmnentTypes udtbl_AssignmentTypes as a variable in its suggestion list, but when I select it, it puts [@AssignmnentTypes ] which is not correct
    5. I can get it to do this consistently
    6. When I type the following code in, when I am typing my SQL statements and filling out the parameters to the function below, @AssignmnentTypes is shown in the SQL Prompt suggestion list, and when I select it, SQL prompt erroneously puts square brackets around the variable. Which when I try to create or alter the stored proc, I get an error that the column name is not defined. Because when there is square brackets around the name of the variable, SQL Server thinks it's a column name in a table somewhere. An @ variable of any kind can not have brackets around it.
    	DECLARE @AssignmentTypes Job.udtbl_AssignmentType
	INSERT @AssignmentTypes
	SELECT ID FROM dbo.fn_GetSplitValues(@ScheduleAssigmentTypeList)

		WITH EmpConflict 
		AS (
			SELECT  EmployeeId, ConflictDate, ConflictExists
			FROM Job.fn_EmployeeScheduleConflictDates(@ClientId, @EmployeeId, @BeginDate,
													[@AssignmentTypes]) -- wrong
			UNION 
			SELECT  EmployeeId, ConflictDate, ConflictExists
			FROM Job.fn_EmployeeScheduleNoConflictDates(@ClientId, @EmployeeId, 
														@BeginDate, [@AssignmentTypes]) -- wrong
			UNION 
			SELECT  EmployeeId, ConflictDate, ConflictExists
			FROM Job.fn_EmployeeScheduleUnscheduledDates(@ClientId, @EmployeeId, 
						@ResourceScheduleId, NULL, @BeginDate, [@AssignmentTypes])  -- wrong
			)
    · Share on Twitter
  • Options
    Ali DAli D Posts: 56 New member
    Apologies, I misunderstood the problem you were having. I’ll take another look at reproducing it.
    · Share on Twitter
  • Options
    Ali DAli D Posts: 56 New member
    edited August 1, 2017 11:08AM
    Hi

    We’ve been able to reproduce this behaviour and have implemented a fix in this private build.

    Normally inserting square brackets around table variables is useful for accessing columns. We’ve now added some logic to check whether the parameter is a table type before surrounding it with square brackets.

    Please could you download the private build and let us know if it works as you’d expect? If so, we’ll include it in the next public release.

    Thanks
    Ali
    · Share on Twitter
  • Options
    rldawgrldawg Posts: 35 Bronze 3
    Hello Ali,
    The private build works perfectly! I went back and modified the proc that I used in the example I provided to you and the suggestion list properly inserted the table variable without square brackets into my T-SQL code.

    Thank you and the team for the efforts!
    Robert
    · Share on Twitter
Sign In or Register to comment.