Issue with Case statement

Question

Post reply

Issue with Case statement

mcfarlandparkway

SSCertifiable

Points: 7699
More actions
December 6, 2021 at 6:35 pm

#3960958

i have a table where i have mix and match records under statement column.
i am trying to filter based on case statement.. but unavailable to figure out how to keep declare logic when condition is met.
IINSERT INTO [dbo].[At]
[statement])
Select
CASE
WHEN Statement like '%from%' AND Statement like '%Where%' AND Statement not like '%Join%' THEN
DECLARE @keysValueToSearch NVARCHAR(4000) = 'from'
DECLARE @untilThisCharAppears NVARCHAR(4000) = 'where'
DECLARE @keysValueToSearchPattern NVARCHAR(4000) = '%' + @keysValueToSearch + '%'
SELECT SUBSTRING(Statement,PATINDEX(@keysValueToSearchPattern,Statement) + LEN(@keysValueToSearch),CHARINDEX(@untilThisCharAppears,Statement,
PATINDEX(@keysValueToSearchPattern, Statement) + LEN(@keysValueToSearch)
) -(PATINDEX(@keysValueToSearchPattern, Statement) + LEN(@keysValueToSearch))
) as SqlStatement
WHEN Statement like '%from%' AND Statement not like '%Where%' AND Statement not like '%Join%' THEN
DECLARE @Keyword varchar(200) = 'from'
SELECT SUBSTRING(Statement,charindex(@Keyword,Statement) + LEN(@Keyword), LEN(Statement)) as SqlStatement
WHEN Statement like '%from%' AND Statement like '%Join%'
DECLARE @keysValueToSearches NVARCHAR(4000) = 'from'
DECLARE @untilThisCharAppear NVARCHAR(4000) = 'Join'
DECLARE @keysValueToSearchPattrn NVARCHAR(4000) = '%' + @keysValueToSearches + '%'
SELECT SUBSTRING(Statement,PATINDEX(@keysValueToSearches,Statement) + LEN(@keysValueToSearches),CHARINDEX(@untilThisCharAppear,Statement,
PATINDEX(@keysValueToSearchPattrn, Statement) + LEN(@keysValueToSearches)
) -(PATINDEX(@keysValueToSearchPattrn, Statement) + LEN(@keysValueToSearches))
) as SqlStatement
END
FROM #temp1

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

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

Phil Parkin SSC Guru Points: 246985 More actions · Answer 1

Where is the DDL, sample data, desired results?

Merely providing a mass of unformatted code is not the way to get results, as you well know.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Jonathan AC Roberts SSCoach Points: 18361 More actions · Answer 2

You cannot include DECLARE inside a SQL statement.

Your SQL should look more like this:

DECLARE @keysValueToSearch NVARCHAR(4000) = 'from'
DECLARE @untilThisCharAppears NVARCHAR(4000) = 'where'
DECLARE @keysValueToSearchPattern NVARCHAR(4000) = '%' + @keysValueToSearch + '%'
DECLARE @Keyword varchar(200) = 'from'
DECLARE @keysValueToSearches NVARCHAR(4000) = 'from'
DECLARE @untilThisCharAppear NVARCHAR(4000) = 'Join'
DECLARE @keysValueToSearchPattrn NVARCHAR(4000) = '%' + @keysValueToSearches + '%'

INSERT INTO [dbo].[At]([statement])
SELECT CASE
           WHEN Statement like '%from%' AND Statement like '%Where%' AND Statement not like '%Join%' 
               THEN SUBSTRING(Statement, 
                              PATINDEX(@keysValueToSearchPattern,Statement) + LEN(@keysValueToSearch),
                              CHARINDEX(@untilThisCharAppears,Statement,PATINDEX(@keysValueToSearchPattern, Statement) + LEN(@keysValueToSearch)) - (PATINDEX(@keysValueToSearchPattern, Statement) + LEN(@keysValueToSearch)))
           WHEN Statement like '%from%' AND Statement not like '%Where%' AND Statement not like '%Join%' 
                THEN SUBSTRING(Statement,charindex(@Keyword,Statement) + LEN(@Keyword), LEN(Statement)) 
           WHEN Statement like '%from%' AND Statement like '%Join%'
                THEN SUBSTRING(Statement,PATINDEX(@keysValueToSearches,Statement) + LEN(@keysValueToSearches),CHARINDEX(@untilThisCharAppear,Statement,PATINDEX(@keysValueToSearchPattrn, Statement) + LEN(@keysValueToSearches)
                              ) - (PATINDEX(@keysValueToSearchPattrn, Statement) + LEN(@keysValueToSearches)))
           ELSE 'Invalid'
       END SqlStatement
  FROM #temp1

mcfarlandparkway SSCertifiable Points: 7699 More actions · Answer 3

mcfarlandparkway

SSCertifiable

Points: 7699

December 7, 2021 at 1:25 pm

#3961276

Thank you so much.

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 4

I have a table where I have mix and match records [rows are not records] under "statement" column.

I am trying to filter based on CASE statement [sic: CASE is an expression, not a statement] but unavailable to figure out how to keep declare logic when condition is met [sic: declarations are made in the DDL and never in the DML]

These are three fundamental mistakes that indicate you have no idea how SQL works. But even if you could get this to work creating SQL statements on the fly is an indication that you have no idea what you're doing at the design and programming level and have to fake it with a kludge at run time. Can you get some help from someone who knows what they're doing?

Please post DDL and follow ANSI/ISO standards when asking for help.