June 11, 2003 at 2:50 am
When using variables within stored procedures, how are you able to use an
integer variable inside the WHERE IN clause?
eg.
SELECT * FROM TableA WHERE ColumnB IN (@SomeVariable)
and the variable can be = 1 or = 1,2,3 etc
June 11, 2003 at 2:54 am
Hello demos,
quote:
SELECT * FROM TableA WHERE ColumnB IN (@SomeVariable)and the variable can be = 1 or = 1,2,3 etc
SELECT * FROM tablea WHERE ColumnB BETWEEN lower_bound and upper_bound
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
June 11, 2003 at 3:03 am
Hello demos,
sorry, hit the Enter button too early
declare @test-2 varchar(10)
declare @stmt varchar(100)
set @test-2 = '25,10789'
set @stmt = 'SELECT * FROM TableA WHERE ColumnB IN (' + @test-2 + ')'
EXEC(@stmt)
All you need to do is get together your IN String
FORGET it, DO NOT WORK PROPERLY
Edited by - a5xo3z1 on 06/11/2003 03:10:15 AM
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
June 11, 2003 at 3:20 am
demos
If the variable really is '1' or '1,2,3' then you will need to build up a sql statement in a string then use sp_executesql to execute the string.
declare @SQL
set @SQL='select * from tablea where columnb in (' + @SomeVariable + ')'
exec sp_executesql @SQL
With your real data you may need to deal with quotes as well.
Hope this helps.
June 11, 2003 at 3:24 am
Hello amelvin,
quote:
declare @SQLset @SQL='select * from tablea where columnb in (' + @SomeVariable + ')'
exec sp_executesql @SQL
that was also my intention, however from some quick testing, it only seems to work with an consecutive numbering
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
June 11, 2003 at 3:25 am
Hello Frank
Thanks for the reply.
I just realised that the variable can also be = 1,2,5. So upper and lower bound would not work.
I thought of building up a SQL string, but then I would loose my query plan. I was trying to avoid that.
At the moment I am looping through the SQL stored procedure from VB using a straight WHERE ColumnB = @SomeVariable clause.
June 11, 2003 at 4:44 am
Hello demos,
quote:
At the moment I am looping through the SQL stored procedure from VB using a straight WHERE ColumnB = @SomeVariable clause.
I don't know what happens to your query plan, but what about trying something like
...WHERE ColumnB = @SomeVariable OR ColumnB = @SomeOtherVariable ....?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
June 11, 2003 at 6:16 am
DECLARE @SomeVariable varchar(10)
SET @SomeVariable = '1,2,5'
SELECT * FROM TableA WHERE CHARINDEX(CAST(ColumnB as varchar),','+@SomeVariable+',') > 0
Far away is close at hand in the images of elsewhere.
Anon.
June 11, 2003 at 6:38 am
might be overkill - but i have a standard UF for these pesky CSV strings
CREATE FUNCTION UF_ParseIntoTable (@lv_PassedString varchar(8000))
RETURNS @Results TABLE
(
ItemId int
)
AS
BEGIN
DECLARE @li_StringPos smallInt,
@lv_ConvString varchar(10)
SET @lv_PassedString = ISNULL(RTRIM ( LTRIM (@lv_PassedString)),'')
IF @lv_PassedString = ''
BEGIN
GOTO DONE
END
SET @li_StringPos = PATINDEX('%,%',@lv_PassedString)
IF @li_StringPos = 0
BEGIN
INSERT INTO @Results
VALUES (CONVERT(INT,@lv_PassedString))
END
ELSE
BEGIN
WHILE @li_StringPos > 0
BEGIN
Set @lv_ConvString = LEFT(@lv_PassedString,(@li_StringPos - 1) )
Set @lv_PassedString = RIGHT(@lv_PassedString,LEN(@lv_PassedString) - @li_StringPos)
Set @li_StringPos = PATINDEX('%,%',@lv_PassedString)
INSERT INTO @Results
VALUES (CONVERT(INT,@lv_ConvString))
END
INSERT INTO @Results
VALUES (CONVERT(INT,@lv_PassedString))
END
DONE:
RETURN
END
This returns a table of integer values
Include this using IN or a derived table like this
SELECT *
FROM TableA
INNER JOIN
(
SELECT *
FROM dbo.UF_ParseIntoTable (@SomeVariable)
) AS T1 ON TableA.IdxFld = T1.ItemId
if the IdxFld is indexed and the index is selective enough - this would probably be the fastest option
June 11, 2003 at 11:41 am
I need to do this exact something, and have developed it using dynamic sql as mentioned. What I'm wondering is has anyone tried doing this with the in clause parm (sp input parameter) declared as a table type variable? I was just wondering if it was possible or not. My calling app is VB and I'm wondering if it could then just call the sp using an array as the input parameter? Books-On-Line is very limited on the table type variable and how to use it.
June 13, 2003 at 7:48 am
Thank you Mr Burrows! 🙂
CHARINDEX with a CAST worked like a charm.
June 13, 2003 at 4:22 pm
Yuo could think outside the box... create a temp table with the values for your IN clause, and then do a join.
For example, if you want:
where col1 IN (2,5,7)
create a one column table that contains three records with these values, and then say:
where col1 = #tmp.Col
Depending on your particular case, you can also code a "select distinct...into" to get ALL possible values if you really want to leave off the IN clause.
Of course, that may not always be practicable.
jef
June 16, 2003 at 1:21 am
GRN,
You do not need to select * from the function, you can simply use the function as if it's a table which is easier to read. Your example would be:
SELECT *
FROM TableA
INNER JOIN
dbo.UF_ParseIntoTable (@SomeVariable) AS T1
ON TableA.IdxFld = T1.ItemId
June 16, 2003 at 1:36 am
John -
i'm used to using derived tables in my selects - bad habit i've picked up 🙂 thanks for reminding me
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply