Stored Procedures (or whatever) returning strings (im no fool) 2

[BB101]

i know its not possible, but i really need to return a string from a stored procedure (or something else)...

I am using PHP on an 2k server, all is good working fine...

I use SQL stored procedures to insert data into a database but recently encountered a minor problem that i couldn't execute more than one line of code at one time or PHP could not connect to the db for the rest of the page...

I worked out that stored procedures were the way to go and wrote several with little effort.

I am now trying to split up a string by a character and use the parts as fields in the database.

However, I cannot find a way to get the string the sp genereates back to the main sp...

here is the sp:

CREATE PROCEDURE [dbo].[split_str] (@str as varchar, @param as char(1), @part as int) AS
Declare @last int, @pos int, @found int
set @last = 1
set @pos = 0
set @found = 0
set @str = @str + @param

WHILE @pos <= LEN(@str)
BEGIN
set @pos = @pos + 1
if (ascii(substring(@str,@pos,1)) = ascii(@param))
BEGIN
set @found = @found + 1
if (@found = @part)
print substring(@str,@last,@pos-@last)
set @last = @pos + 1
END
END
if (@found = (@part - 1))
print substring(@str,@last,@pos-@last)
GO

I thought about trying prints, but to no avail... i also tried return, but it only does ints...

Please help as this is really really bugging me!

thx

 

[TomasDill]

What about output parameters for SP? Can PHP get results from OUTPUT parameters of the SP?

In addition, can PHP get results from SP in form of a table? If yse, then just &quot;SELECT convert(text, substring(@str,@last,@pos-@last))&quot; will return the string in a text (memo) field in a table.
Vlad Grynchyshyn, MVP
vgryn@softserve.lviv.ua

http://www.softservecom.com

The professional level of programmer could be determined by level of stupidity of his/her bugs

 

[BB101]

the whole thing must be handled by SQL... how would I get the value of the field from the select in a parent sp?

 

[TomasDill]

By inserting into the temporary table. INSERT command allows inserting the result data set from SP into the table.

In another SP you can get the value of the OUTPUT parameter of the SP by providing a variable for it. I would recommend you to read more about the SP defniition in the help files, as well as about the INSERT command. Good luck! Vlad Grynchyshyn, MVP
vgryn@softserve.lviv.ua

http://www.softservecom.com

The professional level of programmer could be determined by level of stupidity of his/her bugs

 

[BB101]

I thought of that but decided that would slow things down...

I have just read BOL, and from what i have learnt i have changed the script to:

CREATE PROCEDURE [dbo].[split_str] (@str as varchar, @param as char(1), @part as int, @op as varchar OUTPUT) AS
Declare @last int, @pos int, @found int
set @last = 1
set @pos = 0
set @found = 0
set @str = @str + @param

WHILE @pos <= LEN(@str)
BEGIN
set @pos = @pos + 1
if (ascii(substring(@str,@pos,1)) = ascii(@param))
BEGIN
set @found = @found + 1
if (@found = @part)
BEGIN
select @op = substring(@str,@last,@pos-@last)
END
set @last = @pos + 1
END
END
if (@found = (@part - 1))
select @op = substring(@str,@last,@pos-@last)
GO

However, running this:

declare @tmp as varchar
exec split_str @str=&quot;this is a test string&quot;, @param=&quot; &quot;, @part=&quot;1&quot;, @op=@tmp output
print @tmp

returns &quot;t&quot;
changing part to any other value returns null, please help im so close to cracking this!

 

[tlbroadbent]

--Main SP
Declare @strout varchar(200)

Exec split_str 'the string', ' ', 1, @strout output

Print @strout

-- Split String SP
CREATE PROCEDURE dbo.split_str
(@str as varchar(200),
@param as char(1),
@part as int,
@outstr varchar(200) output) AS
.
.
.
-- Assign the value to the output parameter
Select @strout=substring(@str,@last,@pos-@last) Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[BB101]

who would have thought specifing the length the varchar would fix it!