none
como puedo hacer que mi store procedure me arroje mis resultados en una sola tabla y no en varias tablas RRS feed

  • Pregunta

  • ALTER PROCEDURE [dbo].[contador]

     NOTA:El CARDNUMBER LO TOMA DE OTRA TABLA POR ESO TIENE EL CICLO WHILE 

        -- Add the parameters for the stored procedure here
        @pCardNumber nChar (16)='',
    @pNameInCard nChar(26)='',
        @pCardHolder nChar(26)='',
        @pExpireDate Date= '0424',
        @pServiceCode nChar(3)='501'

    AS
    BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.

        SET NOCOUNT ON;
    DECLARE @Expire nChar(4),@CVV1 nChar(3),@CVV2 nChar(3)
    DECLARE @Track1 nVarchar(80),@Track2 nVarChar(40)

    SET NOCOUNT ON;


    declare @CardCount int
    declare @pCardNumbers int

       SET @CardCount=138


       --SELECT * FROM [dbo].[Tarjetas]

       select @pCardNumbers = COUNT(IdC) from [dbo].[Tarjetas]

       WHILE @CardCount<@pCardNumbers
        BEGIN 
      --WHILE (1=1)
    -- begin 
    set @pCardNumber = (SELECT Cards from [dbo].[Tarjetas] where IdC = @cardcount)

    set @CardCount = @CardCount +1 

      SET @pServiceCode = (SELECT ServiceCode FROM [dbo].[defBINTypes] WHERE Number = Left (@pCardNumber,6) )

    -- set @CardCount = @CardCount +1 
    Declare @ReplaceInvalids table
    (invalid nVarChar(10),valid nVarchar(2))

    Insert Into @ReplaceInvalids
    (invalid,Valid)
    SELECT InvalidCharacter='á',validCharacter='a'
    UNION SELECT 'é','e'
    UNION SELECT 'í','i'
    UNION SELECT 'ó','o'
    UNION SELECT 'ú','u'

    UNION SELECT 'à','a'
    UNION SELECT 'è','e'
    UNION SELECT 'ì','i'
    UNION SELECT 'ò','o'
    UNION SELECT 'ù','u'

    UNION SELECT 'â','a'
    UNION SELECT 'ê','e'
    UNION SELECT 'î','i'
    UNION SELECT 'ô','o'
    UNION SELECT 'û','u'

    UNION SELECT 'ñ','n'
    UNION SELECT 'õ','o'
    UNION SELECT 'ã','a'

    UNION SELECT 'Ð','Ñ'


    Select @pCardHolder=isNUll(stuff(@pCardHolder,charindex(invalid,@pCardHolder),len(invalid),valid),@pCardHolder)
    From @ReplaceInvalids

    SET @pCardHolder=[dbo].[TrimNonASCII](@pCardHolder)

    -- CardHolder
    SET @pCardHolder = @pCardHolder + UPPER(SPACE(26-Len(Replace(@pCardHolder,' ','*'))))



    Select @pNameInCard=isNUll(stuff(@pNameInCard,charindex(invalid,@pNameInCard),len(invalid),valid),@pNameInCard)
    From @ReplaceInvalids

    SET @pNameInCard=[dbo].[TrimNonASCII](@pNameInCard)

    -- CardHolder
    SET @pNameInCard = @pNameInCard + UPPER(SPACE(26-Len(Replace(@pNameInCard,' ','*'))))



    SET @CVV1 = Right('000'+Cast( ABS(CAST(NEWID() AS binary(6)) %999) + 1 as nVarchar),3)
    SET @CVV2 = Right('000'+Cast( ABS(CAST(NEWID() AS binary(6)) %999) + 1 as nVarchar),3)

    SET @Expire = Right(Cast(Year(@pExpireDate) as varChar),2)+Right('00'+Cast(Month(@pExpireDate) as varChar),2)

    SET @Track1 = '%B'
    + @pCardNumber 
    + '^'+@pCardHolder+'^'
    + @Expire
    + @pServiceCode 
    + '100000000000000'
    + @CVV1
    + '000000?'

    SET @Track2 = @pCardNumber
    +'='
    + @Expire
    + @pServiceCode 
    + '00000'
    + @CVV1 
    + '00000?'
    -- continue;
    -- break; 
      SELECT CardNumber=SUBSTRING(@pCardNumber,1,4)+' '+SUBSTRING(@pCardNumber,5,4)+' '+SUBSTRING(@pCardNumber,9,4)+' '+SUBSTRING(@pCardNumber,13,4)
      ,NameInCard = @pNameInCard
      ,CardHolder = @pCardHolder
      ,Expire=Right('00'+Cast(Month(@pExpireDate) as varChar),2)+'/'+Right(Cast(Year(@pExpireDate) as varChar),2)
      ,Track1 =@Track1
      ,Track2= @Track2
      ,CVV2 =@CVV2
      ,BarCode = @pCardNumber

    -- set @CardCount = @CardCount +1 
      END
       END

    jueves, 1 de agosto de 2019 2:40

