j_heck
asked on
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
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
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)
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)
ASKER
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
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
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?
Does the stored procedure return the values as OUTPUT parameters or as part of a resultset?
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
j_heck, do you still need help with this question?
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.