I am creating a stored procedure which is passed a comma delimited string of
ids as a varchar datatype. The param is to be used in an SQL statement such
as:
CREATE PROCEDURE GetFromTable
@.IDs varchar(255)
AS
SELECT * FROM table WHERE iId NOT IN(@.IDs)
GO
The problem is that the iId field is of datatype int, so i get an error
converting the varchar datatype @.IDs to int.
I can not use dynamic SQL as i am not able to give table level access. It
has to be via EXEC rights on the stored procedure.
Any Help?
Thanks
PatrickArrays and Lists in SQL Server
http://www.sommarskog.se/arrays-in-sql.html
Faking arrays in T-SQL stored procedures
http://www.bizdatasolutions.com/tsql/sqlarrays.asp
AMB
"Patrick Russell" wrote:
> I am creating a stored procedure which is passed a comma delimited string
of
> ids as a varchar datatype. The param is to be used in an SQL statement suc
h
> as:
> CREATE PROCEDURE GetFromTable
> @.IDs varchar(255)
> AS
> SELECT * FROM table WHERE iId NOT IN(@.IDs)
> GO
> The problem is that the iId field is of datatype int, so i get an error
> converting the varchar datatype @.IDs to int.
> I can not use dynamic SQL as i am not able to give table level access. It
> has to be via EXEC rights on the stored procedure.
> Any Help?
> Thanks
> Patrick
>
>|||You cannot do this "this way". You'd need to parse your string,
load the values into a TABLE variable and then reference your
table variable:
SELECT * FROM table WHERE iId NOT IN (select myid from @.MyTableVariable)
These two articles will help:
http://www.eggheadcafe.com/articles/20001002.asp
http://www.eggheadcafe.com/PrintSea...asp?LINKID=529
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.learncsharp.net/home/listings.aspx
"Patrick Russell" <prussel@.cfl.rr.com> wrote in message
news:dB2Xd.105360$pc5.97052@.tornado.tampabay.rr.com...
>I am creating a stored procedure which is passed a comma delimited string
>of
> ids as a varchar datatype. The param is to be used in an SQL statement
> such
> as:
> CREATE PROCEDURE GetFromTable
> @.IDs varchar(255)
> AS
> SELECT * FROM table WHERE iId NOT IN(@.IDs)
> GO
> The problem is that the iId field is of datatype int, so i get an error
> converting the varchar datatype @.IDs to int.
> I can not use dynamic SQL as i am not able to give table level access. It
> has to be via EXEC rights on the stored procedure.
> Any Help?
> Thanks
> Patrick
>|||Patrick,
Parse the @.IDs into rows of a temp table or table variable then use a join
or a subselect. The in operator will not take a variable like this without
building dynamic SQL.
"Patrick Russell" <prussel@.cfl.rr.com> wrote in message
news:dB2Xd.105360$pc5.97052@.tornado.tampabay.rr.com...
>I am creating a stored procedure which is passed a comma delimited string
>of
> ids as a varchar datatype. The param is to be used in an SQL statement
> such
> as:
> CREATE PROCEDURE GetFromTable
> @.IDs varchar(255)
> AS
> SELECT * FROM table WHERE iId NOT IN(@.IDs)
> GO
> The problem is that the iId field is of datatype int, so i get an error
> converting the varchar datatype @.IDs to int.
> I can not use dynamic SQL as i am not able to give table level access. It
> has to be via EXEC rights on the stored procedure.
> Any Help?
> Thanks
> Patrick
>|||Hi Patrick.
You could write a function like...
-- pseudo code
create function udtSplitIDs( @.ids varchar(1000) )
returns @.IDsTable table
(
id int
)
as
begin
while (get position of comma)
begin
insert @.IDsTable values( @.strValue )
find next comma
end
return @.IDsTable
end
your select could then be:
SELECT * FROM table
WHERE iId NOT IN(SELECT * FROM udtSplitIDs(@.IDs))
Bryce|||Out of curiousity. A query like that should be avoided if possible in a
high-performance situation due to performance issues, I assume?
Showing posts with label comma. Show all posts
Showing posts with label comma. Show all posts
Thursday, March 29, 2012
Friday, February 10, 2012
convert comma separated values into rows withou using UDF
Hi,
I'm looking for a way to turn csv like 4,5,6,3 into rows without using
UDF, so it can be used in pure T-SQL only.
I want to use it in a code generated sql string, that will use these
values with an IN statement.
for example:
if column VAL contains 1,2,3
I would like to write something like:
SELECT * FROM x WHERE v IN (SELECT VAL ...) - this of course does not
work.
I also don't want to use CHARINDEX since this query should be as
efficient as possible.
I hope this can be done at all.
Thanks,
Eran.I think this is what you want:
DECLARE @.X NVARCHAR(4000)
SELECT @.X = 'SELECT columnList FROM x WHERE v IN (' + VAL + ')'
FROM tableName
EXEC(@.X)
But had the database been designed correctly, you wouldn't have to hope.
You could have simply used a join and avoided using dynamic SQL at all.
(1) You should never use SELECT * in production code.
(2) A column in a row (cell) should never have more than one value. In
other words, you should never store comma-separated values in a cell.
99.999% of the time, the presence of either of these is a sure sign of the
laziness, short-sightedness, and incompetence of the database designer or
developer.
"EranS" <eransevi@.yahoo.com> wrote in message
news:1124871159.442873.283030@.g14g2000cwa.googlegroups.com...
> Hi,
> I'm looking for a way to turn csv like 4,5,6,3 into rows without using
> UDF, so it can be used in pure T-SQL only.
> I want to use it in a code generated sql string, that will use these
> values with an IN statement.
> for example:
> if column VAL contains 1,2,3
> I would like to write something like:
> SELECT * FROM x WHERE v IN (SELECT VAL ...) - this of course does not
> work.
> I also don't want to use CHARINDEX since this query should be as
> efficient as possible.
> I hope this can be done at all.
> Thanks,
> Eran.
>|||thanks to brian heres a sample
use northwind
declare @.test nvarchar(4000)
declare @.values nvarchar (200)
set @.values='1,2,3,4'
set @.test='select * from employees where employeeid in ('+ (@.values)+')'
EXEC(@.test)
"Brian Selzer" wrote:
> I think this is what you want:
> DECLARE @.X NVARCHAR(4000)
> SELECT @.X = 'SELECT columnList FROM x WHERE v IN (' + VAL + ')'
> FROM tableName
> EXEC(@.X)
> But had the database been designed correctly, you wouldn't have to hope.
> You could have simply used a join and avoided using dynamic SQL at all.
> (1) You should never use SELECT * in production code.
> (2) A column in a row (cell) should never have more than one value. In
> other words, you should never store comma-separated values in a cell.
> 99.999% of the time, the presence of either of these is a sure sign of the
> laziness, short-sightedness, and incompetence of the database designer or
> developer.
>
> "EranS" <eransevi@.yahoo.com> wrote in message
> news:1124871159.442873.283030@.g14g2000cwa.googlegroups.com...
>
>|||Thanks Brian,
I can't run what you suggested since the values list should be
dynamically created when running the query, and it should both be in
one query.
This solution is only temporary until I'll normalize my tables better,
so I'm looking for a dirty way to do it. I guess I'll resort to using a
temporary table with the values in rows and then use it with the IN
clause. This won't be as fast as hoped but it should be better then
using a function.|||Take a look at this:
http://solidqualitylearning.com/Blo.../10/22/200.aspx
(by Dejan Sarka)
ML|||Eran, there are some issues with your request...
> I'm looking for a way to turn csv like 4,5,6,3 into rows without using
> UDF, so it can be used in pure T-SQL only.
A UDF is pure T-SQL.
> I want to use it in a code generated sql string, that will use these
> values with an IN statement.
> ...
> I also don't want to use CHARINDEX since this query should be as
> efficient as possible.
Using dynamic execution, you will end up with a different plan generated for
each unique string. Using the CHARINDEX method in a UDF that splits the arra
y
elements into multiple rows (or not encapsulated with a UDF), your code will
still be able to use an index on the table's join column, and reuse executio
n
plans.
That's really the way to go in this case.
My 2c (tested),
--
BG, SQL Server MVP
www.SolidQualityLearning.com
"EranS" wrote:
> Hi,
> I'm looking for a way to turn csv like 4,5,6,3 into rows without using
> UDF, so it can be used in pure T-SQL only.
> I want to use it in a code generated sql string, that will use these
> values with an IN statement.
> for example:
> if column VAL contains 1,2,3
> I would like to write something like:
> SELECT * FROM x WHERE v IN (SELECT VAL ...) - this of course does not
> work.
> I also don't want to use CHARINDEX since this query should be as
> efficient as possible.
> I hope this can be done at all.
> Thanks,
> Eran.
>|||Using a table function to split a string into rows can in most cases prove t
o
be much faster than using the IN operator, since the latter gets translated
into several OR clauses, which slow down the query processing.
On the other hand the table function returns a table of values that can be
joined to the other table(s) in your query, thus taking advantage of the muc
h
faster JOIN operation.
ML|||SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
IF OBJECT_ID ('dbo.fnBAS_utlSplitIDsIntoTable') IS NOT NULL --AND
OBJECTPROPERTY ( OBJECT_ID('dbo.fnBAS_utlSplitIDsIntoTable') ,
'IsinLineFunction' ) = 1
DROP FUNCTION dbo.fnBAS_utlSplitIDsIntoTable
GO
CREATE FUNCTION dbo.fnBAS_utlSplitIDsIntoTable (@.sIDList varchar(8000),
@.Delimeter char(1))
RETURNS TABLE AS
-- Version : $Id: fnBAS_utlSplitIDsIntoTable.sql,v 1.7 2005/05/09 20:11:33
vladimirm Exp $
/*
Usage:
declare @.IDlist varchar(500)
set @.idlist ='12,11,4,23455,13'
select * from dbo.fnBAS_utlSplitIDsIntoTable(@.IDList,',')
*/
RETURN
(
SELECT 1 - LEN(REPLACE(LEFT(@.sIDList, Number), @.Delimeter, SPACE(0))) +
Number AS [idx]
,SUBSTRING( @.sIDList, Number, CHARINDEX(@.Delimeter, @.sIDList +
@.Delimeter, Number) - Number) AS [intID]
FROM dbo.Numbers_8000
WHERE SUBSTRING(@.Delimeter + @.sIDList, Number, 1) = @.Delimeter
AND Number < LEN(@.sIDList) + 1
)
GO
"EranS" <eransevi@.yahoo.com> wrote in message
news:1124871159.442873.283030@.g14g2000cwa.googlegroups.com...
> Hi,
> I'm looking for a way to turn csv like 4,5,6,3 into rows without using
> UDF, so it can be used in pure T-SQL only.
> I want to use it in a code generated sql string, that will use these
> values with an IN statement.
> for example:
> if column VAL contains 1,2,3
> I would like to write something like:
> SELECT * FROM x WHERE v IN (SELECT VAL ...) - this of course does not
> work.
> I also don't want to use CHARINDEX since this query should be as
> efficient as possible.
> I hope this can be done at all.
> Thanks,
> Eran.
>
I'm looking for a way to turn csv like 4,5,6,3 into rows without using
UDF, so it can be used in pure T-SQL only.
I want to use it in a code generated sql string, that will use these
values with an IN statement.
for example:
if column VAL contains 1,2,3
I would like to write something like:
SELECT * FROM x WHERE v IN (SELECT VAL ...) - this of course does not
work.
I also don't want to use CHARINDEX since this query should be as
efficient as possible.
I hope this can be done at all.
Thanks,
Eran.I think this is what you want:
DECLARE @.X NVARCHAR(4000)
SELECT @.X = 'SELECT columnList FROM x WHERE v IN (' + VAL + ')'
FROM tableName
EXEC(@.X)
But had the database been designed correctly, you wouldn't have to hope.
You could have simply used a join and avoided using dynamic SQL at all.
(1) You should never use SELECT * in production code.
(2) A column in a row (cell) should never have more than one value. In
other words, you should never store comma-separated values in a cell.
99.999% of the time, the presence of either of these is a sure sign of the
laziness, short-sightedness, and incompetence of the database designer or
developer.
"EranS" <eransevi@.yahoo.com> wrote in message
news:1124871159.442873.283030@.g14g2000cwa.googlegroups.com...
> Hi,
> I'm looking for a way to turn csv like 4,5,6,3 into rows without using
> UDF, so it can be used in pure T-SQL only.
> I want to use it in a code generated sql string, that will use these
> values with an IN statement.
> for example:
> if column VAL contains 1,2,3
> I would like to write something like:
> SELECT * FROM x WHERE v IN (SELECT VAL ...) - this of course does not
> work.
> I also don't want to use CHARINDEX since this query should be as
> efficient as possible.
> I hope this can be done at all.
> Thanks,
> Eran.
>|||thanks to brian heres a sample
use northwind
declare @.test nvarchar(4000)
declare @.values nvarchar (200)
set @.values='1,2,3,4'
set @.test='select * from employees where employeeid in ('+ (@.values)+')'
EXEC(@.test)
"Brian Selzer" wrote:
> I think this is what you want:
> DECLARE @.X NVARCHAR(4000)
> SELECT @.X = 'SELECT columnList FROM x WHERE v IN (' + VAL + ')'
> FROM tableName
> EXEC(@.X)
> But had the database been designed correctly, you wouldn't have to hope.
> You could have simply used a join and avoided using dynamic SQL at all.
> (1) You should never use SELECT * in production code.
> (2) A column in a row (cell) should never have more than one value. In
> other words, you should never store comma-separated values in a cell.
> 99.999% of the time, the presence of either of these is a sure sign of the
> laziness, short-sightedness, and incompetence of the database designer or
> developer.
>
> "EranS" <eransevi@.yahoo.com> wrote in message
> news:1124871159.442873.283030@.g14g2000cwa.googlegroups.com...
>
>|||Thanks Brian,
I can't run what you suggested since the values list should be
dynamically created when running the query, and it should both be in
one query.
This solution is only temporary until I'll normalize my tables better,
so I'm looking for a dirty way to do it. I guess I'll resort to using a
temporary table with the values in rows and then use it with the IN
clause. This won't be as fast as hoped but it should be better then
using a function.|||Take a look at this:
http://solidqualitylearning.com/Blo.../10/22/200.aspx
(by Dejan Sarka)
ML|||Eran, there are some issues with your request...
> I'm looking for a way to turn csv like 4,5,6,3 into rows without using
> UDF, so it can be used in pure T-SQL only.
A UDF is pure T-SQL.
> I want to use it in a code generated sql string, that will use these
> values with an IN statement.
> ...
> I also don't want to use CHARINDEX since this query should be as
> efficient as possible.
Using dynamic execution, you will end up with a different plan generated for
each unique string. Using the CHARINDEX method in a UDF that splits the arra
y
elements into multiple rows (or not encapsulated with a UDF), your code will
still be able to use an index on the table's join column, and reuse executio
n
plans.
That's really the way to go in this case.
My 2c (tested),
--
BG, SQL Server MVP
www.SolidQualityLearning.com
"EranS" wrote:
> Hi,
> I'm looking for a way to turn csv like 4,5,6,3 into rows without using
> UDF, so it can be used in pure T-SQL only.
> I want to use it in a code generated sql string, that will use these
> values with an IN statement.
> for example:
> if column VAL contains 1,2,3
> I would like to write something like:
> SELECT * FROM x WHERE v IN (SELECT VAL ...) - this of course does not
> work.
> I also don't want to use CHARINDEX since this query should be as
> efficient as possible.
> I hope this can be done at all.
> Thanks,
> Eran.
>|||Using a table function to split a string into rows can in most cases prove t
o
be much faster than using the IN operator, since the latter gets translated
into several OR clauses, which slow down the query processing.
On the other hand the table function returns a table of values that can be
joined to the other table(s) in your query, thus taking advantage of the muc
h
faster JOIN operation.
ML|||SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
IF OBJECT_ID ('dbo.fnBAS_utlSplitIDsIntoTable') IS NOT NULL --AND
OBJECTPROPERTY ( OBJECT_ID('dbo.fnBAS_utlSplitIDsIntoTable') ,
'IsinLineFunction' ) = 1
DROP FUNCTION dbo.fnBAS_utlSplitIDsIntoTable
GO
CREATE FUNCTION dbo.fnBAS_utlSplitIDsIntoTable (@.sIDList varchar(8000),
@.Delimeter char(1))
RETURNS TABLE AS
-- Version : $Id: fnBAS_utlSplitIDsIntoTable.sql,v 1.7 2005/05/09 20:11:33
vladimirm Exp $
/*
Usage:
declare @.IDlist varchar(500)
set @.idlist ='12,11,4,23455,13'
select * from dbo.fnBAS_utlSplitIDsIntoTable(@.IDList,',')
*/
RETURN
(
SELECT 1 - LEN(REPLACE(LEFT(@.sIDList, Number), @.Delimeter, SPACE(0))) +
Number AS [idx]
,SUBSTRING( @.sIDList, Number, CHARINDEX(@.Delimeter, @.sIDList +
@.Delimeter, Number) - Number) AS [intID]
FROM dbo.Numbers_8000
WHERE SUBSTRING(@.Delimeter + @.sIDList, Number, 1) = @.Delimeter
AND Number < LEN(@.sIDList) + 1
)
GO
"EranS" <eransevi@.yahoo.com> wrote in message
news:1124871159.442873.283030@.g14g2000cwa.googlegroups.com...
> Hi,
> I'm looking for a way to turn csv like 4,5,6,3 into rows without using
> UDF, so it can be used in pure T-SQL only.
> I want to use it in a code generated sql string, that will use these
> values with an IN statement.
> for example:
> if column VAL contains 1,2,3
> I would like to write something like:
> SELECT * FROM x WHERE v IN (SELECT VAL ...) - this of course does not
> work.
> I also don't want to use CHARINDEX since this query should be as
> efficient as possible.
> I hope this can be done at all.
> Thanks,
> Eran.
>
Subscribe to:
Posts (Atom)