Prev: Question on OVER Clause Useage
Next: Need help with sequel statement.
From: SqlBeginner on 28 Jul 2010 14:08
Hi,

I am passing comma separated values to a stored proc within which that value
is used something like this.

select col1, col2 from tablename
where col3 in (select * from FnStringSplitter(@strParam, ','))

it works fine as long as one of the string has a single quotes in it.

I tried like

select col1, col2 from tablename
where col3 in (select * from FnStringSplitter(Replace(@strParam,'''',''),
','))

still the data doesn't show up. Any idea what i need to change in the query.

Regards
Pradeep
From: Plamen Ratchev on 28 Jul 2010 14:39
The problem is in your function to split the strings. If you post the
code someone here can help.

Also, take a look at Erland's series of article on arrays, you will
find plenty of reliable methods to split an array:
http://www.sommarskog.se/arrays-in-sql.html

Also, when using the IN predicate you should be specifying a single
column in the SELECT subquery, like:

WHERE colA IN (SELECT colB FROM ...)

--
Plamen Ratchev
http://www.SQLStudio.com
From: --CELKO-- on 28 Jul 2010 16:14
>> I am passing comma separated values to a stored proc within which that value is used something like this.<<

Do not write code like this. I just did an article on the use of a
long parameter list at SQL Server Central.

Good SQL programmers do not like to write procedural code. We also
know that SQL is compiled and not interpreted like BASIC.
From: SqlBeginner on 28 Jul 2010 16:22
Thanks for the response Plamen.

This is the split function i use

CREATE FUNCTION FnStringSplitter
(
@strValue VARCHAR(MAX),
@separator char(1)
)
Returns @result table
(
ActualContent varchar(100)
)
as

begin

declare @Counter int
Set @Counter = 1

while (charindex(@separator, @strValue) >0)
begin
insert into @result (ActualContent)
select ActualContent =
ltrim(rtrim(substring(@strValue,1,charindex(@separator, @strValue)-1)))

set @strValue = substring(@strValue, charindex(@separator, @strValue) + 1,
len(@strValue))
set @Counter = @Counter +1
end

insert into @result(ActualContent)
select ActualContent = ltrim(rtim(@strValue)

end


Regards
pradeep

"Plamen Ratchev" wrote:

> The problem is in your function to split the strings. If you post the
> code someone here can help.
>
> Also, take a look at Erland's series of article on arrays, you will
> find plenty of reliable methods to split an array:
> http://www.sommarskog.se/arrays-in-sql.html
>
> Also, when using the IN predicate you should be specifying a single
> column in the SELECT subquery, like:
>
> WHERE colA IN (SELECT colB FROM ...)
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com
> .
>
From: Erland Sommarskog on 28 Jul 2010 18:13
--CELKO-- (jcelko212(a)earthlink.net) writes:
>>> I am passing comma separated values to a stored proc within which that
value is used something like this.<<
>
> Do not write code like this. I just did an article on the use of a
> long parameter list at SQL Server Central.

Which is among the worst solutions for the task. It's unmaintenable, and the
call overhead is horrible.

And most of all you know this.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 |  Next  |  Last
Pages: 1 2 3
Prev: Question on OVER Clause Useage
Next: Need help with sequel statement.