Solved

Convert WHERE to Dynamic SQL

Posted on 2008-10-01
20
176 Views
Last Modified: 2012-05-05
I know that this should be easy, but am hitting a wall on taking the below and "converting" it to dyanmic SQL - the SELECT I have no problem, but know that I must be messing upon the quotes some where - the below works great - just need help on getting it into a dyanmic SQL statement.
WHERE ',' + @strCriteria + ',' LIKE '%,' + c.[PRIME_CPT] + ',%' OR
 ',' + @strCriteria + ',' LIKE '%,' + c.SECOND_CPT + ',%'

Open in new window

0
Comment
Question by:tbaseflug
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 6
  • 5
20 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 22618863
post your whole query.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22618876
most likely you don't need to put it in dynamic SQL.
0
 

Author Comment

by:tbaseflug
ID: 22618894
DECLARE @strCriteria VARCHAR(30)


SET @strCriteria = '93304,00124'


SELECT  '<b>' + PRIME_CPT + '</b> - '
        + CASE WHEN p.CPT_DESC IS NULL
               THEN CASE WHEN p.LONG_DESC IS NULL THEN p.HCPC_DESC
                         ELSE p.LONG_DESC
                    END
               ELSE p.CPT_DESC
          END AS PRIME_CPT,
        '<b>' + SECOND_CPT + '</b> - '
        + CASE WHEN s.CPT_DESC IS NULL
               THEN CASE WHEN s.LONG_DESC IS NULL THEN s.HCPC_DESC
                         ELSE s.LONG_DESC
                    END
               ELSE s.CPT_DESC
          END AS SECOND_CPT,
        EDIT_TYPE,
        CASE WHEN GB_MOD = '1' THEN '1 - Code pair requires modifiers to bill'
             WHEN GB_MOD = '0' THEN '0 - Code Pair cannot be billed'
             ELSE GB_MOD
        END AS GB_MOD
FROM    data7.dbmasterdata.dbo.tblCCI143 c
        INNER JOIN data7.dbmasterdata.dbo.[vwhcpcs] p ON PRIME_CPT = p.hcpc_code
        INNER JOIN data7.dbmasterdata.dbo.[vwhcpcs] s ON SECOND_CPT = s.hcpc_code
WHERE   ',' + @strCriteria + ',' LIKE '%,' + c.[PRIME_CPT] + ',%'
        OR ',' + @strCriteria + ',' LIKE '%,' + c.SECOND_CPT + ',%'
ORDER BY c.PRIME_CPT,
        c.SECOND_CPT
0
Veeam gives away 10 full conference passes

Veeam is a VMworld 2017 US & Europe Platinum Sponsor. Enter the raffle to get the full conference pass. Pass includes the admission to all general and breakout sessions, VMware Hands-On Labs, Solutions Exchange, exclusive giveaways and the great VMworld Customer Appreciation Part

 

Author Comment

by:tbaseflug
ID: 22618904
Here is the dynamic SQL - I need it dyanmic as the table name is passed into the sproc
SELECT @SQL = ' SELECT ''<b>'' + PRIME_CPT + ''</b> - '' +  
	case when p.CPT_DESC is null then case when p.LONG_DESC is null then p.HCPC_DESC else p.LONG_DESC end else p.CPT_DESC end AS PRIME_CPT, 
	''<b>'' + SECOND_CPT + ''</b> - '' +
	case when s.CPT_DESC is null then case when s.LONG_DESC is null then s.HCPC_DESC else s.LONG_DESC end else s.CPT_DESC end AS SECOND_CPT, 
	EDIT_TYPE, 
CASE WHEN GB_MOD = ''1'' THEN ''1 - Code pair requires modifiers to bill''  WHEN 
GB_MOD = ''0'' THEN ''0 - Code Pair cannot be billed'' ELSE GB_MOD END AS GB_MOD
		
	FROM data7.dbmasterdata.dbo.tblCCI' + @var + ' c
	INNER JOIN data7.dbmasterdata.dbo.[vwhcpcs] p on PRIME_CPT = p.hcpc_code 
	INNER JOIN data7.dbmasterdata.dbo.[vwhcpcs] s on SECOND_CPT = s.hcpc_code 
 
