October 4, 2011 at 11:40 am
Have a need to pass a comma delimited string as a parameter to a stored procedure. That string contains ints seperated by commas. Then I need to use the ints in an IN clause, like,
"WHERE ID IN(@paramString)"
How do i do that?
Thanks.
October 4, 2011 at 11:45 am
One way is to build a dynamic SQL query with it. Simple example:
set @values = '1,2,3,4,5'
set @query = 'select * from table where ID in ('+@values+')'
Just make sure whatever you do is injection proof.
The probability of survival is inversely proportional to the angle of arrival.
October 4, 2011 at 11:52 am
Probably a better way to do this is to use a splitter. Our very own Jeff Moden has a fantastic one here. http://www.sqlservercentral.com/articles/Tally+Table/72993/[/url]
The speed you will experience is amazing and it is already injection proof.
_______________________________________________________________
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 4, 2011 at 11:54 am
Look up the work of Jeff Moden and the SQL 8K Delimited split function, then you just join that to your table. It avoids the issue of SQL Injection.
here's a link:
October 4, 2011 at 12:43 pm
Thanks all for your response. I took STurner's response and created like:
SET @query = N'
SELECT * from tbl where (nid = '+@ID+')
AND ID IN('+IDs+')'
It works for the IDS in the IN clause but does not work of @ID, gives me error :
Conversion failed when converting the varchar value to data type int
October 4, 2011 at 12:51 pm
I would recommend NOT doing this in dynamic sql. The example you posted is not dynamic sql. Trust me you want to use the splitter function from Jeff.
Your final query will look something like this.
SELECT * from tbl
join DelimitedSplit8K(@ID, ',') d on tbl.ID = d.Item
Doesn't get much simpler than that!!! If you get stuck post some ddl and I can help you through it.
_______________________________________________________________
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 4, 2011 at 12:53 pm
Sean,
The problem is that I cannot create a function in our database, the lead shouts. I am allowed to create only couple of stored procs.
Any other suggestions?
October 4, 2011 at 12:57 pm
I understand the concerns about functions from a historical point of view but this is a table valued function and NOT a scalar which is why some shops don't let people create functions. If you simply can't convince them to look at the function (and the multitude of uses it will provide) than you will have to do this via dynamic sql. The example sturner showed should work.
_______________________________________________________________
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 4, 2011 at 12:57 pm
you can't use a comma separated set of values in a case of "where ID = @ID".
If @ID is not an integer then you'll need to use this: 'where (ID = ''' + @ID + ''')'
The probability of survival is inversely proportional to the angle of arrival.
October 4, 2011 at 1:01 pm
As I stated earlier, it works for IN clause, but does not work for an int variable
SET @query = N'
SELECT * from tbl where (nid = '+@ID+')
AND ID IN('+IDs+')'
It works for the IDS in the IN clause but does not work of @ID, gives me error :
Conversion failed when converting the varchar value to data type int
@ID is an int
October 4, 2011 at 1:13 pm
ramadesai108 (10/4/2011)
As I stated earlier, it works for IN clause, but does not work for an int variableSET @query = N'
SELECT * from tbl where (nid = '+@ID+')
AND ID IN('+IDs+')'
It works for the IDS in the IN clause but does not work of @ID, gives me error :
Conversion failed when converting the varchar value to data type int
@ID is an int
Keep in mind that we can't see you screen. You look like you have two things going on here...
You have a variable of some kind called @ID. What is IDs???
Give us a few details to work with. You are probably pretty close.
Given the error message you see it is most likely because you are trying to add character and string data.
Something like this?
declare @ID int = 1
select N'SELECT * from tbl where (nid = ' + cast(@ID as varchar(5)) + ')'
As suggested already you should look up sql injection and make sure you understand it, what you are putting together is potentially vulnerable.
_______________________________________________________________
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 4, 2011 at 1:18 pm
Jeff's function is by far the best (as many have already suggested) but if you can't create a scalar/table function you are going to be limited.
Not certain this will be helpful to you in the case you're in but it's worth a shot:
/* ############################################################################################################### */
--FORMAT DELIMITED STRING
/* ############################################################################################################### */
ALTER PROC [dbo].[utl_FormatArrayText] (
@String varchar(1500),
@Delimiter char(1),
@NumberQuotes int = 1
)
AS
/*
----------------------------------------------------------------------------------------------------------------
Purpose:Convert delimited text within a string into parenthesized values (quotes optional)
Department:DB&R
Created For:
----------------------------------------------------------------------------------------------------------------
NOTES:@Delimiter - Tells function the delimiter to parse the text with
@NumberQuotes - How many quotes you wisdh to have in the OUTPUT string
----------------------------------------------------------------------------------------------------------------
Created On:10/20/2005
Create By:MyDoggieJessie
----------------------------------------------------------------------------------------------------------------
Examples:
exec dbo.utl_FormatArrayText '1|2|3|4','|', 0
returns: (1,2,3,4)
exec dbo.utl_FormatArrayText 'IT|HR|ACCOUNTING|SALES','|', 1
returns: ('IT','HR','ACCOUNTING','SALES')
*/
DECLARE @Quote varchar(10)
SET @Quote = ''
/* ######################################### START MAIN TSQL HERE ########################################## */
IF @NumberQuotes >= 1
BEGIN
SET @Quote = SPACE(@NumberQuotes)
SET @Quote = REPLACE(@Quote, ' ', '''')
END
IF @Delimiter = ' '
BEGIN
/* Eliminate double spaces in text string */
WHILE CHARINDEX(' ', RTRIM(@String)) <> 0
BEGIN
SET @String = REPLACE(@String, ' ', ' ')
END
END
ELSE
BEGIN
/* Eliminate all spaces in text string */
WHILE CHARINDEX(' ', RTRIM(@String)) <> 0
BEGIN
SET @String = REPLACE(@String, ' ', '')
END
END
/* Convert supplied delimiter with open quotes, comma, and close quotes */
SET @String = REPLACE(@String, @Delimiter, @Quote + ',' + @Quote)
/* Add opening and closing quotes and parentheses */
SET @String = '(' + @Quote + @String + @Quote + ')'
/* Return the string */
SELECT @String
/* ########################################## END MAIN TSQL HERE ########################################### */
/*
exec dbo.utl_FormatArrayText '1|2|3|4','|', 0, ''
*/
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
October 4, 2011 at 1:54 pm
Sean,
Casting does work. Now the question is how do i execute? using
EXEC (@query)
or
EXEC sp_execsql (@query)
Thanks.
October 4, 2011 at 1:59 pm
Here is my SP:
CREATE PROCEDURE [dbo].[myList]
(
@Id int,
@StartYear int = NULL,
@EndYear int = NULL,
@Name VarChar(50) = NULL,
@Ids VarChar(3000) = NULL
)
AS
DECLARE @query VARCHAR(8000)
SET @query = N'
SELECT tbl1.OrganizationId, tbl2.EmployeeId, tbl1.Name AS EmployeeName,
tbl3.Name AS DepartmentName, tbl2.Year, AVG(tbl2.Month1) AS Month1, AVG(tbl2.Month2) AS Month2,
AVG(tbl2.Month3) AS Month3, AVG(tbl2.Month4) AS Month4, AVG(tbl2.Month5) AS Month5, AVG(tbl2.Month6)
AS Month6, AVG(tbl2.Month7) AS Month7, AVG(tbl2.Month8) AS Month8, AVG(tbl2.Month9) AS Month9,
AVG(tbl2.Month10) AS Month10, AVG(tbl2.Month11) AS Month11, AVG(tbl2.Month12) AS Month12,
ISNULL(AVG(tbl2.OverallScore), 0) AS OverallScore
FROM tbl2 INNER JOIN
tbl2 ON tbl2.tbl2Id = tbl2.tbl2Id INNER JOIN
tbl1 ON tbl2.EmployeeId = tbl1.Id INNER JOIN
tbl3 ON tbl1.DepartmentId = tbl3.Id INNER JOIN
tbl4 ON tbl1.OrganizationId = tbl4.OrganizationId
WHERE (tbl1.OrganizationId = '+ CAST(@OrganizationId as VARCHAR) +')
AND ((tbl2.Year >= '+ CAST(@StartYear as CHAR(4)) + ' AND tbl2.Year <= ' + CAST(@EndYear as CHAR(4))+ ') OR (@StartYear IS NULL AND @EndYear IS NULL))
AND (tbl4.Name = '+@PillarName+' or @PillarName IS NULL)
AND (tbl2.EmployeeId IN ('+@EmployeeId+') or @EmployeeId IS NULL)
GROUP BY tbl1.OrganizationId, tbl2.EmployeeId, tbl1.Name, tbl3.Name, tbl2.Year
order by tbl1.Name'
--select @query
EXEC (@query)
RETURN
GO
October 4, 2011 at 1:59 pm
ramadesai108 (10/4/2011)
Sean,Casting does work. Now the question is how do i execute? using
EXEC (@query)
or
EXEC sp_execsql (@query)
Thanks.
I could say "Yes". It doesn't really matter which way you execute it.
declare @query nvarchar(500) = 'select top 50 * from sys.sysobjects'
exec (@query)
EXEC sp_executesql @query
_______________________________________________________________
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/
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply