Solved

Convert WHERE to Dynamic SQL

Posted on 2008-10-01
20
164 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
Comment Utility
post your whole query.
0
 
LVL 39

Expert Comment

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

Author Comment

by:tbaseflug
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
can't do both...can't put multiple values in there and wildcards....
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

Author Comment

by:tbaseflug
Comment Utility
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
Comment Utility
then do this:

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

Expert Comment

by:chapmandew
Comment Utility
sorry...

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

Expert Comment

by:BrandonGalderisi
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Can you enforce a business rule of allowing like searching (ie. %993%) when only one is specified?
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL Update Query 23 78
How to disable/enable multiple sql jobs in efficient way 11 90
mySql Syntax 7 26
Problem with SqlConnection 5 109
Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
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…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

744 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

15 Experts available now in Live!

Get 1:1 Help Now