How would you create a stored procedure to insert records containing a field that has mulitple values using SQL Server 2005?

I am creating a Stored Procedure to insert records into a table using SQL Server 2005.

I have a table titled tblA with the following 3 fields and the last field titled

ELEMENT_TYPE_VALUE_PAIRS can have mulitple values in it's field:

ID_TYPE     PRIN_NAME   ELEMENT_TYPE_VALUE_PAIRS

K                          smithj           {(User_Type, Master Access)}  
K                          smithj           {{(Holder,holder-1),(Action,All)}}  
K                          smithj           {{(Holder,holder-2),(Action,Update)}}                
K                          cliffr              {{(Holder,holder-1),(Action,Browse)}}  
K                          cliffr              {(User_Type,Security Officer Access)}  

How would you create a stored procedure to populate table tblA with the values listed above?
zimmer9Asked:
Who is Participating?
 
f_o_o_k_yConnect With a Mentor Commented:
I would use this:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Fooky
-- Create date: 2012-04-10
-- Description:	simple insert
-- =============================================
CREATE PROCEDURE sp_simple_insert 
	-- Add the parameters for the stored procedure here
	@id_type int, 
	@name varchar(50),
	@value varchar(1000)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	INSERT INTO table( ID_TYPE  ,   PRIN_NAME ,  ELEMENT_TYPE_VALUE_PAIRS)
	VALUES(@id_type,@name,@value)
END
GO

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.