Solved

Convert WHERE to Dynamic SQL

Posted on 2008-10-01
20
169 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
  • 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

786 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