SELECT name, number, type, low, high, statusĪfter reviewing the data, and the condition it looks like it’s not a problem with the data. Interestingly, if I uncomment the LIKE condition and comment the IS NOT NULL condition I still get nothing back. And if I uncomment out the LIKE condition I get back nothing. SELECT name, number, type, low, high, status
So let’s add to the WHERE clause so pull back just the row I want. I’m going to pick one row that I’m not getting, but I think I should be. But if I run this: SELECT name, number, type, low, high, status
On my machine (I’ll explain why in a minute) this returns nothing. From there I can decide if it’s a logic problem or a data problem.įor example SELECT name, number, type, low, high, status Then I can toggle back and forth (commenting and uncommenting conditions) until I’m certain what’s the problem. Next, I start removing pieces of the where clause until it shows up (I might add an additional condition to ONLY pull the rows I want). The first thing I do is figure out one or two rows that aren’t there but should be. For example, if I’m not getting enough rows of data back from a SELECT statement.
#Azure data studio debug stored procedure code#
Comment/uncomment out pieces of code to isolate problems. It could have been it could have been it could have even been a piece of code like DATEADD(hour, a particularly long piece of code where I’m getting a fairly non-descript error, I’ve just thrown those PRINT 1, PRINT 2 etc statements all through the SP just to narrow down where the error is coming from.Īnyway, you get the idea. Also, while I’m at it I’m printing out the appropriate variable. I’ve done prints that are just numbers ( 1, 2, 3 etc) but it’s generally best to do something descriptive. That’s because IF only effects the next line and I want to run multiple lines of code within the condition. Now I want to know if/when it’s going into each of the possible if conditions. Anyway, obviously, I’m not incrementing since it’s always coming back as 0. I could use a SELECT instead and neither of those would have been necessary. Also, because of the way I’m handling this, if the variable is null the label won’t show up. Next, I printed out a label to go with the variable so I know what it is. It can be embarrassing to have odd text printing out when it makes it into production. This can become very important when you have a few thousand lines of code and you’ve been working on it for a while. Either that or put something you can search on like maybe –Ken’s debug print before each of them. I always put them on the far left so that they are easy for me to find and remove. SET = DATEADD(hour, 'M圜ounter ' + as varchar(20)) Ī few things of note here: I break formatting for these things. So at the end of the loop print out and DECLARE datetime = getdate() SET = DATEADD(hour, out could be useful but most likely the first thing we want to do is find out why this is in a never ending loop. Kind of a contrived example, but let’s say we need to know what’s going on here. PRINT vs SELECT is pretty subjective and frequently you can use either or. It’s also a great way to see which paths your code is taking. This is a great way to find out what’s in a variable, or maybe what the query for an EXISTS statement is going to return. So what can we do instead? I have three major categories of debugging techniques. Not to mention if you have to figure out why this query isn’t pulling the data you expect in production. Of course, that requires sysadmin access and getting that can be problematic in anything but your own personal box. And technically, you can do this with SQL Server if you have access to the debugger. Now, if you are a developer you are probably used to being able to step through your code, set watch values to see the contents of variables, etc.
Use what you find works best for you.ĭebugging stored procedures, functions, even views is something I end up doing quite a bit. Change INSERT/UPDATE/DELETE statements to SELECTs.ĭisclaimer: These are techniques I use all the time. Tl dr Add PRINT and/or SELECT statements.
0 kommentar(er)
