MSSQL Stored Procedure Needs to update columns in table

Hello,

This is new to me and I did some searching in the knowledge base but could not find anything on what I am trying to accomplish.   Not that it doesn't exist I may not have been searching the correct information.

I have a PriceTable (pt) that needs to have three columns, BeginDate, EndDate & PriceGroupName, from the results of this stored procedure, which requires 4 parameters:  dbo.spGetPriceDetail '2015-09-15', '0028', '972201', 0  (the parameters are pt.PriceDate, pt.StoreId, pt.ItemId, & the 0 (zero) is if it is With Card and a 1 for Without Card - which I will know by the PriceTable row).  I am not certain how to write a sql script that would allow me to process a record in the PriceTable --> Pass the necessary information to the store procedure --> return the results to the correct columns in the PriceTable and update that row.

  I am using MS SQL 2014.

Thank you in advance for you time and assistance in this matter.
Thanks,
John
j_heckAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
I am a little confused.

You are trying to update a table from a stored procedure.  What is the stored procedure pulling from?  What is PriceTable supposed to represent?  I don't see the direct correlation between PriceTable and the procedure since spGetPriceDetail doesnt' supply a PriceGroupName.  I feel like we're getting about half the necessary information to come up with a solution.
UnifiedISCommented:
Here's a loose set of steps to follow

start loop (WHILE...)
get and store parameter values in variables (DECLARE variables, SET/SELECT variable =...)
call stored procedure and store results in temp table (INSERT INTO...EXECUTE...)
update table using data from stored procedure that is in temp table (UPDATE PriceTable)
j_heckAuthor Commented:
Hello Brian,

I see the confusion now.  Hopefully this will clear it up.  I have  PriceTable which has a PriceDate, StoreNumber, ItemId, wc (with card) or nc (withoutcard), begindate, enddate, & Price GroupName.  The first four columns are filled in, and the last three BeginDate, EndDate, & PriceGroupName are not.  A stored procedure was written, by another person, that uses the first four columns to send to the stored procedure so that the returned information is BeginDate, EendDate, & PriceGroupName.  The name of the stored procedure and how it is called is dbo.spGetPriceDetail '2015-09-15', '0028', '972201', 0 - the parameters are PriceDate, StoreNumber, ItemId, and wc/nc (wc = 1 & nc  = 0).  The stored procedure will return a BeginDate, EndDate & PriceGroupName that needs to be updated into the associated columns in the PriceTable.

Does this make more sense.  Part of the problem is that a vendor company wrote the stored procedure, someone else created the PriceTable and I am in the middle trying to get data from one to another.  :)

Thanks,
John
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Brian CroweDatabase AdministratorCommented:
Since it is a stored procedure that you are calling the only real solution is to use a cursor to look through each of your records, call the procedure, and update the record with returned values.

Does the stored procedure return the values as OUTPUT parameters or as part of a resultset?
j_heckAuthor Commented:
Hello Brian,

It returns a result set that will consist of 1 row.  Since it returns a result set how do I go about getting the columns from the store procedure into the PriceTable?  I understand doing an UPDATE command is required but would the store procedure be called before the UPDATE command or is there a better way of doing it?

As for doing the While Loop and using a Cursor would I then use the FETCH NEXT command to keep getting the next row in the table?  

Thanks.
Brian CroweDatabase AdministratorCommented:
I am probably handling the wc/nc field wrong since I don't really understand if that is a single column or two or what so you will need to adjust accordingly and I had to guess at the datatypes so adjust those as needed as well.

Please keep in mind that this is air-code without the benefit of the correct schema or sample data...


DECLARE @PriceDate	DATETIME,
	@StoreNumber	VARCHAR(10),
	@ItemID			INT,
	@WithCard		BIT,
	@BeginDate		DATETIME,
	@EndDate		DATETIME,
	@PriceGroupName	VARCHAR(100);

DECLARE crs CURSOR FOR
	SELECT PriceDate, StoreNumber, ItemID, WithCard
	FROM PriceTable;

CREATE TABLE #PriceDetail
(
	BeginDate		DATETIME,
	EndDate			DATETIME,
	PriceGroupName	VARCHAR(100)
)

OPEN crs;

FETCH NEXT FROM crs
INTO @PriceDate, @StoreNumber, @ItemID, @WithCard;

WHILE @@FETCH_STATUS = 0
BEGIN
	INSERT INTO #PriceDetail (BeginDate, EndDate, PriceGroupName)
	EXECUTE dbo.spGetPriceDetail @PriceDate, @StoreNumber, @ItemID, @WithCard;

	SELECT TOP 1 @BeginDate = BeginDate, @EndDate = EndDate, @PriceGroupName = PriceGroupName
	FROM #PriceDetail;

	UPDATE PriceTable
	SET BeginDate = @BeginDate,
		EndDate = @EndDate,
		PriceGroupName = @PriceGroupName
	WHERE PriceDate = @PriceDate
		AND StoreNumber = @StoreNumber
		AND ItemID = @ItemID
		AND WithCard = @WithCard;

	TRUNCATE TABLE #PriceDetail;

	FETCH NEXT FROM crs
	INTO @PriceDate, @StoreNumber, @ItemID, @WithCard;
END

CLOSE crs;
DEALLOCATE crs;

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
j_heck, do you still need help with this question?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.