-- =============================================
--string fields
-- =============================================
IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'ddf_GetExtenderFieldID2')
DROP FUNCTION ddf_GetExtenderFieldID2
GO
CREATE FUNCTION ddf_GetExtenderFieldID2 (@Extender_Window_ID varchar(15), @FIELDNAM varchar(31))
--run this line to test the function:
-- select dbo.ddf_GetExtenderFieldID2 ('SOPLINE','Customer Directed Price')
RETURNS INT
AS
begin
DECLARE @Extender_Field_IDs_1 INT, @Extender_Field_IDs_2 INT, @Extender_Field_IDs_3 INT,
@Extender_Field_IDs_4 INT, @Extender_Field_IDs_5 INT, @Extender_Field_IDs_6 INT,
@Extender_Field_IDs_7 INT, @Extender_Field_IDs_8 INT, @Extender_Field_IDs_9 INT,
@Extender_Field_IDs_10 INT, @Extender_Field_IDs_11 INT, @Extender_Field_IDs_12 INT,
@Extender_Field_IDs_13 INT, @Extender_Field_IDs_14 INT, @Extender_Field_IDs_15 INT
declare @fieldid int
--not my favorite way to code this. I'd love a suggestion on a better way to code it... but it works.
--credit to member gerispapajani who suggested this
select
@Extender_Field_IDs_1 =Extender_Field_IDs_1, @Extender_Field_IDs_2=Extender_Field_IDs_2, @Extender_Field_IDs_3=Extender_Field_IDs_3,
@Extender_Field_IDs_4=Extender_Field_IDs_4, @Extender_Field_IDs_5=Extender_Field_IDs_5, @Extender_Field_IDs_6=Extender_Field_IDs_6,
@Extender_Field_IDs_7=Extender_Field_IDs_7, @Extender_Field_IDs_8=Extender_Field_IDs_8, @Extender_Field_IDs_9=Extender_Field_IDs_9,
@Extender_Field_IDs_10=Extender_Field_IDs_10, @Extender_Field_IDs_11=Extender_Field_IDs_11, @Extender_Field_IDs_12=Extender_Field_IDs_12,
@Extender_Field_IDs_13=Extender_Field_IDs_13, @Extender_Field_IDs_14=Extender_Field_IDs_14, @Extender_Field_IDs_15=Extender_Field_IDs_15
from EXT20100 frm
where frm.Extender_Window_ID = @Extender_Window_ID
--return the field id
select @fieldid = fld.Field_ID
from ext20010 fld
where fld.FIELDNAM = @FIELDNAM
AND Field_ID IN (
@Extender_Field_IDs_1 , @Extender_Field_IDs_2 , @Extender_Field_IDs_3 ,
@Extender_Field_IDs_4 , @Extender_Field_IDs_5 , @Extender_Field_IDs_6 ,
@Extender_Field_IDs_7 , @Extender_Field_IDs_8 , @Extender_Field_IDs_9 ,
@Extender_Field_IDs_10 , @Extender_Field_IDs_11 , @Extender_Field_IDs_12 ,
@Extender_Field_IDs_13 , @Extender_Field_IDs_14 , @Extender_Field_IDs_15
)
return @fieldid
end
go
grant exec on ddf_GetExtenderFieldID2 to public