WHERE '','' + @strCriteria + '','' LIKE ''%,'' + c.[PRIME_CPT] + '',%'' OR
 '','' + @strCriteria + '','' LIKE ''%,'' + c.SECOND_CPT + '',%''
 
 
	ORDER BY c.PRIME_CPT, c.SECOND_CPT'

Open in new window

0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22618927
Use some sort of function to split your comma delimited string into a table and then use in.
create view vw_Nums
as
with
       cte0 as (select 1 as c union all select 1), -- 2
       cte1 as (select 1 as c from cte0 a, cte0 b), -- 4
       cte2 as (select 1 as c from cte1 a, cte1 b), -- 16
       cte3 as (select 1 as c from cte2 a, cte2 b), -- 256
       cte4 as (select 1 as c from cte3 a, cte3 b), -- 65,536
       cte5 as (select 1 as c from cte4 a, cte4 b), -- 4,294,967,296 --four BILLION, not million
       nums as (select row_number() over (order by c) as n from cte5)
       select n from nums 
 
 
create function [dbo].[fn_DelimitedToTable](@DelimitedString nvarchar(max), @Delimiter nvarchar(32))
returns @Values TABLE
     (ident         int not null identity primary key clustered
     ,thePosition   int not null
     ,theValue      nvarchar(max)
     )
as
begin
 
insert into @Values (thePosition,theValue)
		select n, substring(@delimiter + @DelimitedString + @delimiter, n + (datalength(@delimiter)/2), charindex(@delimiter, @delimiter + @DelimitedString + @delimiter, n + len(@delimiter)) - n - len(@delimiter)) as string_value
		from	dbo.vw_Nums
		where
			n <= (datalength(@delimiter + @DelimitedString + @delimiter)/2) - (datalength(@delimiter)/2)
			and substring(@delimiter + @DelimitedString + @delimiter, n, (datalength(@delimiter)/2)) = @delimiter
 
 
 
return
end
/*
 
Requires:
 
 
GO
SELECT  '<b>' + PRIME_CPT + '</b> - '
        + CASE WHEN p.CPT_DESC IS NULL
               THEN CASE WHEN p.LONG_DESC IS NULL THEN p.HCPC_DESC
                         ELSE p.LONG_DESC
                    END
               ELSE p.CPT_DESC
          END AS PRIME_CPT,
        '<b>' + SECOND_CPT + '</b> - '
        + CASE WHEN s.CPT_DESC IS NULL
               THEN CASE WHEN s.LONG_DESC IS NULL THEN s.HCPC_DESC
                         ELSE s.LONG_DESC
                    END
               ELSE s.CPT_DESC
          END AS SECOND_CPT,
        EDIT_TYPE,
        CASE WHEN GB_MOD = '1' THEN '1 - Code pair requires modifiers to bill'
             WHEN GB_MOD = '0' THEN '0 - Code Pair cannot be billed'
             ELSE GB_MOD
        END AS GB_MOD
FROM    data7.dbmasterdata.dbo.tblCCI143 c
        INNER JOIN data7.dbmasterdata.dbo.[vwhcpcs] p ON PRIME_CPT = p.hcpc_code
        INNER JOIN data7.dbmasterdata.dbo.[vwhcpcs] s ON SECOND_CPT = s.hcpc_code
WHERE    c.[PRIME_CPT] in (select theValue from [dbo].[fn_DelimitedToTable](@strCriteria ,','))
        OR c.SECOND_CPT in (select theValue from [dbo].[fn_DelimitedToTable](@strCriteria ,','))
ORDER BY c.PRIME_CPT,
        c.SECOND_CPT

Open in new window

0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22618936
declare @x nvarchar(4000)

DECLARE @strCriteria VARCHAR(30)


SET @strCriteria = '93304,00124'


set @x = 'SELECT  ''<b>'' + PRIME_CPT + ''</b> - ''
        + CASE WHEN p.CPT_DESC IS NULL
               THEN CASE WHEN p.LONG_DESC IS NULL THEN p.HCPC_DESC
                         ELSE p.LONG_DESC
                    END
               ELSE p.CPT_DESC
          END AS PRIME_CPT,
        ''<b>'' + SECOND_CPT + ''</b> - ''
        + CASE WHEN s.CPT_DESC IS NULL
               THEN CASE WHEN s.LONG_DESC IS NULL THEN s.HCPC_DESC
                         ELSE s.LONG_DESC
                    END
               ELSE s.CPT_DESC
          END AS SECOND_CPT,
        EDIT_TYPE,
        CASE WHEN GB_MOD = ''1'' THEN ''1 - Code pair requires modifiers to bill''
             WHEN GB_MOD = ''0'' THEN ''0 - Code Pair cannot be billed''
             ELSE GB_MOD
        END AS GB_MOD
FROM    data7.dbmasterdata.dbo.tblCCI143 c
        INNER JOIN data7.dbmasterdata.dbo.[vwhcpcs] p ON PRIME_CPT = p.hcpc_code
        INNER JOIN data7.dbmasterdata.dbo.[vwhcpcs] s ON SECOND_CPT = s.hcpc_code
WHERE    c.[PRIME_CPT] IN(' + @strCriteria + ') OR c.SECOND_CPT IN(' + @strCriteria + ')ORDER BY c.PRIME_CPT,
        c.SECOND_CPT'

exec sp_executesql @x
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22618953
Ok... then:


SELECT @SQL = ' SELECT ''<b>'' + PRIME_CPT + ''</b> - '' +  
        case when p.CPT_DESC is null then case when p.LONG_DESC is null then p.HCPC_DESC else p.LONG_DESC end else p.CPT_DESC end AS PRIME_CPT, 
        ''<b>'' + SECOND_CPT + ''</b> - '' +
        case when s.CPT_DESC is null then case when s.LONG_DESC is null then s.HCPC_DESC else s.LONG_DESC end else s.CPT_DESC end AS SECOND_CPT, 
        EDIT_TYPE, 
CASE WHEN GB_MOD = ''1'' THEN ''1 - Code pair requires modifiers to bill''  WHEN 
GB_MOD = ''0'' THEN ''0 - Code Pair cannot be billed'' ELSE GB_MOD END AS GB_MOD
                
        FROM data7.dbmasterdata.dbo.tblCCI' + @var + ' c
        INNER JOIN data7.dbmasterdata.dbo.[vwhcpcs] p on PRIME_CPT = p.hcpc_code 
        INNER JOIN data7.dbmasterdata.dbo.[vwhcpcs] s on SECOND_CPT = s.hcpc_code 
 
WHERE c.[PRIME_CPT] in (' + @strCriteria + ') or c.SECOND_CPT  in (' + @strCriteria +')
 
 
        ORDER BY c.PRIME_CPT, c.SECOND_CPT'

Open in new window

0
 

Author Comment

by:tbaseflug
ID: 22618968
The problem with these is that I need a LIKE query - the user may put in a partial test based search - so they may enter 93304 or just 933, etc.
0
 

Author Comment

by:tbaseflug
ID: 22618985
I was looking at the following and saw that this query worked great - just needed it to be in dyanmic SQL, etc.

http://www.simple-talk.com/sql/t-sql-programming/faking-arrays-in-transact-sql/
    SELECT CustomerID, ContactName, CompanyName
      FROM Northwind.dbo.Customers
     WHERE ',' + @p + ',' LIKE '%,' + CustomerID + ',%' ;

Open in new window

0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22618984
can't do both...can't put multiple values in there and wildcards....
0
 

Author Comment

by:tbaseflug
ID: 22619011
I dont understand as the below works as I would expect - just need the where clause in dyanmic sql, etc.
DECLARE @strCriteria VARCHAR(30)
 
 
SET @strCriteria = '00100'
 
 
SELECT  '<b>' + PRIME_CPT + '</b> - '
        + CASE WHEN p.CPT_DESC IS NULL
               THEN CASE WHEN p.LONG_DESC IS NULL THEN p.HCPC_DESC
                         ELSE p.LONG_DESC
                    END
               ELSE p.CPT_DESC
          END AS PRIME_CPT,
        '<b>' + SECOND_CPT + '</b> - '
        + CASE WHEN s.CPT_DESC IS NULL
               THEN CASE WHEN s.LONG_DESC IS NULL THEN s.HCPC_DESC
                         ELSE s.LONG_DESC
                    END
               ELSE s.CPT_DESC
          END AS SECOND_CPT,
        EDIT_TYPE,
        CASE WHEN GB_MOD = '1' THEN '1 - Code pair requires modifiers to bill'
             WHEN GB_MOD = '0' THEN '0 - Code Pair cannot be billed'
             ELSE GB_MOD
        END AS GB_MOD
FROM    data7.dbmasterdata.dbo.tblCCI143 c
        INNER JOIN data7.dbmasterdata.dbo.[vwhcpcs] p ON PRIME_CPT = p.hcpc_code
        INNER JOIN data7.dbmasterdata.dbo.[vwhcpcs] s ON SECOND_CPT = s.hcpc_code
WHERE   ',' + @strCriteria + ',' LIKE '%,' + c.[PRIME_CPT] + ',%'
        OR ',' + @strCriteria + ',' LIKE '%,' + c.SECOND_CPT + ',%'
ORDER BY c.PRIME_CPT,
        c.SECOND_CPT

Open in new window

0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22619039
then do this:

WHERE c.[PRIME_CPT] like '%' + @strCriteria + '%'
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22619048
sorry...

WHERE c.[PRIME_CPT] like '%' + @strCriteria + '%'
OR c.[second_CPT] like '%' + @strCriteria + '%'
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22619068
But that won't work with multiple criteria in your @strCriteria

Try

SET @strCriteria = '933,00124'

and you will see that 93304 will not return.
0
 

Author Comment

by:tbaseflug
ID: 22619079
that would work if the param was '99299' but not '99299,00164' - The users can pass in a possible CSV value - which is why I was looking at the below as it meets that requirement

    SELECT CustomerID, ContactName, CompanyName
      FROM Northwind.dbo.Customers
     WHERE ',' + @p + ',' LIKE '%,' + CustomerID + ',%' ;

0
 

Author Comment

by:tbaseflug
ID: 22619094
ahh - now I see the flaw

"But that won't work with multiple criteria in your @strCriteria

Try

SET @strCriteria = '933,00124'

and you will see that 93304 will not return."
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22619092
But it doesn't as you say that sometimes they may type in 933 and expect 93304 to be returned.


',933,' is not like '%,93304,%'
0
 

Author Comment

by:tbaseflug
ID: 22619199
originally, I was parsing the values into array/table and then doing a join like the below - but a simple query was taking 40 seconds to complete - #tab2 is the temp table I was placing the array values into

 JOIN #tab2 t ON c.[PRIME_CPT] like ''%'' + t.FieldValue + ''%'' OR
      c.SECOND_CPT like ''%'' + t.FieldValue + ''%''
0
 

Author Comment

by:tbaseflug
ID: 22619993
The below works better - but still "slow" at around 14 seconds per query -

WHERE EXISTS (SELECT FieldValue
FROM #tab2 WHERE c.[PRIME_CPT] LIKE FieldValue + '%' OR
c.SECOND_CPT LIKE FieldValue + '%'
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 500 total points
ID: 22620330
Can you enforce a business rule of allowing like searching (ie. %993%) when only one is specified?
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

626 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question