October 16, 2008 at 12:27 pm
I have a third party application that uses arrays, and I would like to return a number of strings back from a single table in one return value to populate the array, but I am new at this, and quickly losing all of my hair!
The array I am moving the data into is sched[10] where the values would bo from the sql table SCHEDULE with columns facility, line, shift, sequence, item1, item2......
I just don't understand how to send all of that data back in a single @output parameter...
Any help will be greatly appreciated!!!
October 16, 2008 at 12:34 pm
Hello,
One way would be to declare a bigString varchar(4000) and then concatenate the individual strings together in to that bigString variable and use it for the return value.
That seems so apparent the I wonder if I'm understanding your problem correctly.
Something like :
set @bigString = null
select @bigString = @bigString + faculty + line + shift + etc.
return @bigString
Give us more information if you can.
Thanks,
Terry
October 16, 2008 at 12:39 pm
A proc's return value is to be used more like an execution return code (ok or not ok)
However you can return a row set !
Just run the select statement within your proc and the row set will be returned to the calling object.
Create proc myproc
@myinputparam integer, @mysecondparam char(10)
as
begin
set nocount on
/* this will return the rowset of the query */
select col1, col2 from mytable
where paramcolA = @myinputparam
and pracolB = @mysecondparam
if @@rowcount = 0
return (-1) -- execution not OK because you always expect a resultset (non empty)
else
return (0) -- execution OK
end
You can also use outputparameters like
Create proc myproc
@myinputparam integer, @mysecondparam char(10)
, @MyOutputparam datetime
as
begin
set nocount on
/* this will return the rowset of the query */
select @MyOutputparam = max(timestampUpdate)
from mytable
where paramcolA = @myinputparam
and pracolB = @mysecondparam
if @@rowcount = 0
return (-1) -- execution not OK because you always expect a resultset (non empty)
else
return (0) -- execution OK
end
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 16, 2008 at 12:42 pm
I might be able to use that, but my issue is that the third party defines tharray with all members as (it appears) 81 characters. So if I pass the sql string back and it corelates a character by character movement into the array, my individual strings would be screwy. Is there a way to pad the @output string in between string values without doing ...+ ' ' +?
October 16, 2008 at 12:50 pm
Thanks ALZDBA, but how would I associate the recordset with a parm name?
October 16, 2008 at 7:47 pm
Jaggity, can you please show us what your expected output should look like?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 17, 2008 at 12:06 am
jaggity (10/16/2008)
Thanks ALZDBA, but how would I associate the recordset with a parm name?
You don't.
If you want to pass a rowset, just perform a select statement in your poc as shown.
@-parameters can only contain a single value at a time.
You could use a column datatyped XML and issue a query resulting int an XML result, but I'd prefer just passing the row set !
For solutions on string manipulations, there are a number of articles at SSC.
e.g. http://www.sqlservercentral.com/articles/Test+Data/61572/
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 17, 2008 at 4:39 am
jaggity
Just write the query in the sp and after that type the command return.
this will return all the required data
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
October 17, 2008 at 5:50 am
jaggity (10/16/2008)
I might be able to use that, but my issue is that the third party defines tharray with all members as (it appears) 81 characters. So if I pass the sql string back and it corelates a character by character movement into the array, my individual strings would be screwy. Is there a way to pad the @output string in between string values without doing ...+ ' ' +?
Use the CHAR data type instead of VARCHAR. CHAR is a fixed length field and it automatically pads out the string.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply