Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

single quotes Expand / Collapse
Author
Message
Posted Tuesday, October 8, 2013 10:21 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, August 21, 2014 9:40 AM
Points: 984, Visits: 1,326
dwain.c (10/7/2013)
Kurt W. Zimmerman (10/7/2013)
A simple way to debug dynamic SQL is to simply print it out. Then copy/paste the output into a New Query window and see where your errors are.

I'm not a big fan of dynamic SQL but others before me were. There are so many times I have debugged dynamic SQL in that fashion.

FYI, right now if I have to resort to dynamic SQL it is only in one-off scripts for a single purpose.

Kurt


Anytime I write a SP that uses dynamic SQL I include a @debug parameter so that I can EXEC the SP with @debug=1 to print the SQL. Unfortunately, if it gets too long there are limitations on what can display in the Messages pane (4000 characters I think).


I do this as well. It has saved a lot of time debugging some of the more complex SPs. Great tip.

Kurt


Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman
Post #1502707
Posted Wednesday, October 9, 2013 12:25 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 9:39 AM
Points: 425, Visits: 1,678
Jeff Moden (10/8/2013)
dwain.c (10/7/2013)
Unfortunately, if it gets too long there are limitations on what can display in the Messages pane (4000 characters I think).


So don't do that anymore. I got this tip from opc.three.

--===== Declare a demonstration variable to store
-- a long string in.
DECLARE @SomeLongString VARCHAR(MAX)
;
--===== This just builds a long string of SELECTs.
-- Don't ever do something like this with
-- public facing string parameters because
-- it is concatenated dynamic SQL.
WITH
cteTally AS
(
SELECT TOP 1000
N = ROW_NUMBER()OVER(ORDER BY (SELECT 1))
FROM master.sys.all_columns ac1
CROSS JOIN master.sys.all_columns ac2
)
SELECT @SomeLongString
= ISNULL(@SomeLongString,'--')
+ REPLACE(REPLACE('
SELECT "This is string #<<N>>";
'
,'"','''')
,'<<N>>',RIGHT(N+10000,4))
FROM cteTally
OPTION (MAXDOP 1)
;
--===== Show the length of the string.
SELECT LengthOfString = LEN(@SomeLongString)
;
--===== Now, display the string in its entirety.
-- Run the code in the grid mode and then click on the XML to see
-- it all with indents and line breaks preserved.
SELECT @SomeLongString AS [processing-instruction(SomeMeaninglessLabel)]
FOR XML PATH(''), TYPE
;




Woah
Post #1503256
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse