Function with Dynamic Column Name without Execute statement

Hi,

There is a table named status keeping status of a policy at the end of every month.

The schema of the table is as below.

sym pol mod stat_198612 stat_198701 stat_198702 stat_198703
AAB 1409502 00 f f f f
AAB 1409502 01 f f f f
AAB 1409503 00 f f f f
AAB 1409503 01 f f f f
AAB 1409504 00 f f f f
AAB 1409505 00 f f f f
AAB 1409506 00 f f f f
AAB 1409507 00 f f f f
AAB 1409508 00 f f f f
AAB 1409509 00 f f f f

I want to write a function by using Sql Server 2000 so that I can pass the value of sym, pol, mod and month_end_year like '198612' and will get the status of the policy.

I know that the create function has certain limitation in SQL2000 and support Execute statement only for extended stored procedure.

Any Quick suggestion will be highly appreciable..........

Thanks in advance.......

Vishnu:

The first thing is that this query becomes much easier if your status values are stored as rows in a separate table rather than specific columns of the policy table. This is an implementation type to which you should give some consideration. If not what follows is one of many potential solutions that can be implemented.

First, notice that I created an auxillary "mapping" table. This can be replaced by a "hard-coded" table variable in the function if you like that idea better than the permanent table. In addition, this assumes that all of the columns have a length of one and are non-null. Appropriate adjustments will need to be made if these assumptions are not correct. This function returns NULL if there is no target entry to be found.

create table dbo.policyStatusMap
( statusType varchar(10) not null
constraint pk_policyStatusMap primary key,
position tinyint not null
)
go

insert into dbo.policyStatusMap values ('198612', 1)
insert into dbo.policyStatusMap values ('198701', 1)
insert into dbo.policyStatusMap values ('198702', 1)
insert into dbo.policyStatusMap values ('198703', 1)
go

drop function dbo.policyStatus
go

create function dbo.policyStatus
( @pm_sym varchar(5),
@pm_pol varchar(10),
@pm_mod char(2),
@pm_statType varchar(10)
)
returns char(1)
as
begin

return
(
select substring (stat_198612 + stat_198701 + stat_198702 + stat_198703, position, 1)
as status
from dbo.policy
inner join dbo.policyStatusMap
on statusType = @pm_statType
and sym = @pm_sym
and pol = @pm_pol
and mod = @pm_mod
)

end

go

select dbo.policyStatus ('AAB', '1409504', '00', '198612') as policyStatus

-- ---------- Output: ----------

-- policyStatus
-- ------------
-- f

Hi Waldrop,

I appreciate you , I agree the above work where you have a table with constant no of columns, but as I said the table status gets buildup every month and gets added a new columns. The column have the policy status for the current month.

if you look into function

select substring (stat_198612 + stat_198701 + stat_198702 + stat_198703, position, 1)
as status

you have concatenated all columns , looks good, but I am not certain about how more columns I would have after a period of time . if I write a dynamic query , function has end up with schema.

I just have added one more record into

insert into dbo.policyStatusMap values ('199503', 1)

and when I execute

select dbo.policyStatus ('AAB', '1409504', '00', '199503') as policyStatus

returns me the same result as 'f'

where as the policy had a status 'i' for the month and year..........

Hi Louis,

Thanks a lot and I agreed you but I was wondering if it might be a good idea to put a column with name "Period" and rotate status into that column because when you do that the table grows enormously every month you get almost same or different millions of rows in the table. for exe if you have 1 million policies in a month and keep the table updating you get almost same and more 1 million record every month and it become a handleing problem after a period of time.

let me know if you have a different openion with me.

Thank you.