CODEWITHSUNDEEP
sqlt-sqlsql-server-2008stored-procedures
George2
George2

Reputation: 45821

How to pass parameter to stored procedure

I am using SQL Server 2008 Enterprise on Windows Server 2008 Enterprise. In the stored procedure, I need to pass parameter to a select-where-like statement. For example,

@department is store procedure input parameter, its type is varchar(20),

select * from sometable where somecolumn LIKE '%@department%'

but seems my statement above does not work, any ideas how to pass parameter @department to like statement?

thanks in advance, George

Upvotes: 1

Views: 2191

Answers (6)

Josh
Josh

Reputation: 44916

You could do something like this:

select * from sometable where somecolumn LIKE '%' + @department + '%'

Upvotes: 2

Talasila
Talasila

Reputation: 161

select * from sometable 
where CHARINDEX(@department,somecolumn)>0

You can also use the CHARINDEX function.

Upvotes: 0

Scott
Scott

Reputation: 1228

try:

select * from sometable where somecolumn LIKE '%' + @department + '%'

Upvotes: 2

Donnie
Donnie

Reputation: 46943

It's a variable, it goes outside the quotes.

select * from sometable where somecol like '%' + @department + '%'

Or, more preferably, add the %s to the variable and just use it directly

Upvotes: 2

Guffa
Guffa

Reputation: 700850

You concatenate the strings:

select * from sometable where somecolumn LIKE '%' + @department + '%'

Upvotes: 4

Martin Smith
Martin Smith

Reputation: 453920

select * /*But don't use * in production!*/
from sometable 
where somecolumn 
LIKE '%' + @department + '%'

Upvotes: 8

Related Questions