SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 How can i call the SP using select statement
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

micnie_2020
Posting Yak Master

Malaysia
228 Posts

Posted - 02/06/2014 :  04:12:11  Show Profile  Reply with Quote
Hi All,

I have SP:-

Alter PROCEDURE getPartialOrderTotalQty
@POID int,
@POID2 int
--exec getPartialOrderTotalQty 18,18
AS

SET NOCOUNT ON
DECLARE @iPOItemID int
DECLARE @iRef int
IF NOT EXISTS( SELECT 1 FROM sysobjects WHERE NAME = 'ReferralOutput' )
BEGIN
CREATE TABLE ReferralOutput( ID [int] IDENTITY(1,1), Ref int, DNBOItemID int, POItemID int )
END
SELECT @iPOItemID = POItemID, @iRef=@POID2 FROM tblPOItems where DNBOItemID = @POID



IF @iPOItemID IS NOT NULL
BEGIN
INSERT INTO ReferralOutput SELECT @iRef, @POID, @iPOItemID
EXECUTE getPartialOrderTotalQty @iPOItemID,@iRef
END
ELSE
BEGIN
select bal=max(p.qty)-sum(i.qty) from tblDNItems i
inner join(
select distinct Ref,DNBOItemID from ReferralOutput
)b on i.POitemID=b.DNBOItemID
inner join tblPOitems p on p.POItemID=b.ref
group by ref
END

GO


I wanted to get the val as below, but failed. Please advise:-

select
POID,
POItemID,
val=(exec getPartialOrderTotalQty POItemID,POItemID)
from tblPOItems where DNBOItemID is null

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 02/06/2014 :  17:01:59  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Why not rewrite the stored procedure as an inline table valued function?



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

micnie_2020
Posting Yak Master

Malaysia
228 Posts

Posted - 02/06/2014 :  20:23:19  Show Profile  Reply with Quote
I tried this way, but still failed:-

CREATE function [dbo].[GetValue](@POID INT, @POID2 INT)
RETURNS @Output table (bal int)
as
BEGIN
Declare @r int
Insert into @Output Exec getPartialOrderTotalQty @POID,@POID2
select * from @Output
END


Error showing:-
Msg 443, Level 16, State 14, Procedure GetValue, Line 6
Invalid use of a side-effecting operator 'INSERT EXEC' within a function.
Msg 444, Level 16, State 2, Procedure GetValue, Line 7
Select statements included within a function cannot return data to a client.
Msg 455, Level 16, State 2, Procedure GetValue, Line 7
The last statement included within a function must be a return statement.
Go to Top of Page

micnie_2020
Posting Yak Master

Malaysia
228 Posts

Posted - 02/06/2014 :  20:33:55  Show Profile  Reply with Quote
I TRIED THIS ALSO FAILED:-

CREATE function [dbo].[GetValue](@POID INT, @POID2 INT)
RETURNS @Output TABLE (bal int)
as
BEGIN
SELECT *
INTO @Output
FROM OPENROWSET('SQLNCLI',
'Server=XXXXX;Trusted_Connection=yes;',
'SET FMTONLY OFF;exec XXX.DBO.getPartialOrderTotalQty 18,18')

END
Go to Top of Page

micnie_2020
Posting Yak Master

Malaysia
228 Posts

Posted - 02/06/2014 :  20:54:00  Show Profile  Reply with Quote
I manage to get the solution by hardcoded parameter. How can I past the parameter @POID & @POID2 into the sp?


aLTER function [dbo].[GetValue](@POID INT, @POID2 INT)
--SELECT * FROM DBO.GETVALUE(8,8)
RETURNS @Output TABLE (bal int) as
BEGIN
INSERT INTO @Output
select * FROM OPENROWSET('SQLNCLI',
'Server=XXXXX;Trusted_Connection=yes;',
'SET FMTONLY OFF;exec XXXX.DBO.getPartialOrderTotalQty 8,8')
RETURN
END

Please advise.

Thank you.
Go to Top of Page

micnie_2020
Posting Yak Master

Malaysia
228 Posts

Posted - 02/06/2014 :  21:09:04  Show Profile  Reply with Quote
I tried this:-
aLTER function [dbo].[GetValue](@POID INT, @POID2 INT)
--SELECT * FROM DBO.GETVALUE(8,8)
RETURNS @Output TABLE (bal int) as
BEGIN
DECLARE @sql nvarchar(MAX)
SET @sql = '
INSERT INTO @Output
select * FROM OPENROWSET(''SQLNCLI'',
''Server=xxxx;Trusted_Connection=yes;'',
''SET FMTONLY OFF;exec xxx.DBO.getPartialOrderTotalQty cast('+@POID+' as int), cast('+@POID2+' as int))'
EXEC sp_executesql @sql
RETURN
END


But I got error:-
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '
INSERT INTO @Output
select * FROM OPENROWSET('SQLNCLI',
'Server=xxxxx;Trusted_Connection=yes;',
'SET FMTONLY OFF;exec xxx.DBO.getPartialOrderTotalQty cast(' to data type int.

Please advise.

Thank you.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 02/08/2014 :  11:27:04  Show Profile  Visit SwePeso's Homepage  Reply with Quote
I mean that the CODE within the stored procedure should be in a function instead, if possible.


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

micnie_2020
Posting Yak Master

Malaysia
228 Posts

Posted - 02/10/2014 :  00:51:05  Show Profile  Reply with Quote
I get stuck if i used function & my code having execute again as below:

Alter PROCEDURE getPartialOrderTotalQty
@POID int,
@POID2 int
--exec getPartialOrderTotalQty 18,18
AS

SET NOCOUNT ON
DECLARE @iPOItemID int
DECLARE @iRef int
IF NOT EXISTS( SELECT 1 FROM sysobjects WHERE NAME = 'ReferralOutput' )
BEGIN
CREATE TABLE ReferralOutput( ID [int] IDENTITY(1,1), Ref int, DNBOItemID int, POItemID int )
END
SELECT @iPOItemID = POItemID, @iRef=@POID2 FROM tblPOItems where DNBOItemID = @POID



IF @iPOItemID IS NOT NULL
BEGIN
INSERT INTO ReferralOutput SELECT @iRef, @POID, @iPOItemID
EXECUTE getPartialOrderTotalQty @iPOItemID,@iRef <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< Re-Execute again
END
ELSE
BEGIN
select bal=max(p.qty)-sum(i.qty) from tblDNItems i
inner join(
select distinct Ref,DNBOItemID from ReferralOutput
)b on i.POitemID=b.DNBOItemID
inner join tblPOitems p on p.POItemID=b.ref
group by ref
END

GO
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000