Re: INLINE FUNCTION - Database Discussion Boards

It_tech3-Jun-10 4:18

3-Jun-10 4:18

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

Re: INLINE FUNCTION

J4amieC3-Jun-10 4:39

J4amieC

3-Jun-10 4:39

It_tech wrote:
But keeps coming with error on EXEC Lline...

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

Re: INLINE FUNCTION

It_tech3-Jun-10 5:51

It_tech

3-Jun-10 5:51

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'.

Re: INLINE FUNCTION

J4amieC3-Jun-10 6:06

J4amieC

3-Jun-10 6:06

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.

Re: INLINE FUNCTION

It_tech3-Jun-10 6:26

It_tech

3-Jun-10 6:26

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

Re: INLINE FUNCTION

J4amieC3-Jun-10 6:34

J4amieC

3-Jun-10 6:34

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

Re: INLINE FUNCTION

It_tech3-Jun-10 7:00

It_tech

3-Jun-10 7:00

Thanks.What is the way around then?

Re: INLINE FUNCTION

Mycroft Holmes3-Jun-10 14:20

Mycroft Holmes

3-Jun-10 14:20

Don't use a function!

Never underestimate the power of human stupidity
RAH

Re: INLINE FUNCTION

J4amieC3-Jun-10 21:30

J4amieC

3-Jun-10 21:30

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

Re: INLINE FUNCTION

It_tech3-Jun-10 23:45

It_tech

3-Jun-10 23:45

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

Re: INLINE FUNCTION

J4amieC4-Jun-10 0:02

J4amieC

4-Jun-10 0:02

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.

Re: INLINE FUNCTION

It_tech4-Jun-10 0:35

It_tech

4-Jun-10 0:35

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

Re: INLINE FUNCTION

J4amieC4-Jun-10 0:57

J4amieC

4-Jun-10 0:57

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.

Re: INLINE FUNCTION

Eddy Vluggen4-Jun-10 1:48

Eddy Vluggen

4-Jun-10 1:48

Hi,

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

SQL

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 Smile | :)

I are Troll Suspicious | :suss:

Re: INLINE FUNCTION

It_tech4-Jun-10 2:51

It_tech

4-Jun-10 2:51

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'
)

Re: INLINE FUNCTION [modified]

Eddy Vluggen4-Jun-10 3:27

Eddy Vluggen

4-Jun-10 3:27

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;

SQL

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

I are Troll Suspicious | :suss:

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

SQL QUERY PROBLEM..

mjawadkhatri2-Jun-10 23:41

mjawadkhatri

2-Jun-10 23:41

Hi
Please check my this query and tell me error

select productname,sum(issueqty) QTY from issuance
where issuedate >= '5/1/2010' and issuedate <= '5/31/2010'
and productid= '777' and productid= '778'
group by productname
order by productname

i want to show these two product total issue qty of month may

Re: SQL QUERY PROBLEM..

Eddy Vluggen2-Jun-10 23:59

Eddy Vluggen

2-Jun-10 23:59

mjawadkhatri wrote:
i want to show these two product total issue qty of month may

For all records with the number "777" and "778"? Didn't you want an OR instead of an AND?

I are Troll Suspicious | :suss:

Re: SQL QUERY PROBLEM..

mjawadkhatri3-Jun-10 0:10

mjawadkhatri

3-Jun-10 0:10

yes all record of productid=777 and productid=778

Re: SQL QUERY PROBLEM..

Eddy Vluggen3-Jun-10 0:39

Eddy Vluggen

3-Jun-10 0:39

You want the records that have number "777" or "778". You are asking for the records that have *both* numbers.

The resulting list will have records with both records containing 777 AND 778, but you'll need to ask for the records that contain 777 OR 778 Smile | :)

I are Troll Suspicious | :suss:

Re: SQL QUERY PROBLEM..

Tim Carmichael3-Jun-10 2:38

Tim Carmichael

3-Jun-10 2:38

In high school, to drive home the difference between 'AND' and 'OR' in a logical statement, the teacher asked all of the students in room 201 and 202 to please stand up.

When people stood, he asked if there body was split, thereby allowing them to be in two rooms at the same time.

Re: SQL QUERY PROBLEM..

Luc Pattyn3-Jun-10 3:25

Luc Pattyn

3-Jun-10 3:25

and that is when the class decided to tear down the wall between rooms 201 and 202 I presume.

Laugh | :laugh:

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

I only read formatted code with indentation, so please use PRE tags for code snippets.

I'm not participating in frackin' Q&A, so if you want my opinion, ask away in a real forum (or on my profile page).

Re: SQL QUERY PROBLEM..

Tom Deketelaere3-Jun-10 4:38

Tom Deketelaere

3-Jun-10 4:38

Not to be picky here but wouldn't that just merge the 2 rooms into 1 room and therefor they once again weren't in 201 AND 202 but only in 201 OR 202 (or another number given to the now twice as big a room Smile | :)

)

Re: SQL QUERY PROBLEM..

Luc Pattyn3-Jun-10 4:42

Luc Pattyn

3-Jun-10 4:42

Of course not. Without the wall, they would be in both rooms at the same time. Unless you insist on renumbering everything, or otherwise denormalize the school.

Smile | :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

I only read formatted code with indentation, so please use PRE tags for code snippets.

I'm not participating in frackin' Q&A, so if you want my opinion, ask away in a real forum (or on my profile page).

Re: SQL QUERY PROBLEM..

Tom Deketelaere3-Jun-10 4:45

Tom Deketelaere

3-Jun-10 4:45

Well if you keep the numbering that would mean 201 is one half of the room and 202 another half of the room.

One could argue that in order to be in both rooms at the same time you would have to be standing in the exact middle of the room (where the wall was).

I don't know why but I'm in a picky mood today, must be because of the slow day at work Poke tongue | ;-P

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.