Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

UDF Function to search files in SQL Server Expand / Collapse
Author
Message
Posted Sunday, May 19, 2013 4:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, May 19, 2013 4:54 AM
Points: 1, Visits: 4
Here is my case, i have a PRODUCTS table

create table PRODUCTS
(
ID_PRODUCTS CHAR(10) primary key not null,
NAME CHAR(30),
PRICE INTEGER
)


Then I fill it with some data,...

insert into PRODUCTS values('B1','Samsung Galaxy Ace 2',250)
insert into PRODUCTS values('B2','Samsung Galaxy Tab 3',375)
insert into PRODUCTS values('B3','Samsung Galaxy Note 2',700)
insert into PRODUCTS values('B4','Apple iPod Touch',200)
insert into PRODUCTS values('B5','Apple Macbook Pro',1250)


Then i wanna create a stored function to search for data based a keyword on NAME column in PRODUCTS table. For example, when i execute that function with a "Samsung" keyword, it will be show a list which contains word "Samsung" inside it. I hope the list will be like this the apperance

========================================
ID_PRODUCTS | NAME | PRICE
========================================
B1 | Samsung Galaxy Ace 2 | 250
----------------------------------------
B2 | Samsung Galaxy Tab 3 | 375
----------------------------------------
B3 | Samsung Galaxy Note 2 | 700
========================================


Here is the code, but it show nothing when execute it (select*from dbo.products_fun)

create function product_fun
(
@name char(30)
)
returns TABLE
as
return
(
select * from products where name like '%@name%'
)


I think it show nothing because the query

select * from products where name like '%@name%'


It is not a search for keywords inside the variable @name, .. but the search for the keyword "@name",... that's why it show nothing when execute it. Anyone wanna help ???

Post #1454321
Posted Sunday, May 19, 2013 10:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:18 AM
Points: 5,316, Visits: 12,346
I couldn't find a way of doing this in an iTVF.

If you execute the following:

sp_executesql N'select * from products where name like @ProdName'
,N'@ProdName varchar(30)'
,@ProdName = 'Samsung%'

You'll get the idea of how to do this sort of thing, while protecting yourself a little from SQL Injection. It would work fine in a stored proc ...

--Edit: oh and by the way, using char(n) for a column of variable length is generally best avoided, as all 'n' bytes will always get used, so a varchar(n) might be better in your examples.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1454349
Posted Sunday, May 19, 2013 4:10 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 24, 2014 7:06 PM
Points: 4, Visits: 21
replace
'%@name%'

with

'%' + @name + '%'
Post #1454377
Posted Sunday, May 19, 2013 4:20 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:09 AM
Points: 1,816, Visits: 5,911
Got to say, just add a Manufacturer column and do away with the LIKE query...

MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1454378
    Posted Wednesday, May 22, 2013 6:01 PM
    SSCarpal Tunnel

    SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

    Group: General Forum Members
    Last Login: Monday, November 3, 2014 4:30 PM
    Points: 4,574, Visits: 8,366
    lgegerton (5/19/2013)
    replace
    '%@name%'

    with

    '%' + @name + '%'


    '%' + RTRIM(@name) + '%'
    since CHAR data type is used.
    Post #1455768
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse