> hello all,
>
> I am tring to build a form building tool inside the database. I know most
of
> you will say put it in the application layer, but I would like to keep
this
> text formatting in the database so I only have to write the function once
and
> our 2 unix server, and one windows server can all access the same code to
> draw the form elements.
>
> below it my function so far, i cannot get the syntax correct do do a
select
> in a Case statement. Is this possible in t-SQL?
>
> -- function
> IF EXISTS (SELECT *
> FROM sysobjects
> WHERE name = N'fn_draw_form_element')
> DROP FUNCTION fn_draw_form_element
> GO
>
> CREATE FUNCTION fn_draw_form_element ( @id_form_detail int)
>
> RETURNS varchar(8000)
> AS
> Begin
>
> declare
> @element_start varchar(50),
> @element_middle varchar(2000),
> @element_end varchar(50),
>
> @element_final varchar(2100),
> @detail_type varchar(50)
>
>
> -- find out what type of element it is
> select @detail_type = detail_type
> from form_detail with (nolock)
> where id_form_detail = @id_form_detail
>
> -- select the form in three parts
> select
> @element_start =
> case @detail_type
> when 'select' then '<select name="' + cast(@id_form_detail as
> varchar(10)) + '">'
> end,
>
> @element_middle =
> case @detail_type
> when 'select' then '<option value=' + coalesce(@element_middle+' '+
> char(13) + char(10),'') + element_name from (select element_name from
> form_element with (nolock) where id_form_detail = @id_form_detail)
> element_name + '">'
> end
> -- select @element_display
>
>
> set @element_final = @element_start + @element_middle
>
> return @element_final
>
> End
>
> Go
>
> -- related tables
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_form_elements_form_detail]') and OBJECTPROPERTY(id,
> N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[form_element] DROP CONSTRAINT
FK_form_elements_form_detail
> GO
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_form_detail_form_main]') and OBJECTPROPERTY(id,
> N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[form_detail] DROP CONSTRAINT FK_form_detail_form_main
> GO
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[form_detail]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
> drop table [dbo].[form_detail]
> GO
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[form_element]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
> drop table [dbo].[form_element]
> GO
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[form_main]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
> drop table [dbo].[form_main]
> GO
>
> CREATE TABLE [dbo].[form_detail] (
> [id_form_detail] [int] IDENTITY (1, 1) NOT NULL ,
> [id_form_main] [int] NOT NULL ,
> [detail_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
> [detail_type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
> [required] [int] NOT NULL ,
> [order_by] [int] NOT NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[form_element] (
> [id_form_element] [int] IDENTITY (1, 1) NOT NULL ,
> [id_form_detail] [int] NOT NULL ,
> [element_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
> [order_by] [int] NOT NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[form_main] (
> [id_form_main] [int] IDENTITY (1, 1) NOT NULL ,
> [id_agency] [int] NOT NULL ,
> [form_title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
> [is_emailed] [int] NOT NULL
> ) ON [PRIMARY]
> GO
>
>
>
>

> hello all,
>
> I am tring to build a form building tool inside the database. I know most
of
> you will say put it in the application layer, but I would like to keep
this
> text formatting in the database so I only have to write the function once
and
> our 2 unix server, and one windows server can all access the same code to
> draw the form elements.
>
> below it my function so far, i cannot get the syntax correct do do a
select
> in a Case statement. Is this possible in t-SQL?
>
> -- function
> IF EXISTS (SELECT *
> FROM sysobjects
> WHERE name = N'fn_draw_form_element')
> DROP FUNCTION fn_draw_form_element
> GO
>
> CREATE FUNCTION fn_draw_form_element ( @id_form_detail int)
>
> RETURNS varchar(8000)
> AS
> Begin
>
> declare
> @element_start varchar(50),
> @element_middle varchar(2000),
> @element_end varchar(50),
>
> @element_final varchar(2100),
> @detail_type varchar(50)
>
>
> -- find out what type of element it is
> select @detail_type = detail_type
> from form_detail with (nolock)
> where id_form_detail = @id_form_detail
>
> -- select the form in three parts
> select
> @element_start =
> case @detail_type
> when 'select' then '<select name="' + cast(@id_form_detail as
> varchar(10)) + '">'
> end,
>
> @element_middle =
> case @detail_type
> when 'select' then '<option value=' + coalesce(@element_middle+' '+
> char(13) + char(10),'') + element_name from (select element_name from
> form_element with (nolock) where id_form_detail = @id_form_detail)
> element_name + '">'
> end
> -- select @element_display
>
>
> set @element_final = @element_start + @element_middle
>
> return @element_final
>
> End
>
> Go
>
> -- related tables
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_form_elements_form_detail]') and OBJECTPROPERTY(id,
> N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[form_element] DROP CONSTRAINT
FK_form_elements_form_detail
> GO
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_form_detail_form_main]') and OBJECTPROPERTY(id,
> N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[form_detail] DROP CONSTRAINT FK_form_detail_form_main
> GO
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[form_detail]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
> drop table [dbo].[form_detail]
> GO
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[form_element]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
> drop table [dbo].[form_element]
> GO
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[form_main]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
> drop table [dbo].[form_main]
> GO
>
> CREATE TABLE [dbo].[form_detail] (
> [id_form_detail] [int] IDENTITY (1, 1) NOT NULL ,
> [id_form_main] [int] NOT NULL ,
> [detail_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
> [detail_type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
> [required] [int] NOT NULL ,
> [order_by] [int] NOT NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[form_element] (
> [id_form_element] [int] IDENTITY (1, 1) NOT NULL ,
> [id_form_detail] [int] NOT NULL ,
> [element_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
> [order_by] [int] NOT NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[form_main] (
> [id_form_main] [int] IDENTITY (1, 1) NOT NULL ,
> [id_agency] [int] NOT NULL ,
> [form_title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
> [is_emailed] [int] NOT NULL
> ) ON [PRIMARY]
> GO
>
>
>
>

> hello all,
>
> I am tring to build a form building tool inside the database. I know most
> of
> you will say put it in the application layer, but I would like to keep
> this
> text formatting in the database so I only have to write the function once
> and
> our 2 unix server, and one windows server can all access the same code to
> draw the form elements.
>
> below it my function so far, i cannot get the syntax correct do do a
> select
> in a Case statement. Is this possible in t-SQL?
>
> -- function
> IF EXISTS (SELECT *
> FROM sysobjects
> WHERE name = N'fn_draw_form_element')
> DROP FUNCTION fn_draw_form_element
> GO
>
> CREATE FUNCTION fn_draw_form_element ( @id_form_detail int)
>
> RETURNS varchar(8000)
> AS
> Begin
>
> declare
> @element_start varchar(50),
> @element_middle varchar(2000),
> @element_end varchar(50),
>
> @element_final varchar(2100),
> @detail_type varchar(50)
>
>
> -- find out what type of element it is
> select @detail_type = detail_type
> from form_detail with (nolock)
> where id_form_detail = @id_form_detail
>
> -- select the form in three parts
> select
> @element_start =
> case @detail_type
> when 'select' then '<select name="' + cast(@id_form_detail as
> varchar(10)) + '">'
> end,
>
> @element_middle =
> case @detail_type
> when 'select' then '<option value=' + coalesce(@element_middle+' '+
> char(13) + char(10),'') + element_name from (select element_name from
> form_element with (nolock) where id_form_detail = @id_form_detail)
> element_name + '">'
> end
> -- select @element_display
>
>
> set @element_final = @element_start + @element_middle
>
> return @element_final
>
> End
>
> Go
>
> -- related tables
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_form_elements_form_detail]') and OBJECTPROPERTY(id,
> N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[form_element] DROP CONSTRAINT
> FK_form_elements_form_detail
> GO
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_form_detail_form_main]') and OBJECTPROPERTY(id,
> N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[form_detail] DROP CONSTRAINT FK_form_detail_form_main
> GO
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[form_detail]') and OBJECTPROPERTY(id, N'IsUserTable') =
> 1)
> drop table [dbo].[form_detail]
> GO
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[form_element]') and OBJECTPROPERTY(id, N'IsUserTable')
> = 1)
> drop table [dbo].[form_element]
> GO
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[form_main]') and OBJECTPROPERTY(id, N'IsUserTable') =
> 1)
> drop table [dbo].[form_main]
> GO
>
> CREATE TABLE [dbo].[form_detail] (
> [id_form_detail] [int] IDENTITY (1, 1) NOT NULL ,
> [id_form_main] [int] NOT NULL ,
> [detail_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [detail_type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [required] [int] NOT NULL ,
> [order_by] [int] NOT NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[form_element] (
> [id_form_element] [int] IDENTITY (1, 1) NOT NULL ,
> [id_form_detail] [int] NOT NULL ,
> [element_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [order_by] [int] NOT NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[form_main] (
> [id_form_main] [int] IDENTITY (1, 1) NOT NULL ,
> [id_agency] [int] NOT NULL ,
> [form_title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [is_emailed] [int] NOT NULL
> ) ON [PRIMARY]
> GO
>
>
>
>