October 22, 2008 at 5:13 am
DECLARE @v1 AS NVARCHAR(30)
SET @v1 = '1,2,3'
PRINT ('select * from user_login_dtl where CAST(login_type_id AS NVARCHAR(10)) in (' + @v1 +')')
SELECT * FROM user_login_dtl WHERE CAST(login_type_id AS NVARCHAR(10)) in (@v1)
--------------------------------------------------------------------------
In above procedure result
if i execute print result then i got rows.
but directly using last select statement in my procedure i can't get result.
if i set @v-2 = 1 then i get my select query result from procedure.
I know that i can get result using following.
EXEC ('select * from user_login_dtl where CAST(login_type_id AS NVARCHAR(10)) in (' + @v1 +')').
but i need result direct using select in procedure.
October 22, 2008 at 6:11 am
Pravin Patel (10/22/2008)
DECLARE @v1 AS NVARCHAR(30)SET @v1 = '1,2,3'
PRINT ('select * from user_login_dtl where CAST(login_type_id AS NVARCHAR(10)) in (' + @v1 +')')
SELECT * FROM user_login_dtl WHERE CAST(login_type_id AS NVARCHAR(10)) in (@v1)
--------------------------------------------------------------------------
In above procedure result
if i execute print result then i got rows.
but directly using last select statement in my procedure i can't get result.
if i set @v-2 = 1 then i get my select query result from procedure.
I know that i can get result using following.
EXEC ('select * from user_login_dtl where CAST(login_type_id AS NVARCHAR(10)) in (' + @v1 +')').
but i need result direct using select in procedure.
you are trying to execute the dynamic query with the use of direct Select statement and that is not possible . for that you have to use EXEC, or provide the direct ID in the IN parameter. or use the select statement to select the ID in the IN parameter
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
October 23, 2008 at 7:28 am
Thanks dear
October 23, 2008 at 11:21 am
You can use a function to split the CSV parameter into a table, and then join to that table.
There are many different functions out there that will perform the split.
I recommend taking a look at this approach: http://www.sqlservercentral.com/articles/TSQL/62867/
October 23, 2008 at 1:49 pm
I've also seen this approach taken when looking for GUID's in a input list of GUIDs. This method looks up every value in the table in your list of strings. Instead of the standard other way around of looking your values up in the table. This will likely not be faster then spliting the string and lookup up the values on large tables because I belive it does not have the benifit of indexes.
SELECT
*
FROM
user_login_dtl
WHERE
Charindex(
CAST(login_type_id AS NVARCHAR(10)) --Look for your table value in the string
,@v1 --Search the string for the value in the table
) >=1
October 23, 2008 at 2:40 pm
upperbognor (10/23/2008)
I've also seen this approach taken when looking for GUID's in a input list of GUIDs. This method looks up every value in the table in your list of strings. Instead of the standard other way around of looking your values up in the table. This will likely not be faster then spliting the string and lookup up the values on large tables because I belive it does not have the benifit of indexes.
SELECT
*
FROM
user_login_dtl
WHERE
Charindex(
CAST(login_type_id AS NVARCHAR(10)) --Look for your table value in the string
,@v1 --Search the string for the value in the table
) >=1
I just compared this against the table method. It is a lot slower.
Also, I don't even think it is accurate.
Take a look at this:
SELECT TOP 2000 IDENTITY(INT,1,1) AS Num
INTO #Tally
FROM master.sys.columns
declare @sql varchar(max)
set @sql = '10,20,30,1000,200'
SELECT *
FROM #Tally
WHERE Charindex(CAST(Num AS NVARCHAR(10)) --Look for your table value in the string
,@sql --Search the string for the value in the table
) >=1
SELECT *
FROM #Tally A
JOIN dbo.ConvertToTable(@SQL) B ON A.Num = B.IntKey
DROP TABLE #Tally
You would expect both to return the same 5 rows; however the top one returns the following 8 rows:
1
2
3
10
20
30
100
200
October 23, 2008 at 2:53 pm
upperbognor (10/23/2008)
I've also seen this approach taken when looking for GUID's in a input list of GUIDs. This method looks up every value in the table in your list of strings. Instead of the standard other way around of looking your values up in the table. This will likely not be faster then spliting the string and lookup up the values on large tables because I belive it does not have the benifit of indexes.
SELECT
*
FROM
user_login_dtl
WHERE
Charindex(
CAST(login_type_id AS NVARCHAR(10)) --Look for your table value in the string
,@v1 --Search the string for the value in the table
) >=1
You need to find matches on the COMPLETE entry in the string.
SELECT
*
FROM
user_login_dtl
WHERE
Charindex(
N',' +CAST(login_type_id AS NVARCHAR(10)) + N',' --Look for your table value in the string
,N',' + @v1 + N',' --Search the string for the value in the table
) >=1
I apologize if the code is incorrect due to lack of testing; the principle IS correct. Use the value delimiter to preface the search item, and to bracket the list being searched.
October 23, 2008 at 3:04 pm
OK. That works. But it's still a lot slower. For four elements it was 16 ms. Joining to the table was 3 ms.
October 23, 2008 at 3:08 pm
It doesn't matter how quickly it can be done WRONG. 😉
October 23, 2008 at 3:36 pm
PhilPacha (10/23/2008)
It doesn't matter how quickly it can be done WRONG. 😉
Why is it wrong to convert it to a table?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply