Search table against variable list

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Search table against variable list

Author

Topic

abenitez77
Yak Posting Veteran

53 Posts

Posted - 2011-10-06 : 20:50:39

I want to run a select command on a table against a value in a variable

Declare @TableNameHeader as varchar(100)
set @TableNameHeader = 'Header1,Header2,Header3'

Select * from nj_toys_claims.dbo.tblTables where TableName IN(@TableNameHeader)

I get no records returned and the 3 values exist in the table.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-06 : 21:38:06

change to


WHERE ',' + @TableNameHeader + ',' LIKE '%,' + TableName + ',%'

KH
[spoiler]Time is always against us[/spoiler]

abenitez77
Yak Posting Veteran

53 Posts

Posted - 2011-10-06 : 22:11:32

I tried both below and both worked
WHERE @TableNameHeader LIKE '%' + TableName + '%'
and
WHERE ',' + @TableNameHeader + ',' LIKE '%,' + TableName + ',%'

Thanks!

abenitez77
Yak Posting Veteran

53 Posts

Posted - 2011-10-06 : 22:25:27

I actually am using a different solution. I realized my results would vary if in my table I had values like Header1,Header11,Header112, etc...

this is what I am going with:
DECLARE @sql VARCHAR(4000)
DECLARE @list VARCHAR(1000)

SET @list = '''Header1'',''Header2'',''Header3'''

SET @sql =
'SELECT * FROM nj_toys_claims.dbo.tblTables WHERE TableName IN ('
@list + ')'

EXECUTE (@sql)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-06 : 22:26:40

see the different in query below


declare	@Table table
(
	TableName	varchar(100)
)

insert into @Table  select 'Header1'
insert into @Table  select 'Header2'
insert into @Table  select 'Header12'

declare	@TableNameHeader varchar(100)

select	@TableNameHeader = 'Header1,Header2,Header123'

select	*
from	@Table
where	@TableNameHeader	like '%' + TableName + '%'

/* RESULT :
Header1
Header2
Header12
*/

select	*
from	@Table
where	',' + @TableNameHeader + ',' like '%,' + TableName + ',%'

/* RESULT :
Header1
Header2
*/

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-06 : 22:29:36

quote:
Originally posted by abenitez77

I actually am using a different solution. I realized my results would vary if in my table I had values like Header1,Header11,Header112, etc...

this is what I am going with:
DECLARE @sql VARCHAR(4000)
DECLARE @list VARCHAR(1000)

SET @list = '''Header1'',''Header2'',''Header3'''

SET @sql =
'SELECT * FROM nj_toys_claims.dbo.tblTables WHERE TableName IN ('
@list + ')'

EXECUTE (@sql)

that is another way. But it means you will be using Dynamic SQL.

another way is to use a split function like
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25830&SearchTerms=CSVTable
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033


select *
from   nj_toys_claims.dbo.tblTables
where  TableName in 
       (
           select stringval
           from   CSVTable(@TableNameHeader )
       )

KH
[spoiler]Time is always against us[/spoiler]

vmvadivel
Yak Posting Veteran

69 Posts

Posted - 2011-10-06 : 22:41:15

Make use of the SPLIT function which I have shown here - http://vadivel.blogspot.com/2011/10/how-to-split-delimited-string-values-in.html

Usage would be simple like this:

SELECT *
FROM HumanResources.Employee HR
JOIN dbo.[SplitUsingXML] ('5,7,2,3',',') i
ON HR.EmployeeID = i.parsedValue

Best Regards
Vadivel

http://vadivel.blogspot.com

abenitez77
Yak Posting Veteran

53 Posts

Posted - 2011-10-07 : 09:32:58

Thanks all for your help. I ended up going with this solution:

Select T.* from nj_toys_claims.dbo.tblTables as T WHERE T.TableName
IN (Select value From fn_Split(@TableNameHeader, ','))

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-07 : 10:13:58

also read this http://www.sommarskog.se/arrays-in-sql.html

KH
[spoiler]Time is always against us[/spoiler]

abenitez77
Yak Posting Veteran

53 Posts

Posted - 2011-10-09 : 21:16:15

So this is the code that I am using for my solution:

Insert Into [tblTables](TableName, TableIndex, SQLAuth)
Select T.Tablename, 2, 0 From tblTables as T WHERE T.TableName
IN (Select Item From DelimitedSplit8k(@TableNameHeader,',') )

When I insert the records (ie, Header1, Header2, Header3), It creates 3 records in tblTables and 3 new TableID values(ie 34,35,36).

I need to use those values within a loop and execute some code while it loops and grabs the new TableID 1 at a time and use that TableID for the code I am executing. I've never used loops with TSQL...how can I do all this?

Thanks,

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-09 : 21:29:17

how do you grabs the new TableID ?

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-10 : 01:30:16

quote:
Originally posted by abenitez77

So this is the code that I am using for my solution:

Insert Into [tblTables](TableName, TableIndex, SQLAuth)
Select T.Tablename, 2, 0 From tblTables as T WHERE T.TableName
IN (Select Item From DelimitedSplit8k(@TableNameHeader,',') )

When I insert the records (ie, Header1, Header2, Header3), It creates 3 records in tblTables and 3 new TableID values(ie 34,35,36).

I need to use those values within a loop and execute some code while it loops and grabs the new TableID 1 at a time and use that TableID for the code I am executing. I've never used loops with TSQL...how can I do all this?

Thanks,

why do you need a loop? why cant you use a set based solution using OUTPUT clause where you can insert and grab new generated values inline in insert itself?

http://msdn.microsoft.com/en-us/library/ms177564.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

abenitez77
Yak Posting Veteran

53 Posts

Posted - 2011-10-10 : 08:23:43

I need to retrieve the tableID 1 at a time because I will be executing some code individually according to the tablename I just saved.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-10 : 08:34:44

can you show us these code that you need to execute individually ?

KH
[spoiler]Time is always against us[/spoiler]

abenitez77
Yak Posting Veteran

53 Posts

Posted - 2011-10-10 : 09:33:19

It is an insert statement to 2 or 3 other tables. I need the TableID of the recently inserted values as a value to a column in the other table. I have not built the insert statement yet. But it will also use values from variables.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-10 : 09:47:52

please refer to the link that visakh posted and make use of the OUTPUT clause

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources