Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Convert WHERE to Dynamic SQL

Posted on 2008-10-01
20
Medium Priority
?
179 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
Congratulations! You’re Certified – Now What?

Starting a new career can be overwhelming. Becoming certified in your field of expertise is a great start, but where do you go from here?  Here are some tips to help you on your career journey.

 

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 2000 total points
ID: 22620330
Can you enforce a business rule of allowing like searching (ie. %993%) when only one is specified?
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

721 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