September 16, 2009 at 7:32 am
When using a Select From (Update, delete) you may use a table or view as part of the From statement.
Can we use a Stored Procedure?
If so can we use also pass parameters as well?
Thank you,
September 16, 2009 at 7:35 am
I dont think so, but if u find a way i would be delighted to know.
U could do a INSERT INTO TableName EXEC ProcedureName
-----------------
CREATE TABLE #Temp(ID int)
CREATE PROC Test1
AS
SELECT 1
INSERT INTO #Temp
EXEC Test1
September 16, 2009 at 7:39 am
I am very new at this and I honestly have no idea what the commands you show will accomplish.
September 16, 2009 at 7:46 am
My apologies, i may have read the question incorrectly. I thought u wanted an "insert statement - exec proc" method.
September 16, 2009 at 7:50 am
So if i understand correctly u correctly u want to know if the results of an update statement can be used as the from clause in a select??
September 16, 2009 at 8:03 am
Sorry, I'm just very new to this and still learning SQL and all of its related commands.
Here is what I have and would like.
I currently have a view with a where statement selecting a specific division.
I have a stored procedure that does insert these records (from the view i.e. "From vw_blah_blah) into a table, etc...
However now I need to select different divisions.
I can build multiple views or possibly find a way to change the where part of the from statement within the insert statement.
September 16, 2009 at 8:21 am
There's a ugly hack for this, but you must be ABSOLUTELY sure the procedure doesn't invole any kind of data modification. In other words, the procedure must contain SELECT statements and nothing more.
Steps for the ugly hack:
1) Create a linked server pointing to the server itself and call it, for instance, 'SELF'.
2) Select using OPENQUERY:
SELECT *
FROM OPENQUERY(SELF,'EXEC myDatabase.dbo.MyStoredProcedure')
Be warned that this UGLY UGLY UGLY hack will skip any data modification (UPDATE, DELETE, INSERT) and DDL statements (CREATE/DROP/ALTER object) in the stored procedure without raising an error.
Use at your own risk.
Regards
Gianluca
-- Gianluca Sartori
September 16, 2009 at 8:36 am
How about this instead?
P.S. for some reason it doesn't work?????????
It keeps saying I have an error near the last ")"
Insert Into dbo.tbl_PosPay_xfer_File_Test
(
ACCTNUMBER
, ITEMNUMBER
, AMT
, ITEMTYPECODE
, PAYEE
, REFERENCENUMBER
, Date_xfer
)
Select
ACCTNUMBER
, ITEMNUMBER
, AMT
, ITEMTYPECODE
, PAYEE
, REFERENCENUMBER
, GetDate() As Date_xfer
From
(
SELECT
ISNULL(Accnt_A, 0) AS ACCTNUMBER
, CHK_NO AS ITEMNUMBER
, CHK_AMT AS AMT
, CHK_VOID AS ITEMTYPECODE
, PAYEE
, REFERENCE_NO AS REFERENCENUMBER
FROM
OPENQUERY
(
ALPHA,
'
Select
Case
When LENGTH(TRIM(fMst.Accnt_A)) = 0 THEN 0
Else fMst.Accnt_A
End
As Accnt_A
, 0 As Accnt_B
, fPP.Chk_No As Chk_No
, fPP.Chk_Date As Chk_Date
, Case
When fPP.PVDCD = ''Y'' AND fPos_Pay.Chk_Amt = 0 then 1
Else Double(fPP.Chk_Amt)
End
As Chk_Amt
, Case
When fPP.Chk_Void = ''N'' Then 1
When fPP.Chk_Void = ''Y'' Then 3
Else 999
End
As Chk_Void
, Trim(fPos_Pay.Payee) As Payee
, fPos_Pay.Reference_No As Reference_No
From
APTPOS As fPP
Left Outer Join
APTCKB As fMst
On
fPP.CO_NO = fMst.CO_NO
And fPP.DIV_NO = fBMst.DIV_NO
And fPP.GL_ACCNT = fBMst.GL_ACCNT
Where
(fPos_Pay.CO_NO = 1)
Union All
Select
Case
When LENGTH(TRIM(fMst.Accnt_A)) = 0 THEN 0
Else fBnk_Mst.Accnt_A
End
As Accnt_A
, fMst.Accnt_B As Accnt_B
, fPP.Chk_No As Chk_No
, fPP.Chk_Date As Chk_Date
, Double(fPP.Chk_Amt) As Chk_Amt
, Case
When fPP.Chk_Void = ''N'' Then 1
Else 3
End
As Chk_Void
, Trim(fPP.Payee) As Payee
, fPP.Reference_No As Reference_No
From
fPP
Left Outer Join fMst
On
fPos_Pay.CO_NO = fBnk_Mst.CO_NO
And fPos_Pay.DIV_NO = fBnk_Mst.DIV_NO
And fPos_Pay.GL_ACCNT = fBnk_Mst.GL_ACCNT
Where
(fPP.CO_NO = 1)
And
(
(fPP.Chk_Void = ''Y'')
Or
(fPP.Chk_Amt > 0)
And (fPP.Chk_Void = ''N'')
)
Order By
4, 3
'
)
As File
)
P.S. If I run the select statment (that is within the From) by itself it works fine.
If I run the Insert statement with the a view as the from it works fine.
All I am trying to do here is place the 'view' in the From statement.
If that works. Then I can build a Stored Procedure (yeah) that can select (change) the Company based on an input parameter.
September 16, 2009 at 8:46 am
Place an alias after the last closing parenthesis:
SELECT whatever
FROM (
... lots of code here
) AS src_query
-- Gianluca Sartori
September 16, 2009 at 8:49 am
Fantastic!!!!
On to the next step.
Thank you,
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply