pears.BroadBeanCreatePost

CREATE FUNCTION "pears"."BroadBeanCreatePost"( IN @Instance CHAR(20),IN @JobTitle CHAR(65),IN @Industry CHAR(100),IN @JobType CHAR(9),IN @VacancyID CHAR(20),IN @BroadbeanLocationID INTEGER,IN @JobRef CHAR(20),IN @Duration CHAR(30),IN @StartDate CHAR(30),IN @Salary_From INTEGER,IN @Salary_To INTEGER,IN @Salary_per CHAR(5),IN @Salary_Benefits CHAR(50),IN @Skills CHAR(65),IN @Description CHAR(2500),IN @Secure CHAR(1) ) 
RETURNS long VARCHAR
BEGIN
  DECLARE "URL" long VARCHAR;
  DECLARE @UserName CHAR(100);
  DECLARE @Password CHAR(100);
  DECLARE @Location1 CHAR(100);
  DECLARE @Location2 CHAR(100);
  DECLARE @Location3 CHAR(100);
  DECLARE @BoardList CHAR(8000);
  SELECT "UserName","Password" INTO @UserName,@Password FROM "BroadBeanUser" WHERE "StaffID" = "UserStaffID";
  SELECT "Level1","Level2","Level3" INTO @Location1,@Location2,@Location3 FROM "BroadBeanLocation" WHERE "BroadBeanLocationID" = @BroadbeanLocationID;
  SELECT "list"("string"('&destination_',"code",'=on'),'') INTO @BoardList FROM "TTBroadBeanBoardsToPostTo" WHERE "Instance" = @Instance AND "Selected" = 1;
  SET "URL" = "string"('http',
    IF @Secure = 'T' THEN 's' ELSE NULL endif,
    '://www.adcourier.com/broadcast-step-2.cgi?','username=',
    "urlsafe"(@UserName),'&password=',
    "urlsafe"(@Password),
    @BoardList,'&jobtype=',
    "urlsafe"(@JobType),'&jobref=',
    "urlsafe"(@JobRef),'&duration=',
    "urlsafe"(@Duration),'&startdate=',
    "urlsafe"(@StartDate),'&salary_from=',
    "urlsafe"(@Salary_From),'&salary_to=',
    "urlsafe"(@Salary_To),'&salary_per=',
    "urlsafe"("lower"(@Salary_per)),'&salary_benefits=',
    "urlsafe"(@Salary_Benefits),'&location_1=',
    "urlsafe"(@Location1),'&location_2=',
    "urlsafe"(IF @Location2 = @Location3 THEN @Location2
    ELSE "string"('--',@Location3)
    endif),'&jobtitle=',"urlsafe"(@JobTitle),'&industry=',
    "urlsafe"(@Industry),'&skills=',
    "urlsafe"(@Skills),'&description=',
    "urlsafe"(@Description));
  RETURN "URL"
END