Advertisement

06.30.2008 at 08:43AM PDT, ID: 23527263
[x]
Attachment Details

Dyanmic SQl - Stupid/Easy problem

Asked by tbaseflug in MS SQL Server, SQL Server 2005

Tags:

OK - I Have the below - which works fine:
-----------------------------------------------------------------------------
SELECT '<b>' + CAST(PRIME_CPT as varchar(30)) + '</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>' + CAST(SECOND_CPT as varchar(30)) + '</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,
GB_MOD
FROM tblCCI140 c
INNER JOIN [vwhcpcs] p on PRIME_CPT = p.hcpc_code INNER JOIN [vwhcpcs] s on SECOND_CPT = s.hcpc_code
WHERE c.PRIME_CPT = @var0
AND c.SECOND_CPT = @var1
OR c.PRIME_CPT = @var1 AND c.SECOND_CPT = @var0
-----------------------------------------------------------------------------
But when I convert it to Dynamic SQL - I get a type conversion error in the where clause - any suggestions on the obviously stupid/easy thing I am missing:Start Free Trial
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
DECLARE @SQL nvarchar(max)
 
SELECT @SQL = '
SELECT ''<b>'' + CAST(PRIME_CPT as varchar(30)) + ''</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>'' + CAST(SECOND_CPT as varchar(30)) + ''</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, 
GB_MOD
FROM tblCCI140 c
INNER JOIN [vwhcpcs] p on PRIME_CPT = p.hcpc_code INNER JOIN [vwhcpcs] s on SECOND_CPT = s.hcpc_code 
WHERE c.PRIME_CPT = '+ @var0 +'
AND c.SECOND_CPT = '+ @var1 +' 
OR c.PRIME_CPT = '+ @var1 +' AND c.SECOND_CPT = '+ @var0 +''
 
	EXEC sp_executesql @SQL
[+][-]06.30.2008 at 08:48AM PDT, ID: 21900249

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]06.30.2008 at 08:50AM PDT, ID: 21900261

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]06.30.2008 at 08:53AM PDT, ID: 21900286

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]06.30.2008 at 08:56AM PDT, ID: 21900311

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]06.30.2008 at 08:57AM PDT, ID: 21900323

View this solution now by starting your 7-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zones: MS SQL Server, SQL Server 2005
Tags: SQL Server
Sign Up Now!
Solution Provided By: tbaseflug
Participating Experts: 2
Solution Grade: A
 
 
 
Loading Advertisement...
20080716-EE-VQP-32 / EE_QW_2_20070628