Line Break in T-SQL

Hello,

In a Stored Proc, I am building a string variable. I am getting outputs
from 4 different queries and would like the string to have line breaks
to display each entry in a different line in a text area. How can I do
this?

i.e
result = result1 + result2 + result3 + result4.
What characters can I enter so that the output is displayed in the
textarea as
result1
result2
result3
result4

Thanks,SELECT 'asdfasd'+char(13)+'ASDF ASF ASDF ASD'

maybe it's CHR not CHAR|||The line terminator on a Windows platform is carriage return/line feed
(ASCII 10 and 13). You can concatenate CHAR(13) + CHAR(10) where you want
line breaks.

--
Hope this helps.

Dan Guzman
SQL Server MVP

<nashak@.hotmail.comwrote in message
news:1158285904.128023.10670@.e3g2000cwe.googlegrou ps.com...

Quote:

Originally Posted by

Hello,
>
In a Stored Proc, I am building a string variable. I am getting outputs
from 4 different queries and would like the string to have line breaks
to display each entry in a different line in a text area. How can I do
this?
>
i.e
result = result1 + result2 + result3 + result4.
What characters can I enter so that the output is displayed in the
textarea as
result1
result2
result3
result4
>
Thanks,
>

|||On 14 Sep 2006 19:05:04 -0700, nashak@.hotmail.com wrote:

Quote:

Originally Posted by

>Hello,
>
>In a Stored Proc, I am building a string variable. I am getting outputs
>from 4 different queries and would like the string to have line breaks
>to display each entry in a different line in a text area. How can I do
>this?

Hi Nashak,

In addition to the answers already given by Alexander and Dan, here's
another option - just use a newline character inside a string constant.

For example:

SELECT 'First line
Second line.'

This will show the output on two lines (make sure to select output to
text, not output to grid - the grid doesn't handle newlines too well).

If the data comes from columns, try

SELECT Column1 + '
' + Column2
FROM YourTable
WHERE ...

--
Hugo Kornelis, SQL Server MVP

Limiting concurrent execution of a T-SQL stored procedure

Hello,

I have a quite large set of legacy stored procedures. The stored procedures are not reentrant, i.e. can not safely be executed in parallel by multiple threads. The procedures are not called multiple times simultaneously in the system, because of this restriction. There's however no explicit constraint to prevent multiple calls to these stored procedures and thus it's possible to e.g. accidentally call the procedures many times simultaneously.

My problem is, how to prevent multiple simultaneous executions of the stored procedures? Is there a native way to do this? In modern programming languages there are language constructs to prevent simultaneous access to code and variables (synchronized in Java, lock in C#). Is there something similar in T-SQL? What could be a workaround?

One options could be to use SQL Server jobs to prevent simultaneous accesss - only one instance of a job can be running at time. This is, however, problematic since if the job cannot be started because another instance is already running, the second instance will never be started. This is not what I won't.

JM

Refer to Books Online about the use of sp_getapplock and sp_releaseapplock.

Also, refer to this post. And this one.

|||

Thanks Arnie for your quick and good answer!

r,

JM

|||

Yes..

You can achive it using few tricks..using sp_getapplock.

But you need a wrapper storedproc or batch for this. Check the following example it will help you to desing your wrapper sp/batch statement.

Sample:

Code Snippet

Create table MyTable

(

RowId int identity(1,1),

HitStartedAt datetime,

HitTimestamp datetime,

UserName varchar(100)

)

Go

Create proc LegacyProc (@.user varchar(100), @.CalledTime datetime)

as

Begin

Insert Into MyTable

Values(@.CalledTime, getdate(), @.user);

--To wait for 10 sec : not required for your procedures, producing the latency to check the concurrent users action

WAITFOR DELAY '000:00:10'

End

Go

Create Proc MyProc

(

@.user varchar(100)

)

as

Begin

Declare @.PorcName as NVarchar(1000), @.CalledTime datetime

Begin Tran

--To get the Current SP Name, it should be unique for each SP / each batch

SET @.PorcName =object_name(@.@.ProcID)

SET @.CalledTime = Getdate()

--Lock the Current Proc

Exec sp_getapplock @.Resource = @.PorcName, @.LockMode = 'Exclusive'

--Execute Your Legacy Procedures

Exec LegacyProc @.user, @.CalledTime

--Release the lock

Exec sp_releaseapplock @.Resource = @.PorcName

Commit Tran

End