SQLTeam.com | Weblogs | Forums

Help with parameter


#1

Hi Masters,

I'm missing something simple I think. I have this query:

declare @site varchar(10)
set @site = 'ADD'

SELECT
V.VEND_ID
, V.AP_CHK_VEND_ID
, V.VEND_NOTES
, V.VEND_NAME AS VEND_NAME
, V.VEND_LONG_NAME
, V.VEND_NAME_EXT
, PV.PLAT_VENDORKEY
, A.ADDR_DC
, 'L1: '+A.LN_1_ADR+' '
+CHAR(13) +CASE WHEN A.LN_2_ADR = '' THEN '' ELSE ('L2: '+A.LN_2_ADR) END
+CHAR(13) +CASE WHEN A.LN_3_ADR = '' THEN '' ELSE (' '+'L3: '+A.LN_3_ADR) END as ADDERESS
, A.CITY_NAME
, A.MAIL_STATE_DC
, A.POSTAL_CD
FROM WEBAPP_CP.DELTEK.V_VEND V
Left JOIN CP_Migration.dbo.L_CP_Plat_Vendorkey PV
ON V.VEND_ID = PV.CP_Vendorkey
LEFT JOIN WEBAPP_CP.DELTEK.VEND_ADDR A
ON a.vend_id = v.vend_id
WHERE VEND_NAME_EXT like '%' + '@SITE' + '%'

This returns nothing. However, If I remove the @SITE parameter at the bottom and add in ADD, it works. What am I missing?

Thanks


#2

Since you have @SITE in quotes. the where clause is:

WHERE VENC=D_NAME like '%@SITE%'


#3

Hmmm. I still get no results.


#4

Not sure you understood my reply. I showed the effect of your WHERE clause and why it is failing, NOT how that clause should be written. I'l write it like this:

WHERE VEND_NAME LIKE '%' + @SITE + '%'

...removing the quotes around the variable


#5

Indeed. I misread it. Thanks for your help as always.