none
How to pass table values as parameters

    Question

  • I have table with with 500 records.

    The table contains city name.

    ex:

    CityId     CityName

    1             Houston

    2            New York

    3            Amsterdam

    like i have totally 500 records.

    I need to pass these 500 records to contains function.

    i need to passs first 10 (1-10) records for the query first time and i need to insert those result into Temporary table.

    Select * from candidates WHERE contains(c_resume_text,'"houston" or "New York" or "Amsterdam"')

    Again from 10-20,20-30,30-40,40-50....up t0 490-500 i need to pass like.I need to loop all those 500 records.

    How to write a logic.

    Please help me.

    Thanks.

    Tuesday, June 25, 2013 9:51 AM

Answers

All replies

  • INSERT INTO tbl (columns)

    SELECT <columns> FROM City WHERE cityid >=1 AND cityid<=10


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Tuesday, June 25, 2013 9:54 AM
    Answerer

  • I need to pass these 500 records to contains function.

    i need to passs first 10 (1-10) records for the query first time and i need to insert those result into Temporary table.

    Select * from candidates WHERE contains(c_resume_text,'"houston" or "New York" or "Amsterdam"')


    Hi ,

    R u trying to create Function??? and then your calling this function in any front end application.

    What is the goal behind this logic.

    Thanks.


    bala krishna

    Tuesday, June 25, 2013 10:09 AM
  • Try this:

          a. concatenate a search string in application
          b. construct the search string in sql

    then pass it to the following procedure:

    create procedure sp_your_procedure(@search_for nvarchar(1000))
    as
    select * from candidates
    where contains(ca.c_resume_text, @search_for)

    To construct the search string in sql use this (list of cities in "cities" table):

    declare @search nvarchar(1000)
    
    select top 10 
    	@search = isnull(@search + ' or ', '') + '"' + city_name + '"'
    from cities
    
    exec sp_your_procedure @search


    m@te

    • Proposed as answer by farkas.mate Tuesday, June 25, 2013 11:15 AM
    Tuesday, June 25, 2013 11:03 AM