asked on

Stored Procedure with 2 input and 1 output variable

I have a stored procedure that has 3 parameters.
2 of them are input parameters and the other is an output.
The strored procedure name is reconstruct, the parameters are @processed, @reconst, @reconstructed. @reconstructed is the output.
I would like to know the sql syntax or procedures in order to give @processed and @reconst values, execute the SP and retrieve the outputed value from @reconstructed.

Thanks in advanced.

bret

create procedure reconstruct @processed int, @reconst int, @reconstructed int
output
as
select @reconstructed = @processed + @reconst
go

declare @myvariable int

execute reconstruct 1, 2, @reconstructed = @myvariable output

select @myvariable
go

nplib

ASKER

All though I got the result I was looking for I get a bunch of weird stuff in front of it first.

I ran this query

declare @mytemp varchar(255)
execute reconstruct 'ROGERS NANCY R', 'I.I', @reconstructed = @mytemp output
select @mytemp
go

and got this result

Warnings: --->
W (1): 010P4: An output parameter was received and ignored.
W (2): 010P4: An output parameter was received and ignored.
<---

1 record(s) affected

1 record(s) affected

1 record(s) affected

1 record(s) affected

1 record(s) affected

1 record(s) affected

1 record(s) affected

1 record(s) affected

1 record(s) affected

1 record(s) affected

1 record(s) affected

1 record(s) affected

1 record(s) affected

1 record(s) affected

1 record(s) affected

1 record(s) affected

1 record(s) affected

1 record(s) affected

1 record(s) affected

1 record(s) affected

1 record(s) affected

column1
---------------
ROGERS NANCY R.

1 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]

[Executed: 07/07/06 12:42:41 EDT PM ] [Execution: 0/ms]

how do I supress or not get the above warnings, the column1 Rogers Nancy R. is correct.

but I get all those warning, so when I try to execute it from PHP it terminates.

bret

I think you can get rid of the "record(s) affected" lines by adding

set nocount on

at the beginning of the stored procedure.

As for the "output parameter ignored" - what does your parameter declaration
part of the procedure look like? (i.e. everything from "create procedure" to "as ..."

nplib

ASKER

Here it is, but I wasn't the one who created it.
create proc reconstruct
(@processed varchar(255),
@reconst varchar(255),
@reconstructed varchar(255) output)
as
declare @reconst_pos int,
@processed_pos int,
@reconst_len int,
@processed_len int,
@achar char(1),
@before_char char(1),
@before_num int,
@after_char char(1),
@after_num int
/* set positional variables */
select @reconst_pos = 1
select @processed_pos = 0
/* calculate length of processed and reconst columns */
select @reconst_len = datalength(@reconst)
select @processed_len = datalength(@processed)
select @reconstructed = @processed
/* start checking the reconst data one char at a time */
while @reconst_pos < @reconst_len
begin
/* next character */
select @reconst_pos = @reconst_pos + 1
/* set current, previous and next characters (used in most reconst calculations)
*/
select @achar = substring(@reconst,@reconst_pos,1)
select @before_char = substring(@reconst,@reconst_pos-1,1)
select @before_num = ascii(@before_char)
select @after_char = substring(@reconst,@reconst_pos+1,1)
select @after_num = ascii(@after_char)
/* start checking for the action characters I, M, R, 2, P, E, D */
/* insert one character */
if @achar = "I"
begin
select @reconstructed = substring(@reconstructed, 1, @before_num - 1)
+ @after_char
+ substring(@reconstructed, @before_num, 255)
/* increment position in reconst to jump past characters we have just used */
select @reconst_pos = @reconst_pos + 2
continue
end
/* insert many characters */
if @achar = "M"
begin
select @reconstructed = substring(@reconstructed, 1, @before_num - 1)
+ substring(substring(@reconst, @reconst_pos + 2, @after_num) + space(@after_num), 1, @after_num)
+ substring(@reconstructed, @before_num, 255)
/* increment position in reconst to jump past characters we have just used */
select @reconst_pos = @reconst_pos + 2 + @after_num
continue
end
/* Replace */
if @achar = "R"
begin
if @after_char is NULL
select @after_char = " "
select @reconstructed = substring(@reconstructed, 1, @before_num - 1)
+ @after_char
+ substring(@reconstructed, @before_num + 1, 255)
/* increment position in reconst to jump past characters we have just used */
select @reconst_pos = @reconst_pos + 2
continue
end
/* replace 2 */
if @achar = "2"
begin
if @after_char = NULL
select @after_char = " "
select @reconstructed = substring(@reconstructed, 1, @before_num - 1)
+ @after_char
+ substring(@reconstructed, @before_num + 2, 255)
/* increment position in reconst to jump past characters we have just used */
select @reconst_pos = @reconst_pos + 2
continue
end
/* ReplacePair */
if @achar = "P"
begin
select @reconstructed = substring(@reconstructed, 1, @before_num - 1)
+ substring(substring(@reconst, @reconst_pos+1, 2) + " ", 1, 2)
+ substring(@reconstructed, @before_num + 1, 255)
/* increment position in reconst to jump past characters we have just used */
select @reconst_pos = @reconst_pos + 3
continue
end
/* delete */
if @achar = "E"
begin
select @reconstructed = substring(@reconstructed, 1, @before_num - 1)
+ substring(@reconstructed, @before_num + 1, 255)
/* increment position in reconst to jump past characters we have just used */
select @reconst_pos = @reconst_pos + 1
continue
end
/* delete many */
if @achar = "D"
begin
select @reconstructed = substring(@reconstructed, 1, @before_num - 1)
+ substring(@reconstructed, @before_num + @after_num, 255)
/* increment position in reconst to jump past characters we have just used */
select @reconst_pos = @reconst_pos + 2
continue
end
/* Clobber */
if @achar = "C"
begin
select @reconstructed = substring(@reconstructed, 1, @before_num - 1)
+ substring(@reconst, @reconst_pos+1, 255)
/* increment position in reconst to jump past characters we have just used */
select @reconst_pos = @reconst_len
continue
end
/* end of while loop */
end
return

ASKER CERTIFIED SOLUTION

grant300

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

nplib

ASKER

still get same warning messages

nplib

ASKER

I don't know if I do or not, but asume that I do, I'll try anything now.

grant300

What was the stuff you are putting in the second argument???

It occurs to me that if it will screw up an editor, it might well be a problem for Sybase to swallow.
If you are trying to pass anything other than printable ASCII, you are asking for trouble.

Try it again without and put something benign in the middle argument. That is, no NULLs, etc.

If that works, ultimately you will have to rework the code to get rid of the funky stuff from the @reconst argument.

I would suggest using something like \255 or some similar escape method.

Bill

nplib

ASKER

It's complicated to explain.
But I will try,

The database was designed by a company called SirsiDynix. The have a stored procedure that takes a Title like Nancy R. Rogers and splits it into seperate fields in the database.
1 field will have the words, and the other will have the puncuation, special charaters, the's, and's, and other stuff. But it contains those weird box characters to tell it where to put the stuff. Then it uses another stored procedure to put it back in place. They are just not telling me how to do it. I just have to guess.

nplib

ASKER

Also I found that the query browser I was using we causing the warnings, if I use the SQL Advantage that comes with Sybase it works without errors.

But now I have to figure out how to call the SP from ASP.
So unless you know I will just accept the answer you gave me, that gave me the result I was looking for in SQL Avantage.

grant300

Wow. I don't know what to tell you about ASP but I suspect sending the funky stuff back and forth is going to cause you a problem someplace. Give it a try and see what happens; maybe you'll get lucky.

Sorry I can't solve the Active Server Page issues for you.

Bill