Solved

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

Posted on 2012-04-10
1
183 Views
Last Modified: 2012-04-16
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?
0
Comment
Question by:zimmer9
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 11

Accepted Solution

by:
f_o_o_k_y earned 500 total points
ID: 37830142
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

Featured Post

Office 365 Training for IT Pros

Learn how to provision Office 365 tenants, synchronize your on-premise Active Directory, and implement Single Sign-On.

Question has a verified solution.

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

Suggested Solutions

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
Know what services you can and cannot, should and should not combine on your server.
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

737 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