How can i call the SP using select statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

How can i call the SP using select statement

Author

Topic

micnie_2020
Posting Yak Master

232 Posts

Posted - 2014-02-06 : 04:12:11

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

30421 Posts

Posted - 2014-02-06 : 17:01:59

Why not rewrite the stored procedure as an inline table valued function?

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

micnie_2020
Posting Yak Master

232 Posts

Posted - 2014-02-06 : 20:23:19

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.

micnie_2020
Posting Yak Master

232 Posts

Posted - 2014-02-06 : 20:33:55

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

micnie_2020
Posting Yak Master

232 Posts

Posted - 2014-02-06 : 20:54:00

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.

micnie_2020
Posting Yak Master

232 Posts

Posted - 2014-02-06 : 21:09:04

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.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-02-08 : 11:27:04

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

micnie_2020
Posting Yak Master

232 Posts

Posted - 2014-02-10 : 00:51:05

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources