Pull the string inside the stored procedure definition

Question

Pull the string inside the stored procedure definition

vamsye

Old Hand

Points: 372
More actions
May 5, 2014 at 12:41 pm

#290969

In the below procedure definition, i need to find a way parse the definition and get the list of places where the where clause is being used.
SELECT DISTINCT
FC.CASE_ID,
UPPER(FC.CASE_NUMBER) AS CASE_NUMBER,
UPPER(REPLACE(FC.CASE_SHORT_TITLE,CHAR(13)+CHAR(10),'')) AS CASE_SHORT_TITLE,
FC.CASE_STATUS_DESCR,
FC.CASE_TYP_DESC, FC.CASE_SUB_TYP_DESC,
UPPER(AJ.DISPLAYNAME) AS JUDGE,
UPPER(PP.DISPLAYNAME) AS PARTY_DISPLAYNAME,
UPPER(AP.ATTORNEY_DISPLAYNAME)AS ATTORNEY_DISPLAYNAME ,
CASE WHEN RC_PARENT.PARENT_CASE_ID IS NOT NULL
THEN 'PARENT'
ELSE
CASE WHEN RC_CHILD.CHILD_CASE_ID IS NOT NULL
THEN 'CHILD'
ELSE NULL
END
END AS CONSOLIDATED,
PP.PARTY_STATUS_TYP_DESC,
LAST_EVENT_DT = CONVERT(VARCHAR(15),(SELECT TOP 1 FILING_DT FROM SW_ACM_V_EVENT_CASE WHERE (CASE_ID=FC.CASE_ID AND FILING_DT < GETDATE())ORDER BY FILING_DT DESC),101),
LAST_EVENT_ENTRY_DESC = UPPER((SELECT TOP 1 EVENT_ENTRY_DESC FROM SW_ACM_V_EVENT_CASE WHERE CASE_ID=FC.CASE_ID and void_event ='N' ORDER BY FILING_DT DESC )),
LAST_SCHEDULED_DT = CONVERT(VARCHAR(15),(SELECT TOP 1 ITEM_START_DT FROM ACC_SESSION_ITEM WHERE (CASE_ID=FC.CASE_ID AND ITEM_START_DT < GETDATE()) ORDER BY ITEM_START_DT DESC),101),
LAST_TICKLER_DT = CONVERT(VARCHAR(15), (SELECT TOP 1 TERMINATING_DATE FROM SW_ACM_V_TICKLERS_NOTSATISFIED WHERE (CASE_ID=FC.CASE_ID AND TERMINATING_DATE < GETDATE()) ORDER BY TERMINATING_DATE DESC),101)
FROM
AZLJ_ACM_V_FACTS_CASE AS FC
LEFT OUTER JOIN AZLJ_ACM_V_JUDGE AJ ON AJ.JUDGE_CD = FC.JUDGE_CD
LEFT OUTER JOIN ACM_RELATED_CASES RC_PARENT ON RC_PARENT.PARENT_CASE_ID=fc.CASE_ID AND RC_PARENT.CASE_RELATED_TYP_ID=(SELECT [VALUE] FROM CFG_ELEMENTS WHERE ELEMENT_NAME='REPORT_CONSOLIDATEDCASES_CASERELATEDTYPID')
LEFT OUTER JOIN ACM_RELATED_CASES RC_CHILD ON RC_CHILD.CHILD_CASE_ID=FC.CASE_ID AND RC_CHILD.CASE_RELATED_TYP_ID=(SELECT [VALUE] FROM CFG_ELEMENTS WHERE ELEMENT_NAME ='REPORT_CONSOLIDATEDCASES_CASERELATEDTYPID')
LEFT OUTER JOIN AZLJ_ACM_V_PERS_PARTY AS PP ON PP.CASE_ID = FC.CASE_ID AND PP.PARTY_ROLE_TYP_ID NOT IN (SELECT PARTY_ROLE_TYP_ID FROM ACM_PARTY_ROLE_TYP WHERE ROLE_FOR_ATT = 'Y')
LEFT OUTER JOIN SW_ACM_V_ATTORNEY_PARTY AS AP ON AP.PARTY_ID = PP.PARTY_ID AND AP.PARTY_ROLE_TYP_CD = 'Y'
LEFT OUTER JOIN SW_ACM_V_TICKLERS_NOTSATISFIED AS TNS ON TNS.CASE_ID = FC.CASE_ID
LEFT OUTER JOIN ACM_TICKLER_TYP_EVENT_TYP_MAP AS TTETM ON TTETM.TICKLER_TYP_ID =TNS.TICKLER_TYP_ID
WHERE
((TNS.SATISFACTION_EVENT_ENTRY_ID IS NULL) AND (TNS.TERMINATING_DATE < GETDATE()) AND (ISNULL(TTETM.NEXT_EVENT_ENTRY_TYP_ID,0) = 0))
AND (FC.NEXT_SESSION_DATE IS NULL OR FC.NEXT_SESSION_DATE < GETDATE() )
AND FC.CASE_ID NOT IN (SELECT CASE_ID FROM ACC_SESSION_ITEM WHERE ITEM_START_DT > GETDATE())
AND (FC.CASE_STATUS_TYP_ID NOT IN (SELECT DATA_VALUE FROM AICMS_REPORT_PARAMETERS WHERE VARIABLE_NAME='REPORT_ACTIONPEND_NOTINCASESTSID'))
AND ((PP.PARTY_STATUS_TYP_ID NOT IN (SELECT DATA_VALUE FROM AICMS_REPORT_PARAMETERS WHERE VARIABLE_NAME='REPORT_INACTIVE_PARTYSTSID'))
OR (PP.PARTY_STATUS_TYP_ID IS NULL))
AND(FC.CASE_ID IN (SELECT DISTINCT CASE_ID FROM SW_ACM_V_TICKLERS_NOTSATISFIED WHERE TERMINATING_DATE < GETDATE()
EXCEPT SELECT DISTINCT CASE_ID FROM SW_ACM_V_TICKLERS_NOTSATISFIED WHERE TERMINATING_DATE > GETDATE()))
In the above query there are more than one places and the where clause may not have the same string the where clause it can be with a space between the "=" and the value in single quotes.
Result set should be in the below format:
TABLE NAME Column Name VALUE
CFG_ELEMENTS ELEMENT_NAME REPORT_CONSOLIDATEDCASES_CASERELATEDTYPID

Viewing 15 posts - 1 through 14 (of 14 total)

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

gbritton1 SSCertifiable Points: 6522 More actions · Answer 1

Any whitespace (blanks, tabs, line ends, comments /* */) around an "=" sign in a where clause are ignored by SQL. You can have one, ten, one hundred etc., or none at all.

vamsye Old Hand Points: 372 More actions · Answer 2

I'm not sure what your saying here.

Please explain. Thanks,

gbritton1 SSCertifiable Points: 6522 More actions · Answer 3

I mean that the following WHERE clauses are equivalent:

1. No space

WHERE a=b

2. Some spaces

WHERE a = b

3. Some lines between

WHERE

a

=

b

vamsye Old Hand Points: 372 More actions · Answer 4

ohh ok, that is good if they are ignored by SQL.

But, how do i get the list of where clauses from the definition.

x SSC-Insane Points: 23660 More actions · Answer 5

http://stackoverflow.com/questions/6008839/bison-flex-sql-parser

http://stackoverflow.com/questions/8656926/sql-lex-yacc-grammar

edit:

this looks interesting too, although I couldn't discern with my short read if you could use it for your case:

http://social.msdn.microsoft.com/Forums/en-US/559470b8-0d01-415d-9117-d707e708db3d/parse-tree-for-sql-expressions?forum=vstsdb

vamsye Old Hand Points: 372 More actions · Answer 6

vamsye

Old Hand

Points: 372

May 5, 2014 at 2:35 pm

#1710873

I don't think this solves my question.

Sean Lange SSC Guru Points: 286573 More actions · Answer 7

vamsye (5/5/2014)
I don't think this solves my question.

What exactly is the question? It seems like you want a list of filtering predicates? The sql you posted has multiple select statements. Given the sql you posted what is the desired output?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286573 More actions · Answer 8

For anybody else wanting to view this query I formatted it.

SELECT DISTINCT FC.CASE_ID

,UPPER(FC.CASE_NUMBER) AS CASE_NUMBER

,UPPER(REPLACE(FC.CASE_SHORT_TITLE, CHAR(13) + CHAR(10), '')) AS CASE_SHORT_TITLE

,FC.CASE_STATUS_DESCR

,FC.CASE_TYP_DESC

,FC.CASE_SUB_TYP_DESC

,UPPER(AJ.DISPLAYNAME) AS JUDGE

,UPPER(PP.DISPLAYNAME) AS PARTY_DISPLAYNAME

,UPPER(AP.ATTORNEY_DISPLAYNAME) AS ATTORNEY_DISPLAYNAME

,CASE

WHEN RC_PARENT.PARENT_CASE_ID IS NOT NULL

THEN 'PARENT'

ELSE CASE

WHEN RC_CHILD.CHILD_CASE_ID IS NOT NULL

THEN 'CHILD'

ELSE NULL

END

END AS CONSOLIDATED

,PP.PARTY_STATUS_TYP_DESC

,LAST_EVENT_DT = CONVERT(VARCHAR(15), (

SELECT TOP 1 FILING_DT

FROM SW_ACM_V_EVENT_CASE

WHERE (

CASE_ID = FC.CASE_ID

AND FILING_DT < GETDATE()

)

ORDER BY FILING_DT DESC

), 101)

,LAST_EVENT_ENTRY_DESC = UPPER((

SELECT TOP 1 EVENT_ENTRY_DESC

FROM SW_ACM_V_EVENT_CASE

WHERE CASE_ID = FC.CASE_ID

AND void_event = 'N'

ORDER BY FILING_DT DESC

))

,LAST_SCHEDULED_DT = CONVERT(VARCHAR(15), (

SELECT TOP 1 ITEM_START_DT

FROM ACC_SESSION_ITEM

WHERE (

CASE_ID = FC.CASE_ID

AND ITEM_START_DT < GETDATE()

)

ORDER BY ITEM_START_DT DESC

), 101)

,LAST_TICKLER_DT = CONVERT(VARCHAR(15), (

SELECT TOP 1 TERMINATING_DATE

FROM SW_ACM_V_TICKLERS_NOTSATISFIED

WHERE (

CASE_ID = FC.CASE_ID

AND TERMINATING_DATE < GETDATE()

)

ORDER BY TERMINATING_DATE DESC

), 101)

FROM AZLJ_ACM_V_FACTS_CASE AS FC

LEFT JOIN AZLJ_ACM_V_JUDGE AJ ON AJ.JUDGE_CD = FC.JUDGE_CD

LEFT JOIN ACM_RELATED_CASES RC_PARENT ON RC_PARENT.PARENT_CASE_ID = fc.CASE_ID

AND RC_PARENT.CASE_RELATED_TYP_ID = (

SELECT [VALUE]

FROM CFG_ELEMENTS

WHERE ELEMENT_NAME = 'REPORT_CONSOLIDATEDCASES_CASERELATEDTYPID'

)

LEFT JOIN ACM_RELATED_CASES RC_CHILD ON RC_CHILD.CHILD_CASE_ID = FC.CASE_ID

AND RC_CHILD.CASE_RELATED_TYP_ID = (

SELECT [VALUE]

FROM CFG_ELEMENTS

WHERE ELEMENT_NAME = 'REPORT_CONSOLIDATEDCASES_CASERELATEDTYPID'

)

LEFT JOIN AZLJ_ACM_V_PERS_PARTY AS PP ON PP.CASE_ID = FC.CASE_ID

AND PP.PARTY_ROLE_TYP_ID NOT IN (

SELECT PARTY_ROLE_TYP_ID

FROM ACM_PARTY_ROLE_TYP

WHERE ROLE_FOR_ATT = 'Y'

)

LEFT JOIN SW_ACM_V_ATTORNEY_PARTY AS AP ON AP.PARTY_ID = PP.PARTY_ID

AND AP.PARTY_ROLE_TYP_CD = 'Y'

LEFT JOIN SW_ACM_V_TICKLERS_NOTSATISFIED AS TNS ON TNS.CASE_ID = FC.CASE_ID

LEFT JOIN ACM_TICKLER_TYP_EVENT_TYP_MAP AS TTETM ON TTETM.TICKLER_TYP_ID = TNS.TICKLER_TYP_ID

WHERE (

(TNS.SATISFACTION_EVENT_ENTRY_ID IS NULL)

AND (TNS.TERMINATING_DATE < GETDATE())

AND (ISNULL(TTETM.NEXT_EVENT_ENTRY_TYP_ID, 0) = 0)

)

AND (

FC.NEXT_SESSION_DATE IS NULL

OR FC.NEXT_SESSION_DATE < GETDATE()

)

AND FC.CASE_ID NOT IN (

SELECT CASE_ID

FROM ACC_SESSION_ITEM

WHERE ITEM_START_DT > GETDATE()

)

AND (

FC.CASE_STATUS_TYP_ID NOT IN (

SELECT DATA_VALUE

FROM AICMS_REPORT_PARAMETERS

WHERE VARIABLE_NAME = 'REPORT_ACTIONPEND_NOTINCASESTSID'

)

AND (

(

PP.PARTY_STATUS_TYP_ID NOT IN (

SELECT DATA_VALUE

FROM AICMS_REPORT_PARAMETERS

WHERE VARIABLE_NAME = 'REPORT_INACTIVE_PARTYSTSID'

)

OR (PP.PARTY_STATUS_TYP_ID IS NULL)

)

AND (

FC.CASE_ID IN (

SELECT DISTINCT CASE_ID

FROM SW_ACM_V_TICKLERS_NOTSATISFIED

WHERE TERMINATING_DATE < GETDATE()

EXCEPT

SELECT DISTINCT CASE_ID

FROM SW_ACM_V_TICKLERS_NOTSATISFIED

WHERE TERMINATING_DATE > GETDATE()

)

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

vamsye Old Hand Points: 372 More actions · Answer 9

TABLE NAME Column Name VALUE

CFG_ELEMENTS ELEMENT_NAME REPORT_CONSOLIDATEDCASES_CASERELATEDTYPID

Sean Lange SSC Guru Points: 286573 More actions · Answer 10

vamsye (5/6/2014)
TABLE NAME Column Name VALUE
CFG_ELEMENTS ELEMENT_NAME REPORT_CONSOLIDATEDCASES_CASERELATEDTYPID

???

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

vamsye Old Hand Points: 372 More actions · Answer 11

vamsye

Old Hand

Points: 372

May 6, 2014 at 9:30 am

#1711139

result set

Sean Lange SSC Guru Points: 286573 More actions · Answer 12

vamsye (5/6/2014)
result set

The problem is that we can't see your screen. We have no idea what you are trying to do.

In order to help we will need a few things:

1. Sample DDL in the form of CREATE TABLE statements

2. Sample data in the form of INSERT INTO statements

3. Expected results based on the sample data

Please take a few minutes and read the first article in my signature for best practices when posting questions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

vamsye Old Hand Points: 372 More actions · Answer 13

CREATE TABLE sqlText (ID int , line NVARCHAR(MAX),referenced_entity_name VARCHAR(1000) )

INSERT INTO [sqlText] ([Id],[line],[referenced_entity_name])VALUES(8712,'CREATE view [dbo].[AZLJ_RPT_V_STAT_CVLDISPOSITION] as