Dynamic IN
-
I need a dynamic IN for my search page. There is a "select section to search" with checkboxes as part of the search page which sends ID's of the sections to search. The ASP page sends it to the procedure like this: "('1','3','4','10')". I'm trying to use it like this in my SQL code but it doesn't seem to working:
select...
where K.K_Forum in @forumToSearch
This gives a syntax error for this line. Isn't it possible to use it like this or am I doing anything wrong?
-
To resolve the syntax error, try the following (parentheses around the parameter).
select...
where K.K_Forum in (@forumToSearch)
You can also check earlier posts about the same subject :
General - comma delimited string as a variable (04/19/2002)
General - passing in a variable (04/28/2002)
-
Take a look at the following article. It explains how to do it in Dynamic SQL:
http://qa.sqlservercentral.com/columnists/rmarda/dynamicvsstatic2.asp
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley -
NPeeters thanks it worked. Bad thing it led to another error
(Syntax error converting the varchar value '('1','2','3','4','5','19','6','7','8','9','10','11','12','13','14','15','16','17','18')' to a column of data type smallint.)bkelly thanks for the link. I knew the article but I'm trying to stay away from dynamic sql for this one. I was using dynamic sql but when I saw that the search was giving timeout error for some searches I decided to try to convert it to stati sql..
-
When you pass in the result as the example given by NPeeters, SQL Server is going to treat the whole result as a single string. It won't break it down and interpret it because if you think about it, there are times where the string itself (where we want a match on the string "'1', '3', '5'") is what we want to search on and there are times when we want the string broken out (where we want a match on the values 1, 3, or 5). Since SQL Server has no way of knowing which way we mean, it keeps a consistent behavior and goes with the first. The following code will return the same error you are seeing:
USE Northwind
GODECLARE @Search varchar(20)
SET @Search = '3, 7, 11'
SELECT
*
FROM Products
WHERE ProductID IN (@Search)The dynamic SQL will be treated as an ad hoc query and an execution plan will be generated for it. Of course, this means the stored procedure will also require a new execution plan every time. However, a timeout may not be the result of the dynamic SQL. There may be other factors at work.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley -
Is it correct you wish to pass a single string to the procedure and then use the individual numeric members?
The proc below breaks a comma-delimited string into integers. This is a simple one, I know it's a common question and you could find fancier versions in the Resources section of this web site.
create proc String_Buster @input_string varchar(1000)
as
set nocount on
-- Declarations
declare @arraytable table(each_number int)
declare @position tinyint
-- Remove quotes
select @input_string = replace(@input_string,'''','')
-- Loop once per comma
while @input_string like '%,%'
begin
-- Note position of first comma
select @position = charindex(',',@input_string)
-- If it's a number, insert @arraytable
if 1 = isnumeric(left(@input_string,(@position - 1)))
insert @arraytable
select left(@input_string,(@position - 1))
-- Remove this value from @input_string
select @input_string = right(@input_string, len(@input_string) - (@position))
end
-- See results
select each_number from @arraytable
-
Try converting your key field into a string and comparing it to your comma-delimited paramater, like this:
=============================================
@param = '20,13,95'
-- First add leading and trailing commas:
set @param = ',' + @param + ','
select * from myTable where @param like '%,' + ltrim(rtrim(str(myField))) + ',%'
=============================================
It may seem akward, but it works..
- Avi
-
Here is a stored procedure based solution that parses the commas into a tablevar and uses no dynamic sql. Some more details are at :
http://accesshelp.net/content/Report.asp?REPORT=4&PARAM_ID=46
CREATE PROCEDURE [dbo].[SelectEmployeesByID]
@IDList varchar(2000)
AS
set arithignore on
set arithabort off
set ansi_warnings off
set nocount on
declare @IDListPosition int
declare @ArrValue varchar(2000)
--must declare correct data type for IDs
declare @TableVar table ( EmployeeID varchar(50) NOT NULL )
set @IDList = COALESCE(@IDList ,'')
IF @IDList <> ''
BEGIN
--add comma to end of list so user doesn't have to
set @IDList = @IDList + ','
-- Loop through the comma demlimted string list
while patindex('%,%' , @IDList ) <> 0
begin
select @IDListPosition = patindex('%,%' , @IDList)
select @ArrValue = left(@IDList, @IDListPosition - 1)
-- Insert parsed ID into TableVar for WHERE IN select
Insert Into @TableVar (EmployeeID) Values(@ArrValue)
-- Remove processed string
select @IDList = stuff(@IDList, 1, @IDListPosition, '')
END
END
SELECT
*
FROM
[Employee]
WHERE
(
[EmployeeID] IN (select EmployeeID from @TableVar)
)
ORDER BY EmployeeID ASC;
-
If you are using SQL 7 the the previous will work but you cannot use a table variable. Instead you need to create a #temp table then parse each into a record in the temp table. Then call the main query with a select from the #temp table. Code is the same except add create table and drop table statements to handle temp table in process.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
(Syntax error converting the varchar value '('1','2','3','4','5','19','6','7','8','9','10','11','12','13','14','15','16','17','18')' to a column of data type smallint.)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply