0

In SSMS (SQL Server Management Studio) debugger shows question mark for substring's return value when called from a function

I have a query with these two lines

SELECT  SUBSTRING(N'אאא',1,1);
SELECT dbo.testsubstring5(N'א');

I put a breakpoint on the first and hit debug.

As you can see SUBSTRING works fine so far. That's when outside of the function call.

enter image description here

Now we when we go inside dbo.testsubstring5 then for some reason SUBSTRING doesn't output the correct result in the debugger, though it does still work correctly outside of the debugger.

Look at the watch window of the debugger in the picture below

enter image description here

Notice the problem, that

SUBSTRING(@str,1,1) fails, it gives the result of ?

Also as you see in that watch window, UNICODE(SUBSTRING(@str,1,1)) gives the value of 63 which is the ASCII value of question mark.

The correct values are returned in the program though, for example, the watch window shows the correct values for @zzz, @zzz1, and @zzz2

So it seems to be an issue with the debugger window , for substring, when substring is called within a function. I must be missing something though I don't know what.

Abdul Hameed
  • 1,025
  • 12
  • 27
barlop
  • 12,887
  • 8
  • 80
  • 109
  • I don't see any description of what `testsubstring5` is supposed to be doing. – Tim Biegeleisen Apr 19 '18 at 06:11
  • @Larnu There are a few substring lines, which one are you referring to? I don't have a list of numbers. I don't know why you speak of concatenation, i'm doing no concatenation. And Substring takes a string and two numbers https://www.w3schools.com/sql/func_sqlserver_substring.asp – barlop Apr 19 '18 at 06:56
  • @TimBiegeleisen testsubstring5 you see all the lines of it, it is just doing some assignments and i'm looking at the value of expressions in the watch/debug window, its purpose is just to provide material for me to cross check that the watch/debug window is giving the correct result, and the watch/debug window is not doing so, for the cases I mention in the question. Where you get `?` and `63`. – barlop Apr 19 '18 at 06:58
  • @Larnu Also I think you're confused when you say I have a string outside of quotation marks, `N'אאא'` is not a literal string outside of quotation marks. Writing N before a literal string that contains unicode characters, is how you specify unicode. See for example the answer https://stackoverflow.com/questions/49723210/how-do-i-write-a-create-table-query-that-makes-a-unicode-supporting-table-in-sm – barlop Apr 19 '18 at 07:04
  • @Larnu - I suspect we have some unicode/RTL breakage here. If you C&P the code shown here into an SSMS window the string and numbers get reassembled into a correct `SUBSTRING` call. – Damien_The_Unbeliever Apr 19 '18 at 07:14
  • Yep, @Damien_The_Unbeliever is right, seems that the browser didn't like the mix. I've deleted the comment. – Thom A Apr 19 '18 at 07:19
  • @Damien_The_Unbeliever That's not the issue. That is interesting but not the issue 'cos that line (the first of the two), works, with no breakage within SMSS(though granted the line gets jumbled looking in eg notepad. but as mentioned, reassembles fine in SMSS. It's when you go into the procedure called by the second line, that the problem with the watch window is. And that's with a substring call that is passed a variable, no funny problem with quotes there. – barlop Apr 19 '18 at 07:52
  • @barlop - I was responding to Larnu's comment. I don't know how your question looks to you but I (and Larnu) are seeing your first code block as containing `SUBSTRING(N'1,1,'` followed by three identical characters (sorry, not sure on the name of the character) followed by the closing bracket. As I say, when that's copied into an SSMS window it's displayed correctly but that's what Larnu was saying seemed to be a "wrong" `SUBSTRING` call. – Damien_The_Unbeliever Apr 19 '18 at 07:56
  • @Damien_The_Unbeliever I know what you are replying to, as I was, and I can see very well and can see that it reassembles fine as Lamu said. But that line is not the problem i'm describing in my question. That line runs fine. If it makes it easier for you I can even comment out that line. The point of that line was to show you the case where the watch window works fine. My question is about the watch window. Not about how notepad or forums don't correctly display that particular line that isn't relevant to my actual question. – barlop Apr 19 '18 at 07:57
  • I think for now what I might do as a workaround , within the function, is simply use assignments like @asdf=SUBSTRING(.....) 'cos those are getting the correct results.. and I won't watch expressions with unicode cos those are getting question marks. – barlop Apr 19 '18 at 15:29

1 Answers1

-1

SQL Server seems to have some issues displaying UNICODE characters in grid (result or watch). If you try to print output to TEXT (CTRL + T) rather that debugging, it should print correct value.

Edited with image of result for the guy who voted the answer down.

Result in watch windows while debugging

Result in watch windows while debugging

Result when printed to TEXT

Result when printed to TEXT

Seems that problem with result grid is resolved. I am using SSMS version 2012 and don's see this issue now. I remember having this issue in older version of SSMS. But this is still an issue with debug.

barlop
  • 12,887
  • 8
  • 80
  • 109
Sandeep
  • 333
  • 2
  • 7
  • I don't see a "print output to text(Ctrl+T)" option . What menu is it in? – barlop Apr 19 '18 at 07:01
  • @barlop `Query` -> `Results To` -> `Results to Text (Ctrl+t)`. – Thom A Apr 19 '18 at 11:29
  • @Larnu thanks.. I just tried it.. but i'm debugging the function testsubstring5, and if I put it within that then I get the error https://i.imgur.com/JyCEPq9.png "Invalid use of a side-effecting operator 'PRINT' within a function." – barlop Apr 19 '18 at 15:28