Todas las respuestas

  • Hola daniluz:

    Puedes usar una variable de tabla o una tabla temporal para almacenar todas las iteraciones del bucle y a la salida de este retornar los resultados.

    ALTER PROCEDURE [dbo].[contador]
    (
        -- Add the parameters for the stored procedure here
        @pCardNumber nChar (16)='',
        @pNameInCard nChar(26)='',
        @pCardHolder nChar(26)='',
        @pExpireDate Date= '0424',
        @pServiceCode nChar(3)='501'
    )
    AS
    BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
    
    SET NOCOUNT ON;
    DECLARE @Expire nChar(4),@CVV1 nChar(3),@CVV2 nChar(3)
    DECLARE @Track1 nVarchar(80),@Track2 nVarChar(40)
    
    SET NOCOUNT ON;
    
    declare @CardCount int
    declare @pCardNumbers int
    Declare @ReplaceInvalids table
    (invalid nVarChar(10),valid nVarchar(2))
    
    Declare @Resultados table (CardNumber nChar(16), NameInCard nChar(26), CardHolder nChar(26), Expire varchar(8), Track1 NVarChar(80), Track2 NvarChar(80),
    CVV2 NChar(3), BarCode NChar(16));
    
    SET @CardCount=138;
    SET @pCardNumbers = (select COUNT(IdC) from [dbo].[Tarjetas]);
    
    WHILE @CardCount<@pCardNumbers
       BEGIN 
        set @pCardNumber = (SELECT Cards from [dbo].[Tarjetas] where IdC = @cardcount)
        set @CardCount = @CardCount +1 
        SET @pServiceCode = (SELECT ServiceCode FROM [dbo].[defBINTypes] WHERE Number = Left (@pCardNumber,6) )
    
        Insert Into @ReplaceInvalids
        (invalid,Valid)
        Select t.InvalidCharacter, t.validCharacter
    	   from 
    	   (Values 
    	      ('á','a'),('é','e'),('í','i'),('ó','o'),('ú','u'),
    	      ('à','a'),('è','e'),('ì','i'),('ò','o'),('ù','u'),
    	      ('â','a'),('ê','e'),('î','i'),('ô','o'),('û','u'),
    	      ('ñ','n'),('õ','o'),('ã','a'),
    	      ('Ð','Ñ')
    	   ) as t (InvalidCharacter,validCharacter);
    
        Set @pCardHolder = (Select isNUll(stuff(@pCardHolder,charindex(invalid,@pCardHolder),len(invalid),valid),@pCardHolder)
    						  From @ReplaceInvalids);
    
        SET @pCardHolder=[dbo].[TrimNonASCII](@pCardHolder);
    
    -- CardHolder
        SET @pCardHolder = @pCardHolder + UPPER(SPACE(26-Len(Replace(@pCardHolder,' ','*'))));
    
        SET @pNameInCard = (Select isNUll(stuff(@pNameInCard,charindex(invalid,@pNameInCard),len(invalid),valid),@pNameInCard)
    					   From @ReplaceInvalids);
    
        SET @pNameInCard=[dbo].[TrimNonASCII](@pNameInCard);
    
    -- CardHolder
        SET @pNameInCard = @pNameInCard + UPPER(SPACE(26-Len(Replace(@pNameInCard,' ','*'))));
    
        SET @CVV1 = Right('000'+Cast( ABS(CAST(NEWID() AS binary(6)) %999) + 1 as nVarchar),3);
        SET @CVV2 = Right('000'+Cast( ABS(CAST(NEWID() AS binary(6)) %999) + 1 as nVarchar),3);
        
        SET @Expire = Right(Cast(Year(@pExpireDate) as varChar),2)+Right('00'+Cast(Month(@pExpireDate) as varChar),2);
    
        SET @Track1 = CONCAT(N'%B', @pCardNumber , N'^',@pCardHolder,N'^',@Expire, @pServiceCode ,N'100000000000000', @CVV1, N'000000?');
    
        SET @Track2 = CONCAT(@pCardNumber,N'=', @Expire, @pServiceCode ,N'00000', @CVV1 ,N'00000?');
    -- continue;
    -- break; 
        Insert into @Resultados
    	   SELECT Concat (SUBSTRING(@pCardNumber,1,4),N' ',SUBSTRING(@pCardNumber,5,4),N' ',SUBSTRING(@pCardNumber,9,4),N' ',SUBSTRING(@pCardNumber,13,4))
    	   , @pNameInCard
    	   , @pCardHolder
    	   , CONCAT(Right('00'+Cast(Month(@pExpireDate) as varChar),2),N'/',Right(Cast(Year(@pExpireDate) as varChar),2))
    	   , @Track1
    	   , @Track2
    	   , @CVV2
    	   , @pCardNumber;
    
    -- set @CardCount = @CardCount +1 
      END
    END
    
    Select * from @Resultados;

    jueves, 1 de agosto de 2019 3:47