return a record every time sql

Question

i want read a record by table every time, number of input values may vary from 1 to n.
multi-valued fields in the input table should be apart and separate searches

        Input table with one row (multi-valued fields), Value  (nvarchar(500))
        ------
        Ann
        John,Tom
        Mark
        ...

user table: i want search any record of input table (The content of the row is unknown) in user table and return exists content

    user id  |   name
     12            Tom
     132           Ann
     32            Dina
     2             Mark   

Output#1:

    user id  |   name
     12            Tom
     132           Ann
     2             Mark 

**input and user Tables the number of rows Not fixed.
The content of the input table row is unknown, Is there anyone to help me?**

Answer 1

>>>>i want read a record by table every time

I am not sure that understand your question......

SELECT Value,ROW_NUMBER() OVER (ORDER BY Value) rn 

FROM tb

Or perhaps  you need  using TOP clause

DECLARE @read_record INT =2

SELECT TOP(@read_record) value FROM tbl ORDER BY Value

---

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

Answer 2

Hi Qrosh,

First you have to create a table type for inserting your input table

@MyTable dbo.tvp_MyTable READONLY

select userid, name from user

where name in (select name from @MyTable)

Answer 3

Here you go

create table #input (val nvarchar(20))

insert into #input values ('Ann')
insert into #input values ('John,Tom')
insert into #input values ('Mark')

create table #user (id int,val nvarchar(20))
insert into #user values (12,'Tom')
insert into #user values (132,'Ann')
insert into #user values (32,'Dina')
insert into #user values (2,'Mark')



SELECT IDENTITY(INT) "n" INTO Numbers
  FROM sysobjects s1
 CROSS JOIN sysobjects s2
 GO

WITH cte
AS
(
SELECT SUBSTRING(val, n, CHARINDEX(',', val + ',', n) - n) AS Val
from #input,numbers where substring(','+val,n,1)=','
AND n < LEN(val) + 1
) SELECT * FROM cte JOIN #user ON cte.val=#user.val

---

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