April 15, 2003 at 9:39 am
I have a stored procedure thats passing right now 58 fields(subject to go higher, my boss automagically found other stuff to add) across 7 tables. For the most part its a 1 to 1 relationship across all the tables except for 1 table. It has a one to many across the other tables. 25 of the fields are Employee's off and he wants to list the names of those employees. So I created a table with today's date(primary key) and employee name. Now, stored procedures do not support arrays and now I got 25 fields being passed and was wondering what is the best way of handling this. 95% of the time, he will only be using 2 or 3 of the 25 fields. I thought of the idea of using a split function but t-sql has no split function(to pass only 1 comma sepearated list of employee's).
Any idea's on how to accomplish this task w/o checking each of the 25 fields to see if they are blank(thats a lot of code).
Comments are welcome,
Matt
April 15, 2003 at 11:02 am
One alternative is as follows
1. Create a temporary table #tmp_emp(emp_id),
Store the field values (25 fields) in this table in the calling routine.
2. Join this table with employee table to display the employee datails.
Hope this helps.
April 15, 2003 at 12:28 pm
no I do not need to view, that is pretty straightfoward I need to be able to write to the table. Since there is no array's it gets a lot more code just to write the 25 values(if there is even 25).
Matt
April 16, 2003 at 3:29 am
Depending on what calls the proc you could put the 25 fields into one separated by commas and then parse this in the proc into a temp table for joining. This will enable you to pass only the names required or check for empty values when parsing.
Far away is close at hand in the images of elsewhere.
Anon.
April 17, 2003 at 7:32 am
Hmm that sounds good david but how do you parse out the comma delimited variable as there is no type of split function that I found?
Matt
April 17, 2003 at 9:18 am
Use a while loop
create table tblnames (nam varchar(20))
declare @s-2 varchar(1000),@i int
set @s-2 = 'John,,Mike,,,Sue,,'
set @i = charindex(',',@s)
while @i>0
begin
if left(@s,@i-1) <> ''
begin
insert into tblnames values (left(@s,@i-1))
end
set @s-2 = right(@s,len(@s)-@i)
set @i = charindex(',',@s)
end
print @s-2
select * from tblnames
drop table tblnames
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply