asked on

help creating SQL Stored Proceedure

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

Kyle Abrahams, PMP

-- 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;

Chris Luttrell

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

Anthony Perkins

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

Anthony Perkins

Never mind that is not correct.

Anthony Perkins

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

Anthony Perkins

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

supertramp4

ASKER

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.

SOLUTION

Kyle Abrahams, PMP

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anthony Perkins

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

supertramp4

ASKER

Many thanks to ACperkins, and GED325. I have split the points between you, as both are acceptable solutions.