Prev: Question on OVER Clause Useage
Next: Need help with sequel statement.
From: Erland Sommarskog on 28 Jul 2010 18:16
SqlBeginner (SqlBeginner(a)discussions.microsoft.com) writes:
> 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.

Of course, we don't have any idea. This is a forum for SQL questions,
not some kind of Jeopardy or parapsychological forum. "Something like
this"? How shall we guess what you really have?

And if works fine as long as one of the strings has a single quote in it,
why do you then remove the single quote? Of course you will not find your
data if you munge it!

Please post a complete repro script that demonstrates your issue, so we have
some chance to understand what you are talking about.


--
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

From: Plamen Ratchev on 28 Jul 2010 22:59
I do not see a problem. Can you provide a sample input string to
demonstrate the problem?

I fixed the last couple lines of code to make the function compile:

insert into @result(ActualContent)
select ActualContent = ltrim(rtrim(@strValue) );

return;

Then tested:

DECLARE @s VARCHAR(MAX);

SET @s = 'ab''c,''def'',g';

SELECT ActualContent
FROM FnStringSplitter(@s, ',');

All seems to come as expected:

ActualContent
--------------
ab'c
'def'
g


If you want to remove the single quotes surrounding some values, then
you have to use two REPLACE function calls:

SELECT ActualContent
FROM FnStringSplitter(REPLACE(REPLACE(@s, ',''', ','), ''',', ','),
',');

ActualContent
--------------
ab'c
def
g

--
Plamen Ratchev
http://www.SQLStudio.com
From: SqlBeginner on 29 Jul 2010 00:14
Erland, the query is the same just that i didn't mention the exact column,
table names in it!

Regards
Pradeep

"Erland Sommarskog" wrote:

> SqlBeginner (SqlBeginner(a)discussions.microsoft.com) writes:
> > 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.
>
> Of course, we don't have any idea. This is a forum for SQL questions,
> not some kind of Jeopardy or parapsychological forum. "Something like
> this"? How shall we guess what you really have?
>
> And if works fine as long as one of the strings has a single quote in it,
> why do you then remove the single quote? Of course you will not find your
> data if you munge it!
>
> Please post a complete repro script that demonstrates your issue, so we have
> some chance to understand what you are talking about.
>
>
> --
> 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
>
> .
>
From: Tony Rogerson on 29 Jul 2010 01:46


"--CELKO--" <jcelko212(a)earthlink.net> wrote in message
news:6f1b30bd-c903-47ac-b8e0-6232bc5f5c36(a)f6g2000yqa.googlegroups.com...
>>> 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.

And its been berated by a number of professionals and also has a poor
rating.

Long parameter lists are a very very bad idea, unmaintainable compared to
other established solutions that you can get the code off the shelf for.

>
> Good SQL programmers do not like to write procedural code. We also
> know that SQL is compiled and not interpreted like BASIC.

Good SQL programmers follow best practices, they certainly don't pass 100's
of parameters into a stored procedure when the application already has the
data as a "set".

--ROGGIE--


From: Tony Rogerson on 29 Jul 2010 01:48
Thankfully Erland he posted the long parameter article on
(http://www.simple-talk.com/sql/learn-sql-server/values()-and-long-parameter-lists/)
its got a low rating already and has had many comments berating the idea -
pretty much what people have been saying on here for a number of years!

Tony.

"Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message
news:Xns9DC42433167DYazorman(a)127.0.0.1...
> --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
>
First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: Question on OVER Clause Useage
Next: Need help with sequel statement.