Solved

help creating SQL Stored Proceedure

Posted on 2012-04-06
11
248 Views
Last Modified: 2012-06-21
Hi,
I am a complete novice with stored proceedures, so I am hoping someone will be able to show me the code necessary to perform the following.

I have a table containing ( amonst other things) ID ( key), Project Name, Currency, element

The task of the SP is to
1) identify all rows where Currency is 'null'
2) for each row where Currency is 'null'
2a) Set Currency to 'GBP'
2b Insert new row where project name is the same, currency is same and element is set to '01'
2c insert new row where project name is the same, currency is same and element is set to '02'


So as an example
Before
0001, test1, GBP, 00
0002, test2,[null],00
0003, test3,[null],00
0004, test4, GBP,00

After
0001, test1, GBP, 00
0002, test2,GBP,00
0003, test3,GBP,00
0004, test4, GBP,00
0005, test2,GBP,01
0006, test2,GBP,02
0007, test3,GBP,01
0008, test3,GBP,02

Please note , won't be able to test solution until Tuesday, but have intermittent access to respond to any questions before that

Full points, so looking for complete, well documented solution

Hope this makes sense.
Many Thanks
0
Comment
Question by:supertramp4
  • 6
  • 2
  • 2
  • +1
11 Comments
 
LVL 39

Expert Comment

by:Kyle Abrahams
Comment Utility
-- originally had this as a union but was unsure if it would work, this should work for sure.  Replace <table> with your table name

insert into table(project_name, currency, element)
select project_name, currency, '01'
from <table>
where currency is null and element = '00'

insert into table(project_name, currency, element)
select project_name, currency, '02'
from <table>
where currency is null and element = '00'

update <table> set currency = 'GBP' where currency is null;
0
 
LVL 26

Expert Comment

by:Chris Luttrell
Comment Utility
Here is test code and a documented stored procedure to perform about what you want.
I am not sure exactly what your column names or data types are in your table and how you assign new ID (key) values to new records.  
This test just relies on an IDENTITY column which has to be an INT but your values look more like CHAR or VARCHAR values.
-- SETUP Creating a table for testing
CREATE TABLE YourTable (ID INT IDENTITY PRIMARY KEY, ProjectName VARCHAR(10), Currency VARCHAR(10), element CHAR(2));

SET IDENTITY_INSERT dbo.YourTable ON
INSERT INTO dbo.YourTable
		( ID, ProjectName, Currency, element )
 VALUES
('0001', 'test1', 'GBP', '00'),
('0002', 'test2',null,'00'),
('0003', 'test3',null,'00'),
('0004', 'test4', 'GBP','00');
SET IDENTITY_INSERT dbo.YourTable OFF

SELECT * FROM YourTable;
-- SETUP END ****************************************

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================================
-- Author:      Chris Luttrell
-- Created:     4/7/2012
-- Release:     
-- Description: test procedure to show how to update a table and use
--              the output to create other records in the same table
--
-- mm/dd/yy  Name     Release  Description
-- =============================================================
CREATE PROCEDURE [dbo].[usp_HandleNullCurrencyRecords]
AS
BEGIN

	DECLARE @tmpTable TABLE (ID CHAR(4)); -- setting up a table to capture updated IDs
	DECLARE @newElements TABLE (newElement CHAR(2)); -- set up table to supply new values
	-- load newElements table
	INSERT INTO @newElements ( newElement )
	VALUES	( '01' ), ( '02' )

	-- Perform the update replaceing null currency values
	UPDATE	YourTable
	SET		currency = 'GBP'
	OUTPUT (Inserted.ID) INTO @tmpTable(ID) -- OUTPUT gets the updated record IDs back to use later
	WHERE	currency IS NULL

	--SELECT * FROM @tmpTable TT; -- you can uncomment to see the IDs that were updated if needed

	-- Now Insert the new records you want into the table
	INSERT	INTO YourTable
			( projectname ,
			  currency ,
			  element
			)
			SELECT	projectname ,
					currency ,
					NE.newElement
			FROM	YourTable YT
			INNER JOIN @tmpTable TT ON TT.ID = YT.ID
			CROSS JOIN @newElements NE
			WHERE	YT.element = '00'

END

GO

-- TEST new Stored Procedure
EXEC usp_HandleNullCurrencyRecords;


-- PROOF Select from YourTable to see the results as desired
SELECT * FROM YourTable;

-- CLEANUP Drop the table we created for testing
DROP TABLE YourTable;
-- CLEANUP Drop the stored procedure we created for testing
DROP PROCEDURE usp_HandleNullCurrencyRecords;

Open in new window

Stored Procedure Results
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
It should be as simple as this:
CREATE PROCEDURE usp_MyStoredProcedure

AS

SET NOCOUNT ON

INSERT	YourTable(ID, ProjectName, Currency, Element)
SELECT	ID, ProjectName, 'GBP', '01'
FROM	YourTable
WHERE	Currency IS NULL 

UNION ALL

SELECT	ID, ProjectName, 'GBP', '02'
FROM	YourTable
WHERE	Currency IS NULL 

UPDATE	YourTable
SET	Currency = 'GPB'
WHERE	Currency IS NULL 

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Never mind that is not correct.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Let's try that again:
CREATE PROCEDURE usp_MyStoredProcedure

AS

SET NOCOUNT ON

INSERT	YourTable(ID, ProjectName, Currency, Element)
SELECT	RIGHT('000' + CAST(y.ID + ROW_NUMBER() OVER(ORDER BY ProjectName, Element) AS varchar(4)), 4) ID, 
	x.ProjectName, x.Currency, x.Element
FROM	(
	SELECT	ID, ProjectName, 'GBP' Currency, '01' Element
	FROM	YourTable
	WHERE	Currency IS NULL 
	
	UNION ALL

	SELECT	ID, ProjectName, 'GBP', '02'
	FROM	YourTable
		WHERE	Currency IS NULL) x
		CROSS JOIN (
		SELECT	MAX(CAST(ID AS smallint)) ID
		FROM	YourTable) y

UPDATE	YourTable
SET	Currency = 'GPB'
WHERE	Currency IS NULL 

Open in new window

0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Here is how I tested it:
DECLARE @YourTable TABLE (
	ID CHAR(4) NOT NULL,
	ProjectName varchar(20) NOT Null,
	Currency char(3) NULL,
	Element char(2) NOT NULL)

SET NOCOUNT ON

INSERT	@YourTable(
	ID, ProjectName, Currency, Element)
VALUES	('0001', 'test1', 'GBP', '00'),
	('0002', 'test2', NULL, '00'),
	('0003', 'test3', NULL, '00'),
	('0004', 'test4', 'GBP', '00')

INSERT	@YourTable(ID, ProjectName, Currency, Element)
SELECT	RIGHT('000' + CAST(y.ID + ROW_NUMBER() OVER(ORDER BY ProjectName, Element) AS varchar(4)), 4) ID, 
	x.ProjectName, x.Currency, x.Element
FROM	(
	SELECT	ID, ProjectName, 'GBP' Currency, '01' Element
	FROM	@YourTable
	WHERE	Currency IS NULL 

	UNION ALL
	
	SELECT	ID, ProjectName, 'GBP', '02'
	FROM	@YourTable
	WHERE	Currency IS NULL) x
	CROSS JOIN (
	SELECT	MAX(CAST(ID AS smallint)) ID
	FROM	@YourTable) y

UPDATE	@YourTable
SET	Currency = 'GPB'
WHERE	Currency IS NULL 

SELECT	ID, ProjectName, Currency, Element
FROM	@YourTable
ORDER BY
	ID

Open in new window


Here is the output:
ID	ProjectName	Currency	Element
0001	test1		GBP		00
0002	test2		GPB		00
0003	test3		GPB		00
0004	test4		GBP		00
0005	test2		GBP		01
0006	test2		GBP		02
0007	test3		GBP		01
0008	test3		GBP		02

Open in new window

0
 

Author Comment

by:supertramp4
Comment Utility
Hi lll, and many thanks for your suggestions.

I'm not suggesting that any of the above snipits are incorrect, but there are a few points that perhaps I was not clear enough, so I will try to expand.

1) there is no relationship on the value of 'element' for deciding when to insert a new row. it should be purely 'currency = [null]' . 'element' could be any value

2) The Table contains around 20 other Data columns, all of the values will need to be copied to the new rows that need inserting . For the purpose of this examples, call them 'DC01' DC02'... 'DC20'. Of the newly inserted rows it is only the 'element' that needs to be set to a new value.

3) On important point I forgot was that state is that there is a column 'Subelements'  As a result of inserting the 2 new rows, the origonal row 'Subelements' needs updating from 'N' to 'Y'

4) The ID Column is the primary key

Thus as an example
Before
ID,projectName,Currency,element,SubElements,DC01,DC02.....,DC20
0001, test1, GBP, ??,N,??,??.......,??
0002, test2,[null], ??,N,??,??.......,??
0003, test3,[null], ??,N,??,??.......,??
0004, test4, GBP, ??,N,??,??.......,??

After
ID,projectName,Currency,element,SubElements,DC01,DC02.....,DC20
0001, test1,GBP,??,N,??,??.......,??
0002, test2,GBP,??,Y,??,??.......,??
0003, test3,GBP,??,Y,??,??.......,??
0004, test4,GBP,??,N,??,??.......,??
0005, test2,GBP,01,N,??,??.......,??
0006, test2,GBP,02,N,??,??.......,??
0007, test3,GBP,01,N,??,??.......,??
0008, test3,GBP,02,N,??,??.......,??

Many thanks for your continued support, and appologies for not explaining myself fully in the first place. Any questions, please let me know.
0
 
LVL 39

Assisted Solution

by:Kyle Abrahams
Kyle Abrahams earned 250 total points
Comment Utility
This should work for you:


--get all ids where currency is null
select ID into #ids where currency is null

-- first insert
insert into table(project_name, currency, element, DC01, DC02) -- ADD OTHER COLUMNS HERE
select project_name, currency, '01', DC01, DC02 -- ADD OTHER COLUMNS HERE
from <table>
where currency is null

-- second insert
insert into table(project_name, currency, element, DC01, DC02)  -- ADD OTHER COLUMNS HERE
select project_name, currency, '02', DC01, DC02 -- ADD OTHER COLUMNS HERE
from <table>
where currency is null and id in (select id from #ids)  -- this is done to prevent duplicates from the statement above

-- update of subelements
update <table> set Subelements  = 'Y' where currency is null and id in (select id from #ids) -- parent object

--update to GBP
update <table> set currency = 'GBP' where currency is null;
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 250 total points
Comment Utility
My solution has not changed much:
INSERT	YourTable(ID, ProjectName, Currency, Element, SubElements,
	DC01, DC02, DC03, DC04, DC05, DC06, DC07, DC08, DC09, DC10,
	DC11, DC12, DC13, DC14, DC15, DC16, DC17, DC18, DC19, DC20)
SELECT	RIGHT('000' + CAST(y.ID + ROW_NUMBER() OVER(ORDER BY ProjectName, Element) AS varchar(4)), 4) ID, 
	x.ProjectName, x.Currency, x.Element, SubElements,
	DC01, DC02, DC03, DC04, DC05, DC06, DC07, DC08, DC09, DC10,
	DC11, DC12, DC13, DC14, DC15, DC16, DC17, DC18, DC19, DC20
FROM	(
	SELECT	ID, ProjectName, 'GBP' Currency, '01' Element, SubElements,
		DC01, DC02, DC03, DC04, DC05, DC06, DC07, DC08, DC09, DC10,
		DC11, DC12, DC13, DC14, DC15, DC16, DC17, DC18, DC19, DC20
	FROM	YourTable
	WHERE	Currency IS NULL 

	UNION ALL
	
	SELECT	ID, ProjectName, 'GBP', '02', SubElements,
		DC01, DC02, DC03, DC04, DC05, DC06, DC07, DC08, DC09, DC10,
		DC11, DC12, DC13, DC14, DC15, DC16, DC17, DC18, DC19, DC20
	FROM	YourTable
	WHERE	Currency IS NULL) x
	CROSS JOIN (
	SELECT	MAX(CAST(ID AS smallint)) ID
	FROM	YourTable) y

UPDATE	YourTable
SET	Currency = 'GPB',
	Subelements = 'Y'
WHERE	Currency IS NULL 

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Here is how I tested it:
DECLARE @YourTable TABLE (
	ID CHAR(4) NOT NULL,
	ProjectName varchar(20) NOT Null,
	Currency char(3) NULL,
	Element char(2) NOT NULL,
	SubElements CHAR(2),
	DC01 CHAR(2),
	DC02 CHAR(2),
	DC03 CHAR(2),
	DC04 CHAR(2),
	DC05 CHAR(2),
	DC06 CHAR(2),
	DC07 CHAR(2),
	DC08 CHAR(2),
	DC09 CHAR(2),
	DC10 CHAR(2),
	DC11 CHAR(2),
	DC12 CHAR(2),
	DC13 CHAR(2),
	DC14 CHAR(2),
	DC15 CHAR(2),
	DC16 CHAR(2),
	DC17 CHAR(2),
	DC18 CHAR(2),
	DC19 CHAR(2),
	DC20 CHAR(2))


SET NOCOUNT ON

INSERT	@YourTable(
	ID, ProjectName, Currency, Element, SubElements,
	DC01, DC02, DC03, DC04, DC05, DC06, DC07, DC08, DC09, DC10,
	DC11, DC12, DC13, DC14, DC15, DC16, DC17, DC18, DC19, DC20)
VALUES	
	('0001', 'test1', 'GBP', '00', 'N', 
	'??', '??', '??', '??', '??', '??', '??', '??', '??', '??',
	'??', '??', '??', '??', '??', '??', '??', '??', '??', '??'),
	('0002', 'test2', NULL, '00', 'N', 
	'??', '??', '??', '??', '??', '??', '??', '??', '??', '??',
	'??', '??', '??', '??', '??', '??', '??', '??', '??', '??'),
	('0003', 'test3', NULL, '00', 'N', 
	'??', '??', '??', '??', '??', '??', '??', '??', '??', '??',
	'??', '??', '??', '??', '??', '??', '??', '??', '??', '??'),
	('0004', 'test4', 'GBP', '00', 'N', 
	'??', '??', '??', '??', '??', '??', '??', '??', '??', '??',
	'??', '??', '??', '??', '??', '??', '??', '??', '??', '??')
SELECT	ID, ProjectName, Currency, Element, SubElements,
	DC01, DC02, DC03, DC04, DC05, DC06, DC07, DC08, DC09, DC10,
	DC11, DC12, DC13, DC14, DC15, DC16, DC17, DC18, DC19, DC20
FROM	@YourTable

INSERT	@YourTable(ID, ProjectName, Currency, Element, SubElements,
	DC01, DC02, DC03, DC04, DC05, DC06, DC07, DC08, DC09, DC10,
	DC11, DC12, DC13, DC14, DC15, DC16, DC17, DC18, DC19, DC20)
SELECT	RIGHT('000' + CAST(y.ID + ROW_NUMBER() OVER(ORDER BY ProjectName, Element) AS varchar(4)), 4) ID, 
	x.ProjectName, x.Currency, x.Element, SubElements,
	DC01, DC02, DC03, DC04, DC05, DC06, DC07, DC08, DC09, DC10,
	DC11, DC12, DC13, DC14, DC15, DC16, DC17, DC18, DC19, DC20
FROM	(
	SELECT	ID, ProjectName, 'GBP' Currency, '01' Element, SubElements,
		DC01, DC02, DC03, DC04, DC05, DC06, DC07, DC08, DC09, DC10,
		DC11, DC12, DC13, DC14, DC15, DC16, DC17, DC18, DC19, DC20
	FROM	@YourTable
	WHERE	Currency IS NULL 

	UNION ALL
	
	SELECT	ID, ProjectName, 'GBP', '02', SubElements,
		DC01, DC02, DC03, DC04, DC05, DC06, DC07, DC08, DC09, DC10,
		DC11, DC12, DC13, DC14, DC15, DC16, DC17, DC18, DC19, DC20
	FROM	@YourTable
	WHERE	Currency IS NULL) x
	CROSS JOIN (
	SELECT	MAX(CAST(ID AS smallint)) ID
	FROM	@YourTable) y

UPDATE	@YourTable
SET	Currency = 'GPB',
	Subelements = 'Y'
WHERE	Currency IS NULL 

SELECT	ID, ProjectName, Currency, Element, SubElements,
	DC01, DC02, DC03, DC04, DC05, DC06, DC07, DC08, DC09, DC10,
	DC11, DC12, DC13, DC14, DC15, DC16, DC17, DC18, DC19, DC20
FROM	@YourTable
ORDER BY
	ID

Open in new window


And here is the output:

Initial:
ID	ProjectName	Currency	Element	SubElements	DC01	DC02	DC03	DC04	DC05	DC06	DC07	DC08	DC09	DC10	DC11	DC12	DC13	DC14	DC15	DC16	DC17	DC18	DC19	DC20
0001	test1	GBP	00	N 	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??
0002	test2	NULL	00	N 	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??
0003	test3	NULL	00	N 	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??
0004	test4	GBP	00	N 	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??

Open in new window


Final:
ID	ProjectName	Currency	Element	SubElements	DC01	DC02	DC03	DC04	DC05	DC06	DC07	DC08	DC09	DC10	DC11	DC12	DC13	DC14	DC15	DC16	DC17	DC18	DC19	DC20
0001	test1	GBP	00	N 	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??
0002	test2	GPB	00	Y 	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??
0003	test3	GPB	00	Y 	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??
0004	test4	GBP	00	N 	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??
0005	test2	GBP	01	N 	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??
0006	test2	GBP	02	N 	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??
0007	test3	GBP	01	N 	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??
0008	test3	GBP	02	N 	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??	??

Open in new window

0
 

Author Closing Comment

by:supertramp4
Comment Utility
Many thanks to ACperkins, and GED325. I have split the points between you, as both are acceptable solutions.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

743 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now