February 17, 2009 at 4:07 am
Hi, All
I have one variable @Temp..
Set @Temp = (Select Name From Group Where G_ Name = 'XYZ'
In this @Temp I get value "Pravin,Mit,Sid"
Now, I want detail information of above all user (Pravin,Mit and Sid) From User_Detail Table..
SELECT * FROM User_Detail WHERE NAME IN (@Temp)
But, above query is not working....
I Know i can do this in following way..
EXEC ('SELECT * FROM User_Detail WHERE NAME IN ('+@Temp+')')
But i don't/Can't use EXEC...
Any one have any other solution?
Thanks,
Pravin.
February 17, 2009 at 7:08 am
Hi,
Create a function which takes csv(coma saperated values) as input and returns all the values as rows of a table.
Function:
CREATE FUNCTION [dbo].[CsvToTable]
(
-- Add the parameters for the function here
@Csv VARCHAR(8000)
)
RETURNS
@Tbl TABLE
(
Name VARCHAR(100)
)
AS
BEGIN
-- Fill the table variable with the rows for your result set
DECLARE @pos int, @nextpos int, @valuelen int
SELECT @pos = 0, @nextpos = 1
WHILE @nextpos > 0
BEGIN
SELECT @nextpos = charindex(',', @Csv, @pos + 1)
SELECT @valuelen = CASE WHEN @nextpos > 0
THEN @nextpos
ELSE len(@Csv) + 1
END - @pos - 1
INSERT @Tbl (Name)
VALUES (substring(@Csv, @pos + 1, @valuelen))
SELECT @pos = @nextpos
END
RETURN
END
Now use the function in the select statement like
SELECT * FROM User_Detail WHERE NAME IN (Select * from CsvToTable(@Temp))
With Regards,
Gowtam
February 17, 2009 at 7:18 am
The solution proposed by Gowtam will work, but this article, http://www.sqlservercentral.com/articles/TSQL/62867/ offers another solution that tends to scale better.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 17, 2009 at 10:43 am
There are probably better ways...
[font="Courier New"]
DECLARE @temp VARCHAR(30)
SET @temp = 'Pravin,Mit,Sid'
DECLARE @rest VARCHAR(30)
DECLARE @table_var TABLE (name VARCHAR(30))
SET @rest=@temp
WHILE PATINDEX('%,%',@rest) <> 0
BEGIN
INSERT @table_var VALUES(LTRIM(RTRIM(SUBSTRING(@rest,1,PATINDEX('%,%',@rest)-1))))
SET @rest = SUBSTRING(@rest,PATINDEX('%,%',@rest)+1,LEN(@rest))
END
INSERT @table_var VALUES(LTRIM(RTRIM(@rest)))
SELECT * FROM @table_var
SELECT * FROM user_detail WHERE name IN (SELECT name FROM @table_var)
[/font]
Derek
February 17, 2009 at 10:57 am
There is...
[font="Courier New"]-- This should really be a permanent table because it's soooo useful
DECLARE @tally TABLE (n INT)
INSERT @tally SELECT row_number() OVER(ORDER BY id) FROM sysobjects
-- I've used a table variable only to show that it works!
-- USE A PERMANENT TALLY TABLE!
DECLARE @temp VARCHAR(30)
SET @temp = 'Pravin,Mit,Sid'
--SELECT * FROM user_detail
--WHERE name IN (
SELECT
SUBSTRING(','+mh.CsvColumn+',',N+1,CHARINDEX(',',','+mh.CsvColumn+',',N+1)-N-1)
FROM @Tally t
CROSS JOIN (SELECT @temp AS CsvColumn) mh
WHERE N < LEN(','+mh.CsvColumn+',')
AND SUBSTRING(','+mh.CsvColumn+',',N,1) = ','
--)
[/font]
It's pretty much a straight copy from Jeff's article!
Note to self: Must learn to use tally table.
Derek
February 18, 2009 at 5:54 am
Below is a function based on recursion for parsing a string with a specfied delimiter
Go
If Object_id('GmtGetTokens') is not null drop Function GmtGetTokens
GO
Create Function dbo.GmtGetTokens(
@AllElse Varchar(8000),
@Delimiter varchar(1)
)
Returns Table
AS
Return
(
with StartEnd(ID,i,j) as
(
select
ID=1,
i=1,
j=charindex(@Delimiter,@AllElse+@Delimiter)
union all
select
ID=ID+1,
i=j+1,
j=charindex(@Delimiter,@AllElse+@Delimiter,j+1)
from StartEnd
where charindex(@Delimiter,@AllElse+@Delimiter,j+1) <> 0
)
select
ID=ID,
Token=substring(@AllElse,i,j-i)
from StartEnd
)
February 18, 2009 at 8:24 am
Try this:
SELECT *
FROM User_Detail
WHERE ',' + @Temp + ',' LIKE '%,' + NAME + ',%'
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
February 20, 2009 at 9:08 pm
Edward Boyle (2/18/2009)
Below is a function based on recursion for parsing a string with a specfied delimiterGo
If Object_id('GmtGetTokens') is not null drop Function GmtGetTokens
GO
Create Function dbo.GmtGetTokens(
@AllElse Varchar(8000),
@Delimiter varchar(1)
)
Returns Table
AS
Return
(
with StartEnd(ID,i,j) as
(
select
ID=1,
i=1,
j=charindex(@Delimiter,@AllElse+@Delimiter)
union all
select
ID=ID+1,
i=j+1,
j=charindex(@Delimiter,@AllElse+@Delimiter,j+1)
from StartEnd
where charindex(@Delimiter,@AllElse+@Delimiter,j+1) <> 0
)
select
ID=ID,
Token=substring(@AllElse,i,j-i)
from StartEnd
)
Heh... ya forgot something, Ed....
[font="Courier New"]Msg 530, Level 16, State 1, Line 4
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.[/font]
--Jeff Moden
Change is inevitable... Change for the better is not.
February 20, 2009 at 9:42 pm
Recursion is sexy... and, recursion is slow. I recommend avoiding it whenever you can. The following code takes 93 CPU milliseconds to parse 1821 elements on my humble 1.8 GHZ single CPU box...
-===== Create an 1821 element parameter
DECLARE @AllElse VARCHAR(8000)
SELECT @AllElse = ISNULL(@AllElse+',','') + CAST(t.N AS VARCHAR(4))
FROM dbo.Tally t
WHERE t.N <= 1821
--===== Create the delimiter
DECLARE @Delimiter VARCHAR(1)
SET @Delimiter = ','
--===== Run the recursive code with timers on
SET STATISTICS TIME ON
;with StartEnd(ID,i,j) as
(
select
ID=1,
i=1,
j=charindex(@Delimiter,@AllElse+@Delimiter)
union all
select
ID=ID+1,
i=j+1,
j=charindex(@Delimiter,@AllElse+@Delimiter,j+1)
from StartEnd
where charindex(@Delimiter,@AllElse+@Delimiter,j+1) <> 0
)
select
ID=ID,
Token=substring(@AllElse,i,j-i)
from StartEnd
OPTION (MAXRECURSION 8000)
SET STATISTICS TIME OFF
... while the following code, which uses a Tally table, only takes 16 CPU milliseconds...
DECLARE @AllElse VARCHAR(8000)
SELECT @AllElse = ISNULL(@AllElse+',','') + CAST(t.N AS VARCHAR(4))
FROM dbo.Tally t
WHERE t.N <= 1821
SET STATISTICS TIME ON
SELECT ROW_NUMBER() OVER (ORDER BY t.N) AS ElementNumber,
SUBSTRING(','+@AllElse,N+1,CHARINDEX(',',@AllElse+',',N)-N) AS ElementValue
FROM dbo.Tally t
WHERE t.N <= LEN(','+@AllElse)
AND SUBSTRING(','+@AllElse,N,1) = ','
SET STATISTICS TIME OFF
If you don't have a Tally table, yet, or don't know how it works, please see the following article...
[font="Arial Black"]The "Numbers" or "Tally" Table: What it is and how it replaces a loop. (Click me)[/font]
[/url]
--Jeff Moden
Change is inevitable... Change for the better is not.
February 21, 2009 at 6:53 am
Jeff
Thanks for the response and the time measurements. Unfortunately, the MaxRecursion Option cannot be applied inside the function, it has to be applied in the Select statement using the function.
That said, I use both Tally Tables and recursion.
Eddie
February 21, 2009 at 3:28 pm
Edward Boyle (2/21/2009)
Unfortunately, the MaxRecursion Option cannot be applied inside the function, it has to be applied in the Select statement using the function.That said, I use both Tally Tables and recursion.
Eddie
Thanks Eddie... that was my point. You may want to mention that fact in a comment in the code.
Just as a comparison, I avoid recursion for the same reasons I avoid cursors and most While loops. If folks want to use recursion, that's ok by me... I just wanted them to see that there are some high performance alternatives.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply