Get an Output from a Stored Procedure

  • Oscar_Boots

    SSC Veteran

    Points: 294

    Hi Forum,

    I have an SP developed by Pinal Dave which is great for removing all text from a given table value. I want to apply the same logic via a Stored Proc that loops down a Column of a table, removes the text characters & then outputs the results to a temp table, external .csv or whatever is easiest.

    Can anyone help here?

    Thanks

    SET NOCOUNT ON
    DECLARE @loop INT
    DECLARE @str VARCHAR(8000)
    SELECT @str = 'ab123ce234fe'
    SET @loop = 0
    WHILE @loop < 26
    BEGIN
    SET @str = REPLACE(@str, CHAR(65 + @loop), '')
    SET @loop = @loop + 1
    END
    SELECT @str

    What I want to do is insert the code into a SQL Server Stored Proc & get the results to output.

    My attempts with execute meant the request would run for ages.

    Below is my attempt to do what I need but I'm missing an efficient output method.

    Can anyone assist?

    Thanks

    USE [Reports]
    GO

    CREATE PROCEDURE sp_remove_ph_no_text (@Telephone VARCHAR(MAX))
    AS
    SET NOCOUNT ON

    DECLARE @id INT
    DECLARE @MAXCOUNT INT

    SET @id = 1
    SET @MAXCOUNT = (SELECT COUNT(id) FROM [dbo].[phone_dataset]) -- Table 'phone_dataset' has 2 Columns, 'id' & 'Telephone'

    WHILE @id < @MAXCOUNT

    BEGIN

    SET @Telephone = (SELECT Telephone1 FROM [dbo].[phone_dataset] WHERE [id] = @id)

    SET @Telephone = REPLACE(@Telephone, CHAR(65 + @id),'')

    SET @id = @id + 1
    --OUTPUT TO SOMEWHERE???

    END
    GO

    --EXECUTE sp_remove_ph_no_text (Telephone)
  • Jeff Moden

    SSC Guru

    Points: 994238

    First of all, using a loop in a stored procedure is one of the slowest things you can do.  Only an rCTE (recursive CTE) would make it slower.

    My suggestion is to read the following article to understand what a "TALLY Table" is in SQL Server so that you'll have a better understanding of the answer I'm working on for you.  Here's the link...

    https://www.sqlservercentral.com/articles/the-numbers-or-tally-table-what-it-is-and-how-it-replaces-a-loop-1

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Oscar_Boots

    SSC Veteran

    Points: 294

    Thanks Jeff,

    I'm happy to learn, I'll have a read.

    Thanks again.

  • Jeff Moden

    SSC Guru

    Points: 994238

    Ok, first you need to follow the link to the "fnTally" function in my signature lines below this post.  It's a high performance iTVF (inline Table Valued Fuction) that we can use in place of the normal inline cCTEs (cascading CTEs) that me and a lot of others have been using.  This is one of the few times where a function calling a function isn't a bad thing.

    Once you've created the fnTally function, then create this one.  It has an interesting history where a fellow and good friend by the name of Eirikur Eiriksson and I kept build on the other's previous work to make things faster and faster for this type of thing.  Here's the code.

    CREATE FUNCTION dbo.DigitsOnly
    /******************************************************************************************************************************
    Purpose:
    Given a VARCHAR(8000) or less string, return only the numeric digits from the string.
    Programmer's Notes:
    1. This is an iTVF (Inline Table Valued Function) that will be used as an iSF (Inline Scalar Function) in that it returns a
    single value in the returned table and should normally be used in the FROM clause as with any other iTVF.
    2. CHECKSUM r eturns an INT and will return the exact number given if given an INT to begin with. It's also faster than a CAST
    or CONVERT and is used as a performance enhancer by changing the BIGINT of ROW_NUMBER() to a more appropriately sized INT.
    3. Another performance enhancement is using a WHERE clause calculation to prevent the relatively expensive XML PATH
    concatentation of empty strings normally determined by a CASE statement in the XML "loop".
    4. Another performance enhancement is not making this function a generic function that could handle a pattern. That allows
    us to use all integer math to do the comparison using the high speed ASCII function convert characters to their numeric
    equivalent. ASCII characters 48 through 57 are the digit characters of 0 through 9.
    5. Last but not least, added another of Eirikur's later optimizations using 0x7FFF which he says is a "simple trick to shift
    all the negative values to the top of the range so a single operator can be applied, a lot less expensive than using
    between.

    Kudos:
    1. Hats off to Eirikur Eiriksson for the ASCII conversion idea and for the reminders that dedicated functions will always
    be faster than generic functions and that integer math beats the tar out of character comparisons that use LIKE or
    PATINDEX.
    2. Hats off to all of the good people that submitted and tested their code on the following thread. It's this type of
    participation and interest that makes code better. You've just gotta love this commmunity.
    http://www.sqlservercentral.com/Forums/Topic1585850-391-2.aspx#bm1629360

    Usage:
    --===== CROSS APPLY example
    SELECT ca.DigitsOnly
    FROM dbo.SomeTable
    CROSS APPLY dbo.DigitsOnly(SomeVarcharCol) ca
    ;
    Revision History:
    Rev 00 - 29 Oct 2014 - Jeff Moden - Initial Creation
    Re 01 - 13 Aug 2019 - Jeff Moden - Simplified the code by changing the cascading CTEs that formed the sequence to a call to
    an equivalent fnTally function and incorporate Eirikur's last performance enhancement.
    ******************************************************************************************************************************/
    --===== Declare the I/O for this function
    (@pString VARCHAR(8000))
    RETURNS TABLE WITH SCHEMABINDING AS
    RETURN
    SELECT DigitsOnly =
    (
    SELECT SUBSTRING(@pString,N,1)
    FROM dbo.fnTally(1,ISNULL(LEN(@pString),0)) t
    WHERE ((ASCII(SUBSTRING(@pString,N,1)) - 48) & 0x7FFF) < 10
    ORDER BY t.N
    FOR XML PATH('')
    )
    ;

    Once those two functions are in place, here's a couple of snippets of code to demonstrate how to use it and what is returned.

    --===== Mock up the "phone_dataset" table for testing.
    -- We'll use a TempTable for the test.
    -- This is not a part of the solution. We're just making test data here.
    SELECT ID = t.N
    ,Telephone = CONVERT(CHAR(36),NEWID()) --Just a simulation.
    INTO #phone_dataset
    FROM dbo.fnTally(1,10000) t -- Just testing on 10K rows.
    ;
    --===== Use the function to return only the digits of the GUIDs
    -- using the DigitsOnly function.
    -- All you need to do is change the drop the "#" from the table
    -- name and see if it works for your telephone numbers.
    SELECT pds.ID
    ,pds.Telephone
    ,dig.DigitsOnly
    FROM #phone_dataset pds
    CROSS APPLY dbo.DigitsOnly(Telephone) dig
    ;

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Oscar_Boots

    SSC Veteran

    Points: 294

    Thanks Jeff,

    I'll have a read & give it a try.

    Thanks again

     

  • Jeff Moden

    SSC Guru

    Points: 994238

    Oscar_Boots wrote:

    Thanks Jeff,

    I'll have a read & give it a try.

    Thanks again

    I hit submit by accident.  I left a message at the top of the previous post.  Please have a look.  I'm still working on it.

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Jeff Moden

    SSC Guru

    Points: 994238

    Ok... my previous post with the code and some example code on how to use it are done.  Read the comments in the demo code near the end.

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply