?
Solved

Build stored procedure to populate dropdownlist.

Posted on 2009-04-27
5
Medium Priority
?
228 Views
Last Modified: 2012-05-06
I have the following.
create table tbl_lookup_field_ticket_contact_method
  (lftcm_id  int not null identity primary key clustered
  ,lftcm_name  varchar(20) not null)
go
insert into tbl_lookup_field_ticket_contact_method (lftcm_name) values('Email')
insert into tbl_lookup_field_ticket_contact_method (lftcm_name) values('Phone')
insert into tbl_lookup_field_ticket_contact_method (lftcm_name) values('Web')
insert into tbl_lookup_field_ticket_contact_method (lftcm_name) values('Fax')
-----------------------------------------------------------------------------------
create table tbl_lookup_field_ticket_location_sims
  (lftls_id  int not null identity primary key clustered
  ,lftls_name  varchar(20) not null)
go
insert into tbl_lookup_field_ticket_location_sims  (lftls_name) values('Canada')
insert into tbl_lookup_field_ticket_location_sims  (lftls_name) values('US')
insert into tbl_lookup_field_ticket_location_sims  (lftls_name) values('US and Canada')
insert into tbl_lookup_field_ticket_location_sims  (lftls_name) values('Overseas')
-----------------------------------------------------------------------------------
create table tbl_lookup_field_ticket_features
  (lftf_id  int not null identity primary key clustered
  ,lftf_name  varchar(80) not null)
go
insert into tbl_lookup_field_ticket_features  (lftf_name) values('Set Ticket to Waiting Status')
insert into tbl_lookup_field_ticket_features  (lftf_name) values('Reactivate Waiting Ticket')
insert into tbl_lookup_field_ticket_features  (lftf_name) values('Assigned To Email')
insert into tbl_lookup_field_ticket_features  (lftf_name) values('NOC Ticket Email')
insert into tbl_lookup_field_ticket_features  (lftf_name) values('Developer Email')
insert into tbl_lookup_field_ticket_features  (lftf_name) values('Companies Affected Email')
insert into tbl_lookup_field_ticket_features  (lftf_name) values('POCs Associated Email')
insert into tbl_lookup_field_ticket_features  (lftf_name) values('Services Affected Email')
insert into tbl_lookup_field_ticket_features  (lftf_name) values('List Associated Companies')
insert into tbl_lookup_field_ticket_features  (lftf_name) values('Delete This Ticket')
insert into tbl_lookup_field_ticket_features  (lftf_name) values('Update This Ticket')


I need to build three stored procedures.
sprLookupTicketContactMethod
sprLookupTicketLocationSIMs
sprLookupTicketFeatures

Any help would be great!
I think a select * from table should suffice. However, if you can help me out with the layout
since I do not have sql on my machine I am on.
0
Comment
Question by:mathieu_cupryk
  • 3
  • 2
5 Comments
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 24246132
Try these, they return all rows in each respective table.
create procedure sprLookupTicketContactMethod 
as
select lftcm_id, lftcm_name
from tbl_lookup_field_ticket_contact_method
go
 
create procedure sprLookupTicketLocationSIMs
as
select lftls_id, lftls_name
from tbl_lookup_field_ticket_location_sims
go
 
create procedure sprLookupTicketFeatures
as
select lftf_id, lftf_name
from tbl_lookup_field_ticket_features
go

Open in new window

0
 

Author Comment

by:mathieu_cupryk
ID: 24246768
This looks great but I had to change the table.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_lookup_field_ticket_contact_method](
      [ID] [int] IDENTITY(1,1) NOT NULL,
      [ContactMethod] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [OrderNum] [int] NULL,
      [ContactMethodDescription] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [DateCreated] [datetime] NULL CONSTRAINT [DF_tbl_lookup_ticket_severity_DateCreated]  DEFAULT (getdate()),
 CONSTRAINT [PK_tbl_lookup_field_ticket_contact_method] PRIMARY KEY CLUSTERED
(
      [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF


Now the description will be in the ContactMethod

0
 

Author Comment

by:mathieu_cupryk
ID: 24247128
Here are the new tables:

/****** Object:  Table [dbo].[tbl_lookup_field_ticket_contact_method]    Script Date: 04/27/2009 21:06:26 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tbl_lookup_field_ticket_contact_method]') AND type in (N'U'))
DROP TABLE [dbo].[tbl_lookup_field_ticket_contact_method]

/****** Object:  Table [dbo].[tbl_lookup_field_ticket_contact_method]    Script Date: 04/27/2009 21:05:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_lookup_field_ticket_contact_method](
      [ID] [int] IDENTITY(1,1) NOT NULL,
      [ContactMethod] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [OrderNum] [int] NULL,
      [ContactMethodDescription] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [DateCreated] [datetime] NULL CONSTRAINT [DF_tbl_lookup_field_ticket_contact_method_DateCreated]  DEFAULT (getdate()),
 CONSTRAINT [PK_tbl_lookup_field_ticket_contact_method] PRIMARY KEY CLUSTERED
(
      [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

------------------------------------------------------------------------------------------------------
/****** Object:  Table [dbo].[tbl_lookup_field_ticket_location_sims]    Script Date: 04/27/2009 21:06:55 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tbl_lookup_field_ticket_location_sims]') AND type in (N'U'))
DROP TABLE [dbo].[tbl_lookup_field_ticket_location_sims]

/****** Object:  Table [dbo].[tbl_lookup_field_ticket_location_sims]    Script Date: 04/27/2009 21:03:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_lookup_field_ticket_location_sims](
      [ID] [int] IDENTITY(1,1) NOT NULL,
      [LocationSIMs] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [OrderNum] [int] NULL,
      [LocationSIMsDescription] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [DateCreated] [datetime] NULL CONSTRAINT [DF_tbl_lookup_field_ticket_location_DateCreated]  DEFAULT (getdate()),
 CONSTRAINT [PK_tbl_lookup_field_ticket_location_sims] PRIMARY KEY CLUSTERED
(
      [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF


-------------------------------------------------------------------------------------------------------
/****** Object:  Table [dbo].[tbl_lookup_field_ticket_features]    Script Date: 04/27/2009 21:07:25 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tbl_lookup_field_ticket_features]') AND type in (N'U'))
DROP TABLE [dbo].[tbl_lookup_field_ticket_features]

/****** Object:  Table [dbo].[tbl_lookup_field_ticket_features]    Script Date: 04/27/2009 21:04:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_lookup_field_ticket_features](
      [ID] [int] IDENTITY(1,1) NOT NULL,
      [Features] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [OrderNum] [int] NULL,
      [FeaturesDescription] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [DateCreated] [datetime] NULL CONSTRAINT [DF_tbl_lookup_field_ticket_features_DateCreated]  DEFAULT (getdate()),
 CONSTRAINT [PK_tbl_lookup_field_ticket_features] PRIMARY KEY CLUSTERED
(
      [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

I have to create stored procedure for each one.
0
 
LVL 27

Accepted Solution

by:
Chris Luttrell earned 2000 total points
ID: 24247624
These are changed for the new table structures.
create procedure sprLookupTicketContactMethod 
as
select [ID], [ContactMethod], [OrderNum], [ContactMethodDescription], [DateCreated]
from [dbo].[tbl_lookup_field_ticket_contact_method]
go
 
create procedure sprLookupTicketLocationSIMs
as
select [ID], [LocationSIMs], [OrderNum], [LocationSIMsDescription], [DateCreated]
from [dbo].[tbl_lookup_field_ticket_location_sims]
go
 
create procedure sprLookupTicketFeatures
as
select [ID], [Features], [OrderNum], [FeaturesDescription], [DateCreated]
from [dbo].[tbl_lookup_field_ticket_features]
go

Open in new window

0
 

Author Closing Comment

by:mathieu_cupryk
ID: 31575158
Great!
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Loops Section Overview
Screencast - Getting to Know the Pipeline

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question