Variable Inquiry

Transact-SQL
1

Hi SQL Masters,

I have a pretty simple query that calls a variable like so:

DECLARE @Site VARCHAR(10)
SET @Site = 'ADD'

	select v.VEND_ID
	, v.vend_notes
	, v.vend_name as VEND_NAME
	, v.vend_name_ext, l.Plat_Vendorkey
	, a.LN_1_ADR + ' '+a.LN_2_ADR as Address
	, 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 l
on v.VEND_ID = l.CP_Vendorkey
join WEBAPP_CP.DELTEK.V_VEND_ADDR a
on a.vend_id = v.vend_id

where VEND_NAME_EXT like @Site + '%'
order by Plat_Vendorkey

If I enter the variable 'ADD', it will pull all records that only start with 'ADD'. I would like it to pull in records with 'ADD' anywhere in the value. So for example, it will pull in records like ADD123, ADD456.

I would like it to grab records such as ABC123ADDXYZ and 456ADD. Hope this makes sense. Thanks for your assistance as always!

2 
where VEND_NAME_EXT like '%' + @Site + '%'
1 Like
3

You guys ROCK

4

I don't know if this is an important consideration for you, but just in case of interest:

Of the three statements:

where VEND_NAME_EXT = @Site
where VEND_NAME_EXT like @Site + '%'
where VEND_NAME_EXT like '%' + @Site + '%'

and IF there is an index on the VEND_NAME_EXT column, then SQL will almost certainly use the index in #1 (it won't if there are a huge number of duplicate rows with VEND_NAME_EXT = @Site), SQL will probably use the index in #2, and probably will NOT use it in #3.

So performance-wise there are some issues to consider. If the table is small I doubt you will notice much/any different, but if the table will grow, over time, then the query will slow down :frowning:

1 Like