Declaring variables 1

[cafenerogal]

I'm not a complete newbie to SQL, but I've reached the limit of what I can do with the query I'm working on.

What I need to do is bring back the rows of data where one of the 12 fields I'm checking contain the codes I'm looking for.

Here's how I've declared my variable (this is cut down):

DECLARE @OPPROCS AS VARCHAR(500)
SET @OPPROCS = ''P273','Q554','Q181','Q188','Q189','H251','H258','H259',
'H281','H288''

The SELECT includes all the fields that may contain the above codes, with the relevant conditions. The problems is it's not returning any data at all. The rest goes something along the lines of (again this is a cut down version):

SELECT	OP.Primary_Procedure_Code,
	OP.Procedure_Code_2,
	Count(OP.HIS_Identity)

FROM	dbo.vw_OP_ALL_Commissioning_0708 as OP
	left 	outer join ReferenceTables..tbl_Refs_OPCS4_L4 as OPCS 
	on	OP.Primary_Procedure_Code = OPCS.Code collate database_default

WHERE
	OP.his_activitymonth between '200704' and '200803' and
	OP.HIS_PBC_Practice_PCT_Code like 'XYZ%' and
	OP.attendance_status in (5, 6) and
	OP.Administrative_Category = 1 and
	OP.Provider_Code like 'ABC%' and
	(OP.Primary_Procedure_Code in (@OPPROCS) or
	 OP.Procedure_Code_2 in (@OPPROCS))
GROUP BY
	OP.Primary_Procedure_Code
	OP.Procedure_Code_2,

Can someone do the honours and let me know where I'm going wrong?

Many thanks

 

[gmmastros]

You could try this....

DECLARE @OPPROCS AS VARCHAR(500)
SET @OPPROCS = '[!],[/!]P273,Q554,Q181,Q188,Q189,H251,H258,H259,H281,H288[!],[/!]'

WHERE
    OP.his_activitymonth between '200704' and '200803' and
    OP.HIS_PBC_Practice_PCT_Code like 'XYZ%' and
    OP.attendance_status in (5, 6) and
    OP.Administrative_Category = 1 and
    OP.Provider_Code like 'ABC%' and
    [!](@OPPROCS Like '%,' + OP.Primary_Procedure_Code + '%,' or
     @OPPROCS Like '%,' + OP.Procedure_Code_2 + '%,')[/!]

First, notice that I changed the variable so that there are commas on both sides of the string. This is important.

Using a like comparison in this way will not make use of index seeks (you'll get scans instead). However, if the data is suitably filtered already (based on other where clause conditions), this may actually work well for you.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[cafenerogal]

Makes sense and works really well!!

Ta v much

 

[gmmastros]

Slight mistake in the code.

    (@OPPROCS Like '%,' + OP.Primary_Procedure_Code + [!]',%'[/!] or
     @OPPROCS Like '%,' + OP.Procedure_Code_2 + [!]',%'[/!])

Notice I changes '%,' to ',%'

This may not ever affect you depending on your data, but it could, so I encourage you to correct it.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[cafenerogal]

Thanks George, I've amended the code to take this into account.