INLINE FUNCTION
-
Hi all, I have a problem in getting the one below to work. I think i have used the right syntax. But keeps coming with error on EXEC Lline... CREATE FUNCTION Summary (@Month varchar(30),@Year varchar(30),@ShowType varchar(30)) RETURNS TABLE AS RETURN With EXECUTE C_WEB_sp_RevenueSummaryByShow 'April', '2010', '3' Go Thansk for your time
-
Hi all, I have a problem in getting the one below to work. I think i have used the right syntax. But keeps coming with error on EXEC Lline... CREATE FUNCTION Summary (@Month varchar(30),@Year varchar(30),@ShowType varchar(30)) RETURNS TABLE AS RETURN With EXECUTE C_WEB_sp_RevenueSummaryByShow 'April', '2010', '3' Go Thansk for your time
-
It_tech wrote:
But keeps coming with error on EXEC Lline...
Do you think the error message might help us debug your problem?
-
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'.
-
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.
-
Thanks Once again.Its Much appreciated.What is it about extended Stored Procedures?I hope we can execute them in table valued functions.
-
Don't use a function!
Never underestimate the power of human stupidity RAH
-
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
-
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
-
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.
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
-
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
-
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
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
GOSELECT *
INTO #MyTempTable
FROM OPENROWSET(
'SQLNCLI',
'Server=(local);Trusted_Connection=yes;',
'EXEC AdventureWorks.dbo.uspGetEmployeeManagers 1'
)Hope this helps :)
I are Troll :suss:
-
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
GOSELECT *
INTO #MyTempTable
FROM OPENROWSET(
'SQLNCLI',
'Server=(local);Trusted_Connection=yes;',
'EXEC AdventureWorks.dbo.uspGetEmployeeManagers 1'
)Hope this helps :)
I are Troll :suss:
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' )
-
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' )
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