how to concat n strings - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to concat n strings

Author

Topic

AAAV
Posting Yak Master

152 Posts

Posted - 2010-03-19 : 14:35:54

i am trying to get the concat of the text field in the syscomments for the same id in the colid order.
if there are 2 rows for a colid i must contac those 2 text columns. if there are 4 i must concat them. basically trying to get the Procedure function definitions.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-20 : 01:11:23

no need of using syscomments for that if you're using SQL 2005. just use

SELECT definition FROM sys.sql_modules WHERE OBJECT_NAME([object_id])='Your procedure or function name here'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2010-03-20 : 06:32:19

Why does sp_HelpText make such a mess of this then? Splitting long lines at 256 characters?

Only problem with

SELECT definition FROM sys.sql_modules

is that it can exceed the display limit (8,000 characters?), so need something that splits at line breaks

Fine if you are using it in an Application though ...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-22 : 03:30:38

sp_helptext, definition, object_definition all work fine if the result is exported to text file or any other format. The only problem is with QA's display

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-03-22 : 05:17:47

sp_HelpText wraps at 256 characters if I run it in SQL Text mode and export to file.

My Q.A. is set to column width = 8000 characters

"SELECT definition FROM sys.sql_modules" works if exported to a file (truncates the whole Sproc source at 8,000 characters if output to Results Pane)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-22 : 06:20:59

quote:
Originally posted by Kristen

sp_HelpText wraps at 256 characters if I run it in SQL Text mode and export to file.

My Q.A. is set to column width = 8000 characters

"SELECT definition FROM sys.sql_modules" works if exported to a file (truncates the whole Sproc source at 8,000 characters if output to Results Pane)

Have you tried using bcp?

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-03-22 : 07:57:16

quote:
Originally posted by madhivanan

Have you tried using bcp?

With sp_helpText? No - but I don;t think it will make any difference, there is "slicing" logic in sp_HelpText to split lines at 256 character (I have no idea why, I think they have overlooked "modernising" that Sproc")

I think it should "chunk" at Line Breaks that are less than 256 characters where possible - so return multiple rows that will work in QA / SSMS (provided that the column-width is configured to something longer than the max actual line length in the SProc)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources