CODEWITHSUNDEEP
sqlsql-serversql-server-2008
user3233650
user3233650

Reputation: 419

Use LIKE for strings in sql server 2008

I want to make a function that search in a table and returns rows that contain a certain word that I Insert like below. But when I use LIKE it give me an error: Incorrect syntax near '@perberesi'

CREATE FUNCTION perberesit7
(@perberesi varchar(100))
RETURNS @menu_rest TABLE 
(emri_hotelit varchar(50), 
emri_menuse varchar(50), 
perberesit varchar(255))
AS
Begin
   insert into @menu_rest
   Select  dbo.RESTORANTET.Emri_Rest, dbo.MENU.Emri_Pjatës, dbo.MENU.Pershkrimi
   From RESTORANTET, MENU
   Where dbo.MENU.Rest_ID=dbo.RESTORANTET.ID_Rest and
          dbo.MENU.Pershkrimi LIKE %@perberesi%

   return

End

Pleae help me...How can I use LIKE in this case

Upvotes: 0

Views: 73

Answers (3)

CRAFTY DBA
CRAFTY DBA

Reputation: 14925

Why do you have to define the return table?

The following is a inline table variable function that performs better than a multi-line table. I wrote one to return columns that have the two letters 'id'. Just modify for your own case.

See article from Wayne Sheffield.

http://blog.waynesheffield.com/wayne/archive/2012/02/comparing-inline-and-multistatement-table-valued-functions/

-- Use tempdb
use tempdb;
go

-- Simple ITVF
create function search_columns (@name varchar(128))
returns TABLE
return
(
select * from sys.columns where name like '%' + @name + '%'
)
go

-- Call the function
select * from search_columns('id');
go

However, since you have a '%' in the like clause at the front of the expression, a full table or index scan is likely. You might want to look at full text indexing if you data is large.

http://craftydba.com/?p=1629

Upvotes: 1

Lamak
Lamak

Reputation: 70668

Ok, I just realized that you are creating a function, which means that you can't use INSERT. You should also really take Gordon's advice and use explicit joins and table aliases. 

CREATE FUNCTION perberesit7(@perberesi varchar(100))
RETURNS @menu_rest TABLE (  emri_hotelit varchar(50), 
                            emri_menuse varchar(50), 
                            perberesit varchar(255))
AS
Begin
   return(
   Select  R.Emri_Rest, M.Emri_Pjatës, M.Pershkrimi
   From RESTORANTET R
   INNER JOIN MENU M
        ON M.Rest_ID = R.ID_Rest
   Where M.Pershkrimi LIKE '%' + @perberesi + '%')
End

Upvotes: 1

jyrkim
jyrkim

Reputation: 2869

try using:
'%' + @perberesi + '%'

instead of:
%@perberesi%

Some Examples

Upvotes: 2

Related Questions