Prev: sys.dm_db_missing_index_*
Next: Case statement strangeness
From: mahmad on 26 Feb 2010 11:24
Hi,
I have a query setup as a trigger below in sql 7:

declare arow cursor for select sonitem, spart, soq, sdisc, sunitprice,
sprice from salesitems where sona = @xson and soq>0


open arow
while 1=1
begin
fetch next from arow into @xitem,@xpart,@xqty,@xsdisc,@xsunitp,@xsprice
if @@fetch_status <> 0 break
select @xmess1 = @xmess1 +ltrim(convert(char(4),@xitem)) + "
"+@xpart + " " +ltrim(convert(char(10),@xsdisc))+
" " +ltrim(convert(char(10),@xqty)) + " "
+(convert(char(10),@xsunitp)) + " " +(convert(char(10),@xsprice))
+char(10) + char(10)

select @xmess1= @xmess1 + partdesc from partmaster where partnum = @xpart

select @xmess1= @xmess1 + sfsoiline from soifree where sfsona = @xson
and sfsonitem = @xitem
select @xmess1 =@xmess1 + char(10) + char(10)
end
close arow
deallocate arow

end
which outputs the follwoing text on an email

1 LOCK PORTION 0 2 0 0

LOCK PORTION ASSEMBLYtext for item 1

how can i separate the text, which you can see on the above line "text for
item 1"

this text needs to on the next line so i need a carriage return. How can i
do this.

thanks for your help

M

From: John Bell on 27 Feb 2010 07:04
On Fri, 26 Feb 2010 08:24:06 -0800, mahmad
<mahmad(a)discussions.microsoft.com> wrote:

>Hi,
>I have a query setup as a trigger below in sql 7:
>
>declare arow cursor for select sonitem, spart, soq, sdisc, sunitprice,
>sprice from salesitems where sona = @xson and soq>0
>
>
> open arow
> while 1=1
> begin
> fetch next from arow into @xitem,@xpart,@xqty,@xsdisc,@xsunitp,@xsprice
> if @@fetch_status <> 0 break
> select @xmess1 = @xmess1 +ltrim(convert(char(4),@xitem)) + "
>"+@xpart + " " +ltrim(convert(char(10),@xsdisc))+
> " " +ltrim(convert(char(10),@xqty)) + " "
>+(convert(char(10),@xsunitp)) + " " +(convert(char(10),@xsprice))
>+char(10) + char(10)
>
>select @xmess1= @xmess1 + partdesc from partmaster where partnum = @xpart
>
> select @xmess1= @xmess1 + sfsoiline from soifree where sfsona = @xson
>and sfsonitem = @xitem
> select @xmess1 =@xmess1 + char(10) + char(10)
> end
> close arow
> deallocate arow
>
> end
>which outputs the follwoing text on an email
>
>1 LOCK PORTION 0 2 0 0
>
>LOCK PORTION ASSEMBLYtext for item 1
>
>how can i separate the text, which you can see on the above line "text for
>item 1"
>
>this text needs to on the next line so i need a carriage return. How can i
>do this.
>
>thanks for your help
>
>M

Hi

I am not sure why you are doing this in a trigger sending emails from
a trigger could be time consuming and therefore prolong any
transactions and cause blocking issues.

You are already using CHAR(10) to do line feeds if you need carriage
returns add CHAR(13), If you know the position when you want these
characters you can use STUFF to insert them into the string and
CHARINDEX if you want to find the location of a string. e.g

DECLARE @txt varchar(max);
SET @txt = 'LOCK PORTION ASSEMBLYtext for item 1'

SELECT CHARINDEX('text for item',@txt), STUFF(@txt,CHARINDEX('text for
item',@txt),0,CHAR(13))

John
 | 
Pages: 1
Prev: sys.dm_db_missing_index_*
Next: Case statement strangeness