Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Pass a string into SP and parse on space Expand / Collapse
Author
Message
Posted Thursday, May 9, 2013 3:48 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, January 7, 2014 11:14 AM
Points: 19, Visits: 22
I'm wanting to send a string into a stored procedure, but only use the first characters up to the first space.

For example, if I pass in:
"ABC DEF GHI"

I'm only interested in "ABC".

How would I go about handling that in the SP? Currently, I'm setting up the parameter to be VARCHAR(MAX). However, when I pass in a string like above, I get an error:
Incorrect syntax near 'DEF'

Any ideas?
Post #1451356
Posted Thursday, May 9, 2013 3:51 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:13 AM
Points: 20,807, Visits: 32,745
j2cagle (5/9/2013)
I'm wanting to send a string into a stored procedure, but only use the first characters up to the first space.

For example, if I pass in:
"ABC DEF GHI"

I'm only interested in "ABC".

How would I go about handling that in the SP? Currently, I'm setting up the parameter to be VARCHAR(MAX). However, when I pass in a string like above, I get an error:
Incorrect syntax near 'DEF'

Any ideas?


Are you using double quotes around the string or single quotes? If double quotes, that's the problem. If single quotes are being used, we don't have enough info to really answer your question.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1451358
Posted Thursday, May 9, 2013 3:54 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, January 7, 2014 11:14 AM
Points: 19, Visits: 22
Actually, it's being called from VB and a string variable being passed in is holding the value "ABC DEF GHI".
Post #1451360
Posted Thursday, May 9, 2013 3:59 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, January 7, 2014 11:14 AM
Points: 19, Visits: 22
But, whether it's being executed from VB or another SP, it shouldn't make any difference to how the current SP should be written...should it?
Post #1451363
Posted Thursday, May 9, 2013 4:03 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:13 AM
Points: 20,807, Visits: 32,745
j2cagle (5/9/2013)
But, whether it's being executed from VB or another SP, it shouldn't make any difference to how the current SP should be written...should it?


Try it with this: 'ABC DEF GHI'



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1451364
Posted Thursday, May 9, 2013 4:09 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, January 7, 2014 11:14 AM
Points: 19, Visits: 22
It's just a string variable in VB. There is no " or ' choice. It works fine when you just pass in "ABC", but when a space is introduced...that's when things crash and burn...
Post #1451366
Posted Thursday, May 9, 2013 4:14 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:13 AM
Points: 20,807, Visits: 32,745
j2cagle (5/9/2013)
It's just a string variable in VB. There is no " or ' choice. It works fine when you just pass in "ABC", but when a space is introduced...that's when things crash and burn...


Sorry, I'm not a VB programmer, I am a SQL Server DBA/Developer.

In SSMS you would execute the stored proc like this:

exec dbo.MyProc 'ABC DEF GH'



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1451367
Posted Thursday, May 9, 2013 4:23 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, January 7, 2014 11:14 AM
Points: 19, Visits: 22
In your example, what would happen if you used double quotes?
Post #1451369
Posted Thursday, May 9, 2013 4:24 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 7:16 PM
Points: 298, Visits: 715
Hi,

If you want to split the value inside SQL you can use this:

declare @input varchar(max)
declare @delimiter varchar(1)
declare @index int
declare @corrected_input varchar(20)

set @input = 'ABC DEF GHI'
set @delimiter = ' '
set @index = charindex(@Delimiter, @input)

if @index != 0
set @corrected_input = left(@input, @index - 1)
else
set @corrected_input = @input

select @corrected_input



But it looks like there is something funny going on with your VB code. Probably the parameter is being passed wrong.

You could always split it in VB:

Dim inputString As String = "ABC DEF GHI"

' Returns an array containing "ABC", "DEF", and "GHI".
Dim inputArray() As String = Split(inputString)

then use the first value in the array. Does that help?


Regards,
Bevan Keighley
Post #1451370
Posted Thursday, May 9, 2013 4:54 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, January 7, 2014 11:14 AM
Points: 19, Visits: 22
Yes that helps...but I'm trying to resolve this without touching the VB code.
Post #1451376
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse