Something that many find difficult with Dynamic SQL is debugging it. When looking at a batch that creates and executes a dynamic statement it can be daunting to understand where exactly the error is happening, or even where the SQL that is generating the error is coming from, as it might be a value that was injected, rather than part of the literal strings.
I've touched on this before, but to reiterative Formatting is Important. That doesn't just mean with your statements that are creating the dynamic SQL, it means ensuring that the dynamic SQL you create is well formatted as well. This is probably one if the harder things to keep in mind when writing dynamic SQL, as putting formatting into a literal string can feel quite "odd" to do. It's also very easy to forget about. The reason, however, that it's so important is because the first step to debugging a dynamic statement that is failing is to take the dynamic SQL statement and attempt to run it. The error will still be the same, but you can now actually see the SQL that's actually being run, and so it's far easier to debug.
The simplest way to get the dynamic statement is to use
SELECT the value, however, this isn't something you should blindly do in a stored procedure (as it couldn't easily break existing code).
SELECT too may not return the full length, depending on the application you are using; for example SSMS' default maximum length returned is around 64,000~ characters and I suspect ADS (Azure Data Studio) has a similar limitation; though I couldn't stop the setting at a quick glance.
Once you have the dynamic statement, copy and paste it into a new query window so you can run it. Note that if you are passing any parameters to the dynamic statement you'll need to declare these in the new window too, and assign values to them; though that work will mostly be done in the prior query window. Then, when you run the query, the same error will be presented to you, however, the position information of where the error is, such as the line number, will be far more meaningful. This, of course, further enforces why formatting is important, as if every thing in on a single line, then "error on line 1" is not helpful.
Spend time debugging the non-dynamic statement, and ensure you keep a note of the finalised changes you make to the query. Once you have the non-dynamic statement working correctly propagate those changes to your batch that creates the dynamic statement. The solution(s) you implement may not be exactly the same as the correction you made, due to the fact the statement is dynamic mind, but they will highlight what the underlying problem is. Some simple errors might be that a single quote hasn't been "double doubled up", as within a dynamic statement a string literal that has a single quote in it, would need 4:
N'DECLARE @String varchar(30) = ''You can''''t do that'';'. It might be that you have an value that is being injected, such as an object name or literal string, that isn't being properly quoted, so you need to make sure you wrap the injected value in
QUOTENAME or add a
REPLACE to escape the appropriate characters.
You may well find that as you fix one problem, you find another; don't be disheartened by this, it happens with non-dynamic SQL as well. Sometimes you might fix a problem, have the non-dynamic SQL run fine, propagate the solution to have it not run. Just ensure you
SELECT the code each time, if the generation is working correctly (as in you aren't getting an error with the SQL generating the dynamic statement).
If you're getting the wrong results from the query, then you'll likely be better off writing the statement you are expecting to have generated, then compare the two and find the the differences. Then check the SQL you're using to generate the statement and review that part that's generating the specific area that's different and make the needed corrections. These can be difficult to debug if you're dealing with creating the statement using multiple columns, so you one suggestion is to return the dataset that you're using, not as part of the dynamic statement, but in its "raw" form. Most likely a value you're expecting isn't there, or the opposite.
Other than that, trial and error, like any SQL is your friend. I can't really offer any full examples here. Every scenario is different and specific to the statement you have. As you debug each statement, you'll learn the things to look for first, and the likely candidates, but you'll also learn what things to watch out for as your write the initial statement.