October 6, 2011 at 6:52 pm
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.
October 6, 2011 at 9:32 pm
This should get it for you:
Declare @TableNameHeader as varchar(100)
set @TableNameHeader = 'Header1,Header2,Header3'
;
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
),
E2(N) AS (SELECT 1 FROM E1 a, E1 b),
cteTally(N) AS (SELECT TOP (ISNULL(DATALENGTH(@TableNameHeader),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E2
),
cteStart(N1) AS (
SELECT 1 UNION ALL
SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@TableNameHeader,t.N,1) = ','
),
cteLen(N1,L1) AS(
SELECT s.N1,
ISNULL(NULLIF(CHARINDEX(',',@TableNameHeader,s.N1),0)-s.N1,8000)
FROM cteStart s
)
Select t.* from nj_toys_claims.dbo.tblTables t
INNER JOIN (SELECT ItemRow = ROW_NUMBER() OVER(ORDER BY l.N1),
Item = SUBSTRING(@TableNameHeader, l.N1, l.L1)
FROM cteLen l) c
ON C.Item = T.TableName
You need to split your variable out.
I extracted the guts of DelimitedSplit8K - using the least amount necessary to make this work. You should go look for that article/script by Jeff Moden and download the entire script.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 7, 2011 at 7:35 am
I found another solution that looks like works for me as well:
Select T.* from nj_toys_claims.dbo.tblTables as T WHERE T.TableName
IN (Select value From fn_Split(@TableNameHeader, ','))
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fn_Split]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fn_Split]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE FUNCTION dbo.fn_Split(@text varchar(8000), @delimiter varchar(20) = ' ')
RETURNS @Strings TABLE
(
position int IDENTITY PRIMARY KEY,
value varchar(8000)
)
AS
BEGIN
DECLARE @index int
SET @index = -1
WHILE (LEN(@text) > 0)
BEGIN
SET @index = CHARINDEX(@delimiter , @text)
IF (@index = 0) AND (LEN(@text) > 0)
BEGIN
INSERT INTO @Strings VALUES (@text)
BREAK
END
IF (@index > 1)
BEGIN
INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))
SET @text = RIGHT(@text, (LEN(@text) - @index))
END
ELSE
SET @text = RIGHT(@text, (LEN(@text) - @index))
END
RETURN
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
October 7, 2011 at 7:48 am
You REALLY should look at Jeff Moden's article and method for this type of thing. It is set based and does not use looping at all which means it is extremely fast. You can find his article here. http://www.sqlservercentral.com/articles/Tally+Table/72993/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 7, 2011 at 7:53 am
I will look at it. I am working with a small number of items in my list, so I am not worried about performance but for future use with larger lists, I will look at a different solution as the one you suggested. Thanks.
October 7, 2011 at 7:59 am
The usage would be pretty much the same as the function you posted. It receives a delimited string and the delimiter and returns a table. The big difference is performance. Splitting with a set based approach is just going to outperform the looping structure no matter how you slice it. Probably not a big deal with your small dataset. However we all tend to keep reusing functions (which of course is kind of the point) and someday you will use that with a large dataset and suddenly the cpu on your sql box starts melting and running out of the front of the case. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 7, 2011 at 8:22 am
GrassHopper (10/7/2011)
I will look at it. I am working with a small number of items in my list, so I am not worried about performance but for future use with larger lists, I will look at a different solution as the one you suggested. Thanks.
Why not build the queries as if to tune for a large data set anyway?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 7, 2011 at 8:25 am
Sean Lange (10/7/2011)
The usage would be pretty much the same as the function you posted. It receives a delimited string and the delimiter and returns a table. The big difference is performance. Splitting with a set based approach is just going to outperform the looping structure no matter how you slice it. Probably not a big deal with your small dataset. However we all tend to keep reusing functions (which of course is kind of the point) and someday you will use that with a large dataset and suddenly the cpu on your sql box starts melting and running out of the front of the case. 😛
Agreed.
To further the point, in the discussion on Jeff's delimiter you can also find the code for a CLR delimiter that will outperform the split function that jeff created. Using the more efficient code will keep you from having to troubleshoot because something is less likely to hit the fan. 😎
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 7, 2011 at 8:31 am
Good point...I guess it was just my lazyness. I'll save myself a headache later and look at that solution now. Thanks!
October 7, 2011 at 8:33 am
Don't forget to have fun with it 😀
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 9, 2011 at 7:13 pm
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,
October 9, 2011 at 7:15 pm
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,
October 9, 2011 at 7:34 pm
Gosh... we just got you out of one looping problem. 😛 What does the store procedure you want to loop-over actually do?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply