Solved

Convert WHERE to Dynamic SQL

Posted on 2008-10-01
20
166 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
 

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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
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.​
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

867 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now