INLINE FUNCTION

[J4amieC]

It_tech wrote:

But keeps coming with error on EXEC Lline...

Do you think the error message might help us debug your problem?

[It_tech]

Many thanks for your help. Here is the error message: Msg 156, Level 15, State 1, Procedure Summary, Line 6 Incorrect syntax near the keyword 'EXECUTE'.

[J4amieC]

The error message is somewhat descriptive. The main problem you're having is that you wont be able to return the result of a stored procedure call from a function. Its just not possible in SQLServer.

[It_tech]

Thanks Once again.Its Much appreciated.What is it about extended Stored Procedures?I hope we can execute them in table valued functions.

[J4amieC]

As far as I know, you cant execute any SP's in a Function.

[It_tech]

Thanks.What is the way around then?

[Mycroft Holmes]

Don't use a function!

Never underestimate the power of human stupidity RAH

[J4amieC]

Without knowing what you're trying to achieve, I cant answer that.

[It_tech]

Hi There, I tried changing it to a Stored procedure but still have a problem ALTER PROCEDURE [dbo].[sp_LiveRevenueGraph] -- Add the parameters for the stored procedure here @ShowType NVARCHAR(20), @Statistic NVARCHAR(20) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; DECLARE @data TABLE ([Week] NVARCHAR(120), Sunday DECIMAL(12,5), Monday DECIMAL(12,5), Tuesday DECIMAL(12,5), Wednesday DECIMAL(12,5), Thursday DECIMAL(12,5), Friday DECIMAL(12,5), Saturday DECIMAL(12,5), SubTotal DECIMAL(12,5)) -- Insert statements for procedure here -- INSERT INTO @data EXECUTE C_WEB_sp_RevenueAchievedByShow 0, @ShowType; END Error : --INSERT EXEC statement cannot be nested Please advice. Thanks

[J4amieC]

This is the answer you're looking for: http://stackoverflow.com/questions/653714/how-to-select-into-temp-table-from-stored-procedure[^] Google is your friend for this sort of question. Learn to use it.

[It_tech]

Thanks very much for your help. I have changed it to the following But seems to have problem with My server Name when using Open Query Declare @ServName nvarchar(50) set @ServName= (select @@SERVERNAME) select @ServName SELECT * INTO #data FROM OPENQUERY(@ServName, 'EXEc C_WEB_sp_RevenueAchievedByShow 0, @ShowType') Error : Msg 102, Level 15, State 1, Procedure sp_LiveRevenueGraph, Line 22 Incorrect syntax near '@ServName

[J4amieC]

There is no possible way for me to debug that. I have no access to your database, no knowledge of what you're doing. I think you need to spend some time debugging your problem yourself.

[Lost User]

Hi, Just tried it, the code below works for me on the AdventureWorks database;

sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

SELECT *
INTO #MyTempTable
FROM OPENROWSET(
'SQLNCLI',
'Server=(local);Trusted_Connection=yes;',
'EXEC AdventureWorks.dbo.uspGetEmployeeManagers 1'
)

Hope this helps :)

I are Troll :suss:

[It_tech]

Many thanks for your help. I have the following Error : Msg 7403, Level 16, State 1, Line 2 The OLE DB provider "PC-NAME\SqlDev" has not been registered. Here is how i changed it on my Development : sp_configure 'Show Advanced Options', 1 GO RECONFIGURE GO sp_configure 'Ad Hoc Distributed Queries', 1 GO RECONFIGURE GO SELECT * INTO #MyTempTable FROM OPENROWSET( 'Kibrom\SqlDev', 'Server=(local);Trusted_Connection=yes;', 'Exec [dbo].[C_WEB_sp_RevenueAchievedByShow] 1,2' )

[Lost User]

Nice :) --edit The first argument should be a provider; the name of the server has already been provided as "(local)" - this gets replaced with "Kibrom\SqlDev". Something like below should work;

SELECT *
INTO #MyTempTable
FROM OPENROWSET(
'SQLNCLI',
'Server=(local);Trusted_Connection=yes;',
'Exec [dbo].[C_WEB_sp_RevenueAchievedByShow] 1,2'
)

I are Troll :suss:

modified on Friday, June 4, 2010 2:35 PM