Post reply

Query Help : pull Store Procedure name from Command

  • February 6, 2014 at 9:39 am

    #304090

    Hello

    I need help to develop logic, to pull Store Procedure name from command.

    see below is the table for that

    CREATE TABLE #X1

    (ID INT,COMMAND VARCHAR(100))

    INSERT INTO #X1 VALUES (1,'usp_LoginUpdate (5376,1,0)')

    INSERT INTO #X1 VALUES (2,'uspEmpl_DeleteCertifications(5320)')

    INSERT INTO #X1 VALUES (3,'uspEmpl_InsertCertification(1106,'','','','')')

    INSERT INTO #X1 VALUES (4,'exec CleanApplicationProgramRequirement 2013,1651,0')

    I develop logic as below

    SELECT ID,

    case

    when REPLACE(SUBSTRING(COMMAND, 1, (PATINDEX('%(%',COMMAND))),'(','') = '' then REPLACE(COMMAND,' ','')

    else REPLACE(SUBSTRING(COMMAND, 1, (PATINDEX('%(%',COMMAND))),'(','') end as [StoreProcedure]

    FROM #X1

    and get output as

    IDStoreProcedure

    1usp_LoginUpdate

    2uspEmpl_DeleteCertifications

    3uspEmpl_InsertCertification

    4execCleanApplicationProgramRequirement2013,1651,0

    but desired output is

    IDStoreProcedure

    1usp_LoginUpdate

    2uspEmpl_DeleteCertifications

    3uspEmpl_InsertCertification

    4exec CleanApplicationProgramRequirement

    Please help me to develop this logic

    Thanks

  • February 6, 2014 at 9:52 am

    #1686674

    Something like this?

    SELECT *,

    LEFT( REPLACE( REPLACE( COMMAND, 'exec ',''), 'execute ','') --Eliminate EXEC or EXECUTE

    , PATINDEX( '%[ (]%', REPLACE( REPLACE( COMMAND, 'exec ',''), 'execute ','')) - 1) --Find the start of parameters

    FROM #X1

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • February 6, 2014 at 10:22 am

    #1686685

    wah.... this is the first time i saw this new highlighting and "this worked for the OP";

    cute ehnacement!

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 6, 2014 at 10:24 am

    #1686686

    Lowell (2/6/2014)

    wah.... this is the first time i saw this new highlighting and "this worked for the OP";

    cute ehnacement!

    Wow - ditto. Never seen that before and it is definitely a good enhancement.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • February 6, 2014 at 10:32 am

    #1686689

    I'm pleasantly surprised, too. I wonder if this would stop people from improving working solutions.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • February 6, 2014 at 10:43 am

    #1686692

    Luis Cazares (2/6/2014)

    I'm pleasantly surprised, too. I wonder if this would stop people from improving working solutions.

    Possibly but I would wager that it won't.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 6 posts - 1 through 5 (of 5 total)

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