June 28, 2013 at 7:55 am
I was able to finally develop this SPROC that runs fine and returns the results but i would like to add an INSERT INTO clause that will load the values into a table that i could use for additional querying and joins.
Thoughts?
DECLARE @Start_Date DATETIME = NULL
DECLARE @Part_Type_MP VARCHAR(1000) = ''
SET @Part_Type_MP = ',' + @Part_Type_MP + ','
SET @Start_Date = ISNULL(@Start_Date, DATEADD(yy, 50, GETDATE()))
SET @Start_Date = dateadd(ms,-3,dateadd(day,1,DATEADD(dd, DATEDIFF(dd,0,@Start_Date), 0)))
DECLARE
@sqlquery NVARCHAR(MAX),
@finalQuery NVARCHAR(MAX),
@q CHAR(1)=''''
SET @sqlquery = N'SELECT ' +
'PLK.Part_no, ' +
'PLK.line_item_key, ' +
'PLK.unit_price ' +
'FROM ' +
'(SELECT p.part_no, pli.unit_price, MAX(PLI.Line_Item_Key) AS [Line_Item_Key] ' +
'FROM Purchasing_v_Line_Item_e AS PLI ' +
'JOIN Part_v_Part_e AS P ' +
'ON p.plexus_customer_no = pli.plexus_customer_no ' +
'AND p.part_key = pli.part_key ' +
'WHERE pli.add_date <= ' + @q + @q + CONVERT(VARCHAR(30), @Start_Date,120)+ @q + @q +
' AND (' + @q + @q + @Part_Type_MP + @q + @q +
' = '''',,'''' OR (CHARINDEX('''','''' + CAST(P.Part_Type as VARCHAR(50)) + '''','''', ' +
@q + @q + @Part_Type_MP + @q + @q + ') >0))' +
'GROUP by p.part_no, pli.unit_price ' +
') AS PLK'
-- SET @finalQuery = N'INSERT INTO dbo.tblActualPrice ' + CHAR(10)
SET @finalQuery = N'SELECT ' +
'part_no AS Part, ' +
'unit_price AS ActualCost ' +
'FROM OPENQUERY (PLEXREPORTSERVER, ''' + @sqlquery + ''')'
EXEC (@finalQuery)
June 28, 2013 at 8:00 am
as long as you declare a temp table before the EXEC command, any other queries would have that table in scope, and can insert into it (same for permanent tables)
so i think it's as easy as my edits below:
tstagliano (6/28/2013)
I was able to finally develop this SPROC that runs fine and returns the results but i would like to add an INSERT INTO clause that will load the values into a table that i could use for additional querying and joins.Thoughts?
DECLARE @Start_Date DATETIME = NULL
DECLARE @Part_Type_MP VARCHAR(1000) = ''
SET @Part_Type_MP = ',' + @Part_Type_MP + ','
SET @Start_Date = ISNULL(@Start_Date, DATEADD(yy, 50, GETDATE()))
SET @Start_Date = dateadd(ms,-3,dateadd(day,1,DATEADD(dd, DATEDIFF(dd,0,@Start_Date), 0)))
DECLARE
@sqlquery NVARCHAR(MAX),
@finalQuery NVARCHAR(MAX),
@q CHAR(1)=''''
SET @sqlquery = N'SELECT ' +
'PLK.Part_no, ' +
'PLK.line_item_key, ' +
'PLK.unit_price ' +
'FROM ' +
'(SELECT p.part_no, pli.unit_price, MAX(PLI.Line_Item_Key) AS [Line_Item_Key] ' +
'FROM Purchasing_v_Line_Item_e AS PLI ' +
'JOIN Part_v_Part_e AS P ' +
'ON p.plexus_customer_no = pli.plexus_customer_no ' +
'AND p.part_key = pli.part_key ' +
'WHERE pli.add_date <= ' + @q + @q + CONVERT(VARCHAR(30), @Start_Date,120)+ @q + @q +
' AND (' + @q + @q + @Part_Type_MP + @q + @q +
' = '''',,'''' OR (CHARINDEX('''','''' + CAST(P.Part_Type as VARCHAR(50)) + '''','''', ' +
@q + @q + @Part_Type_MP + @q + @q + ') >0))' +
'GROUP by p.part_no, pli.unit_price ' +
') AS PLK'
-- SET @finalQuery = N'INSERT INTO dbo.tblActualPrice ' + CHAR(10)
CREATE TABLE #Results(ResultsId int identity(1,1) NOT NULL PRIMARY KEY,Part varchar(50),ActualCost money )
SET @finalQuery = N'INSERT INTO #Results (Part,ActualCost) SELECT ' +
'part_no AS Part, ' +
'unit_price AS ActualCost ' +
'FROM OPENQUERY (PLEXREPORTSERVER, ''' + @sqlquery + ''')'
EXEC (@finalQuery)
Lowell
June 28, 2013 at 8:06 am
BE VERY CAREFUL HERE, from what you posted I suspect that @Start_Date DATETIME and @Part_Type_MP are inbound parameters to your stored proc. You have created a huge hole here that is ripe for sql injection. You allow the user to pass you a string and then you execute it. This is the classic scenario for sql injection. Consider what would happen if somebody passed "'); drop table Purchasing_v_Line_Item_e;--". It would throw a syntax error for the first statement, then drop your purchasing table.
You should parameterize your query and use sp_executesql instead of a simple exec.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 28, 2013 at 8:07 am
Thanks for the information but i dont what the table to be a temporary table in the tempdb database. I need it to appear in the ConstarOLAP_PROPHIX_FactDB where the SPROC is running
June 28, 2013 at 8:12 am
tstagliano (6/28/2013)
Thanks for the information but i dont what the table to be a temporary table in the tempdb database. I need it to appear in the ConstarOLAP_PROPHIX_FactDB where the SPROC is running
The same logic works. Just create a permanent table and insert into it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 28, 2013 at 8:13 am
tstagliano (6/28/2013)
Thanks for the information but i dont what the table to be a temporary table in the tempdb database. I need it to appear in the ConstarOLAP_PROPHIX_FactDB where the SPROC is running
I specifically mentioned you could use permanent tables, but it might not have been clear enough.
Which target able you use is up to you, whether it's a temporary table a permanent table that already exists, my snippet was only to demonstrate how to insert the results of the openquery data into a table so it can be used in other queries.
Lowell
June 28, 2013 at 8:20 am
worked perfect. thank you
July 8, 2013 at 11:18 am
One last piece of advice i need. i am able to run this for the first time, but i need to delete the dbo.tblPlexActualPrice table everytime the SPROC runs so i can be re-created. I tried to add a DELETE FROM and INSERT INTO statement but it error out on the syntax where is creates the table.
USE [ConstarOLAP_PROPHIX_FactDb]
GO
/****** Object: StoredProcedure [dbo].[ActualPurchasePriceExport] Script Date: 07/08/2013 13:16:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =======================================================
-- Author:Tom Stagliano, Constar
-- Create date: June 25, 2013
-- Description:Prophix Actual Purchase Price Export SPROC
-- =======================================================
ALTER PROCEDURE [dbo].[ActualPurchasePriceExport]
AS
BEGIN
DECLARE @Start_Date DATETIME = NULL
DECLARE @Part_Type_MP VARCHAR(1000) = ''
SET @Part_Type_MP = ',' + @Part_Type_MP + ','
SET @Start_Date = ISNULL(@Start_Date, DATEADD(yy, 50, GETDATE()))
SET @Start_Date = dateadd(ms,-3,dateadd(day,1,DATEADD(dd, DATEDIFF(dd,0,@Start_Date), 0)))
DECLARE
@sqlquery NVARCHAR(MAX),
@finalQuery NVARCHAR(MAX),
@q CHAR(1)=''''
SET @sqlquery = N'SELECT ' +
'PLK.Part_no, ' +
'PLK.line_item_key, ' +
'PLK.unit_price ' +
'FROM ' +
'(SELECT p.part_no, pli.unit_price, MAX(PLI.Line_Item_Key) AS [Line_Item_Key] ' +
'FROM Purchasing_v_Line_Item_e AS PLI ' +
'JOIN Part_v_Part_e AS P ' +
'ON p.plexus_customer_no = pli.plexus_customer_no ' +
'AND p.part_key = pli.part_key ' +
'WHERE pli.add_date <= ' + @q + @q + CONVERT(VARCHAR(30), @Start_Date,120)+ @q + @q +
' AND (' + @q + @q + @Part_Type_MP + @q + @q +
' = '''',,'''' OR (CHARINDEX('''','''' + CAST(P.Part_Type as VARCHAR(50)) + '''','''', ' +
@q + @q + @Part_Type_MP + @q + @q + ') >0))' +
'GROUP by p.part_no, pli.unit_price ' +
') AS PLK'
-- SET @finalQuery = N'INSERT INTO dbo.tblActualPrice ' + CHAR(10)
CREATE TABLE dbo.tblPlexActualPrice(ResultsID int identity(1,1) NOT NULL PRIMARY KEY, Part varchar(50), ActualCost money)
SET @finalQuery = N'INSERT INTO dbo.tblPlexActualPrice (Part,ActualCost) SELECT ' +
'part_no AS Part, ' +
'unit_price AS ActualCost ' +
'FROM OPENQUERY (PLEXREPORTSERVER, ''' + @sqlquery + ''')'
EXEC (@finalQuery)
END
July 8, 2013 at 11:34 am
tstagliano (7/8/2013)
One last piece of advice i need. i am able to run this for the first time, but i need to delete the dbo.tblPlexActualPrice table everytime the SPROC runs so i can be re-created. I tried to add a DELETE FROM and INSERT INTO statement but it error out on the syntax where is creates the table.
Now you are confusing me; if the table gets data inserted into it, and then you want to destroy the table, what is the procedure supposed to actually do then?
if it creates and destroys the data, it's really doing nothing; maybe you left out some other code? chances are the table needs to exist BEFORE the procedure runs, and so the procedure should not be creating anything...just inserting into your table; i think you might just have vestiges of a copy/paste from my first examples?
if you really need to destroy the table, use a temp table instead.
Create and insert into #tblPlexActualPrice in this case;
the usage of a temp table, instead of a permanent table, allows for full concurrency, meaning two or hundreds of people can call the same procedure at the same time, without one of them pausing or crashing.
a temp table, created in a procedure, is automatically destroyed when it goes out of scope(the procedure ends). a temp table with that name is unique to the session, so 100 people can create their own version of that table, with the same name, even with different schemas and data, than any of the other sessions.
Lowell
July 8, 2013 at 1:51 pm
I recommend taking a step back and trying to analyze your solution a little more in depth.
From what I can tell, based on your DB name, ConstarOLAP_PROPHIX_FactDb, you are creating some type of data warehouse. My hunch is this is a proc you want to run nightly and refresh a table in this DB. Presumably some reports down the line use this table for some calculations and it changes everyday.
To answer your specific question, the proc is attempting to create the table every time its run, you can't have multiple objects with the same name.
Syntactically, to achieve your solution, you would have to execute a drop table command prior to the create table command in your proc.
drop table tblPlexActualPrice
or you can create the table outside the proc and issue a truncate table command prior to your insert.
truncate table tblPlexActualPrice
I do not advocate either of the above, but it would work. A better solution would be to create and populate your tblPlexActualPrice table with pricing data constrained by a date value, to give you what actual pricing was on any given day. Your procedure should then only run inserts of daily changes to this table to keep it current. Any reports or queries will need to be constrained by the date value.
It would help us as well if you could tell us what you are ultimately trying to accomplish. Often times we get lost in the search for how to make something work vs. what is a better way...
July 9, 2013 at 5:56 am
what we are utilmately trying to do is run this procedure on a daily or weekly basis to pull in the actual price of our material to load into a reporting system. Once this table is created, we then import this data into a more permanent dimensional table that holds all the facts for a dimensional cube. So when this SPROC runs, it would just need to clear what is in the table and refresh with updated data.
July 9, 2013 at 6:01 am
tstagliano (7/9/2013)
what we are utilmately trying to do is run this procedure on a daily or weekly basis to pull in the actual price of our material to load into a reporting system. Once this table is created, we then import this data into a more permanent dimensional table that holds all the facts for a dimensional cube. So when this SPROC runs, it would just need to clear what is in the table and refresh with updated data.
in that case, it seems like the table must exist, but the sproc needs to DELETE FROM dbo.tblPlexActualPrice or TRUNCATE TABLE dbo.tblPlexActualPrice, instead of than destroy and create the table?
Lowell
July 9, 2013 at 6:04 am
that is correct. delete from table and then reinsert.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply