[Resolved] Query by wild card - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

[Resolved] Query by wild card

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2009-07-23 : 12:50:09

I have a query vhere user can select a product code from a drop dow list like:

*All
V96
V97
V98

Here is the query that is called:

select     l.[load] as Bol#,
           convert(char(10), l.date, 101) as Date,
           p.petroexcode as PetroEXCode,
           l.branded as Product,
           substring(p.name,1,30) as Name,
           cast(sum(l.gross) as decimal(15, 2)) AS GrossTotal,
           cast(sum(l.net) as decimal(15,2)) as NetTotal
from       [SFM-TP6000-1].TP6000.dbo.product as p
inner join [SFM-TP6000-1].TP6000.dbo.loadcomp as l on l.branded = p.product
where      (@Product IS null or p.petroexcode = @Product) and
           (@DateFrom IS Null or l.date >= @DateFrom) and
           (@DateTo IS Null or l.date <= DATEADD(DAY, 1, @DateTo))
group by   l.[load],
           convert(char(10), l.date, 101),
           p.petroexcode,
           l.branded,
           p.name

Now user wants to expand the query options and be able to search by wild card. If user keys in V*, I need to query all product codes that starts with "V" (I guess using "like"). How can this be done using the existing query? The product code is passed to my stored procedure as a parameter.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-23 : 13:07:12

declare @parm varchar(255)
set @parm='F*'
select * from
(select 'Fred' as test)dt
where test like replace(@parm,'*','%')

No, you're never too old to Yak'n'Roll if you're too young to die.