Hi All
I have to work with a list of values passed through from an application that
I have no way of changing. I have to get the values into a procedure.
The values come through in the following format:
('value1','value2','value3',...)
I have no way of knowing how many values will come through.
I developed the following type pf proc:
SET QUOTED_IDENTIFIER off
GO
SET ANSI_NULLS ON
GO
ALTER procedure MyProc @.InParm varchar(8000)
as
begin
declare @.MyVar varchar(8000)
set @.MyVar = @.InParm
--..... this is an examle
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
And called it like this using " to enclose the value list:
Exec MyProc "('this is my','list','of choices','that is sent like','this
from the','application','which I have no way','of changing.','this is a very
long list that comes through')"
I get the following error:
Server: Msg 103, Level 15, State 7, Line 1
The identifier that starts with '('this is my','list','of choices','that is
sent like','this from the','application','which I have no way','of
changing.','this i' is too long. Maximum length is 128.
I found somewhere that I should set quoted identifier off, but this didn't
seem to help.
I have to get this list into my proc. Any Suggestions?
Thanks!You can replace all ' with '' (two single quotes) and then surround the
whole text with a single quote. For example:
=====
CREATE PROCEDURE takeLongList (@.theList VARCHAR(8000)) AS
BEGIN
PRINT @.theList
END
GO
EXEC takeLongList '''This is my'', ''Long List'', ''That I cannot change'',
''From what it was'''
GO
=====
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Chan" <Chan@.discussions.microsoft.com> wrote in message
news:61815D17-ED68-49D6-8489-BC397B466EC1@.microsoft.com...
> Hi All
> I have to work with a list of values passed through from an application
> that
> I have no way of changing. I have to get the values into a procedure.
> The values come through in the following format:
> ('value1','value2','value3',...)
> I have no way of knowing how many values will come through.
> I developed the following type pf proc:
> SET QUOTED_IDENTIFIER off
> GO
> SET ANSI_NULLS ON
> GO
> ALTER procedure MyProc @.InParm varchar(8000)
> as
> begin
> declare @.MyVar varchar(8000)
> set @.MyVar = @.InParm
> --..... this is an examle
> end
>
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> And called it like this using " to enclose the value list:
> Exec MyProc "('this is my','list','of choices','that is sent like','this
> from the','application','which I have no way','of changing.','this is a
> very
> long list that comes through')"
>
> I get the following error:
> Server: Msg 103, Level 15, State 7, Line 1
> The identifier that starts with '('this is my','list','of choices','that
> is
> sent like','this from the','application','which I have no way','of
> changing.','this i' is too long. Maximum length is 128.
> I found somewhere that I should set quoted identifier off, but this didn't
> seem to help.
> I have to get this list into my proc. Any Suggestions?
> Thanks!|||Hi
tried that tho I can't change how the app sends the list through which is:
('val1','val2','val3',...)
so I tried:
declare @.myVar varchar(8000)
select @.myvar = "('this is my','list','of choices','that is sent like','this
from the','application','which I have no way','of changing.','this is a very
long list that comes through')"
set @.myvar = replace(@.myvar, '''',''')
which still gives the same error.
Am I missing your point here?
Thanks
--
Chan
Programmer
"SriSamp" wrote:
> You can replace all ' with '' (two single quotes) and then surround the
> whole text with a single quote. For example:
> =====
> CREATE PROCEDURE takeLongList (@.theList VARCHAR(8000)) AS
> BEGIN
> PRINT @.theList
> END
> GO
> EXEC takeLongList '''This is my'', ''Long List'', ''That I cannot change''
,
> ''From what it was'''
> GO
> =====
> --
> HTH,
> SriSamp
> Email: srisamp@.gmail.com
> Blog: http://blogs.sqlxml.org/srinivassampath
> URL: http://www32.brinkster.com/srisamp
> "Chan" <Chan@.discussions.microsoft.com> wrote in message
> news:61815D17-ED68-49D6-8489-BC397B466EC1@.microsoft.com...
>
>|||> Am I missing your point here?
Yes. SQL Server expects ' to be a string delimiter. So, when you pass
'val1','val2'
What I would suggest doing, unless ' appears in the data itself, is
replacing all instances of ' with space(0) (empty space) and surrounding the
whole string with a single set of quotes.
> tried that tho I can't change how the app sends the list
Well, the app is doing it wrong, and it will need to change. How can you
not be in a position to change an app that could never have possibly worked?
A|||What I showed was an example and you can see that I do the '' when I
actually pass the parameter to the SP. This means that your application will
have to change to send it this way.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Chan" <Chan@.discussions.microsoft.com> wrote in message
news:24688FF2-F179-436D-B0A0-0C35AE87D7FE@.microsoft.com...
> Hi
> tried that tho I can't change how the app sends the list through which is:
> ('val1','val2','val3',...)
> so I tried:
> declare @.myVar varchar(8000)
> select @.myvar = "('this is my','list','of choices','that is sent
> like','this
> from the','application','which I have no way','of changing.','this is a
> very
> long list that comes through')"
> set @.myvar = replace(@.myvar, '''',''')
> which still gives the same error.
> Am I missing your point here?
> Thanks
> --
> Chan
> Programmer
>
> "SriSamp" wrote:
>|||Hi
I really have no way of changing the app as it was something we purchased
froom another company and they have the parameters setuyp the way they need
to use them.
--
Chan
Programmer
"SriSamp" wrote:
> What I showed was an example and you can see that I do the '' when I
> actually pass the parameter to the SP. This means that your application wi
ll
> have to change to send it this way.
> --
> HTH,
> SriSamp
> Email: srisamp@.gmail.com
> Blog: http://blogs.sqlxml.org/srinivassampath
> URL: http://www32.brinkster.com/srisamp
> "Chan" <Chan@.discussions.microsoft.com> wrote in message
> news:24688FF2-F179-436D-B0A0-0C35AE87D7FE@.microsoft.com...
>
>|||Then go back to them, because they are wrong.
> I really have no way of changing the app as it was something we purchased
> froom another company and they have the parameters setuyp the way they
> need
> to use them.
