newbie in sotre procedures

[yordangs]

here its my escenario i need to get the mas id from a table doing the next query select max(folio+1) as Folio from Folios, then i need to get this Folio and replace in string with a 6 number format example 001111

DECLARE @Tram varchar(800)
SET @Folio = 1111111 the value from above query im put explicit to be understand it, with 6 digits and fill it with 00 to the left in case of the folio its smaller than the format need it
SET @Tram = 'BCI11000010510121438510 0010250000000000001100000000010250000000000000NOW0000000000000000000000000000'
SELECT STUFF(@tram, 31, 6, @Folio) as trama

this return me this
BCI11000010510121438510 1111110000000000001100000000010250000000000000NOW0000000000000000000000000000

but i need this

BCI11000010510121438510 Folio0000000000001100000000010250000000000000NOW0000000000000000000000000000


any one can help me ???

 

[jbenson001]

What exactly is your question? Do you want to know how to get the max(folio)? How to pad it? ..etc


Jim

 

[DotNetGnat]

your question is not completely clear...try this:

DECLARE @Tram varchar(800)
SET @Tram = 'BCI11000010510121438510 0010250000000000001100000000010250000000000000NOW0000000000000000000000000000'

SELECT STUFF(@tram, 31, 6, 'Folio') as trama

-DNG

 

[yordangs]

sorry here im again i need to replace the @Folio variable in SELECT STUFF(@tram, 31, 6, '@Folio') as trama with the results of
select max(folio+1) as Folio from Folios who i can do that ???

 

[yordangs]

and then i need if the result of select max(folio+1) as Folio from Folios is example 111 i need this format 000111 ans replace it in the string

 

[yordangs]

well i do it this way may be isnt the best but its the way it work, any one have a suguestion to solve this by another way

 

[yordangs]

Alter PROCEDURE Proc_ObtieneFolioIBS
@tram varchar(1000),
@NumeroFolio int = 0 OUTPUT
AS
DECLARE @Tramarespuesta varchar(800)
declare @strFolio varchar(6)
set nocount on
BEGIN TRAN
select @NumeroFolio=max(folio+1) from Folios
select @strFolio=right('000000000000'+convert(varchar(6), @NumeroFolio),6)
SELECT @Tramarespuesta= STUFF(@tram, 31, 6, @strFolio)
select NumeroFolio=@strFolio,Tramarespuesta=@Tramarespuesta
Insert Into Folios (TramaEnvio,FolioenvioIbs) values (@Tramarespuesta,@strFolio)
COMMIT TRAN
set nocount on