Execute stored procedure from view? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

SQL Server Development (2000)

Execute stored procedure from view?

Author

Topic

ropep
Starting Member

8 Posts

Posted - 2009-04-07 : 06:47:13

Hi,

i have a stored procedure in SQL Server which I need to call from Oracle. However I can only use Oracle Gateway for ODBC which doesn't support the execution of Stored procedures, only queries on tables/views.

Is it possible for me to create a view in SQL Server which calls the stored procedure?

The parameters for the stored procedure are userid, startdate, finishdate. I'm thinking of making a hard-coded selection of all users (and so retrieving the userid's), and then create a separate view for certain periods (for start/finishdates)... anybody following me?

Thanks!

Naigewron
Starting Member

6 Posts

Posted - 2009-04-07 : 07:04:56

I didn't really follow what you wanted to achieve, but you can't use a view to retrieve results from a stored procedure, as far as I know. A view can execute functions though, but I'm guessing that won't be sufficient in this case?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-04-07 : 07:23:03

You can't execute sp from views

Can the stored proc be rewritten as a view? You can't reference a temp table or table variable from a view so depending on what your sp does it may not be possible.

Why not post the code of the sp?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-07 : 07:24:54

A view can "execute" stored procedures too, but you can't change the inbound parameters from call to call.
They have to be fixed.

E 12°55'05.63"
N 56°04'39.26"

ropep
Starting Member

8 Posts

Posted - 2009-04-07 : 07:30:41

Peso, that's what i'm looking for. Can the parameters be 'fixed' as in retrieved from another view?

ropep
Starting Member

8 Posts

Posted - 2009-04-07 : 07:45:37

Charlie: here is some code for the stored procedure. As you can see it takes 3 parameters and it returns a selection from a temp table (created in the sp). My problem should be solvable according to Peso right?

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[p_FindDayProgForEmp]
@PersonID INTEGER,
@DateFrom INTEGER,
@DateTill INTEGER
AS

--REMOVED CODE

DECLARE pr_month_cursor CURSOR FORWARD_ONLY
FOR SELECT pr_key, bookdate, cyc_dp, dayprog, flags1 FROM pr_month WHERE persnr = @PersonId AND bookdate >= @DateFrom AND bookdate <= @DateTill ORDER BY bookdate

-- Create a temporary DayProgramForPerson table
CREATE TABLE #tempDayProgramForPerson(
REC_NR INT IDENTITY (1, 1) NOT NULL,
PR_MONTH_ID INT,
PERSNR INT,
BOOKDATE INT,
DAYPROG INT,
IS_PRIORITARY INT)

SET @CurrentDate = @DateFrom

-- REMOVED CODE

-- start parsing through records from PR_MONTH (if any)
OPEN pr_month_cursor

FETCH NEXT FROM pr_month_cursor
INTO @PRKey, @BookDate, @Cyc_DP, @Dayprog, @Flags

--REMOVED CODE

-- get generated info
SELECT * FROM #tempDayProgramForPerson

-- drop the temporary table
DROP TABLE #tempDayProgramForPerson

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-07 : 07:47:08

I see no reason for having CURSOR at all.

E 12°55'05.63"
N 56°04'39.26"

ropep
Starting Member

8 Posts

Posted - 2009-04-07 : 07:50:35

Sorry can't change the proc, it's in our timetracking software....

Can I just use Transact-SQL in the view to call the sp? (Sorry coming from Oracle..)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-04-07 : 08:13:47

Ummm, no, you can't define a view to execute a stored procedure, and you can't pass parameters into it.

What exactly is the need to call this from Oracle? Can you instead push the data from SQL Server to your Oracle server? Is there another Oracle driver you can use that supports procedure execution?

ropep
Starting Member

8 Posts

Posted - 2009-04-07 : 08:28:20

There is an Oracle driver which supports it, but it costs 17500 dollars, about 1/3 of the Oracle Enterprise license :-D.

Pushing data is not an option. Our timetracking software uses MSDE which doesn't have support for designing DTS packages. Transferring data (push/pull) is something I really want to avoid though, I need realtime data, and the data is queried a lot.

But Rob, you're saying that Peso is wrong?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-07 : 08:39:36

[code]EXEC master..sp_addlinkedserver @server = 'loopback', @srvproduct = '', @provider='SQLNCLI', @datasrc = 'phbgpela\sql2008'
GO
CREATE VIEW vwTest
AS

SELECT *
FROM OPENQUERY(loopback, 'EXEC sp_who 7')
GO

SELECT *
FROM vwTest
GO

DROP VIEW vwTest
GO
EXEC master..sp_dropserver @server = 'loopback'
GO[/code]

E 12°55'05.63"
N 56°04'39.26"

ropep
Starting Member

8 Posts

Posted - 2009-04-07 : 08:43:26

I like you Peso :-)

Thx!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-07 : 08:46:40

As you can see, you can have a stored procedure as data source for a view.
But you can't change the inbound parameters!

E 12°55'05.63"
N 56°04'39.26"

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2009-04-07 : 09:22:34

quote:
Originally posted by ropep

There is an Oracle driver which supports it, but it costs 17500 dollars, about 1/3 of the Oracle Enterprise license :-D.

that sounds like a good opportunity for someone to write a bit of code and undercut that ridiculous price.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-04-07 : 12:02:45

quote:
Originally posted by Peso

As you can see, you can have a stored procedure as data source for a view.
But you can't change the inbound parameters!

E 12°55'05.63"
N 56°04'39.26"

grats to you Peso -- I always forget about OPENQUERY. (mostly because it's been disabled on our servers)

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-04-07 : 12:11:47

ropep -- you've taken out all the code from the sp that actually does stuff. As peso said you can probably eliminate the cursor (we'd be able to tell you for sure if you had actually posted the working code). If you can do that then you can probably eliminate the temp table entirely as you don't require an intermediate storage mechanism. if you can do that then you can rewrite the sp as a view in nice static SQL without have to resort to loopback connections!

I know absolutely nothing about OPENQUERY (mostly because our infrastructure people don't like the idea and have forbidden it): resulting in:


OLE DB provider "SQLNCLI" for linked server "loopback" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "loopback" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".

I'm sure that the OPENQUERY method will work but you might have an opportunity here to clean up and optimise your code that you won't often get until something is broken. (if your shop is anything like mine anyway)

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

ropep
Starting Member

8 Posts

Posted - 2009-04-08 : 06:20:06

Thanks for all the thinkings folks! I'm very keen to know if the procedure could be made obsolete. This opens the door for me to go for the generic (free) ODBC driver for Oracle and have a more optimised application! Let me post the entire SP:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[p_FindDayProgForEmp]
@PersonID INTEGER,
@DateFrom INTEGER,
@DateTill INTEGER
AS

DECLARE @PRKey INTEGER
DECLARE @BookDate INTEGER
DECLARE @Cyc_DP INTEGER
DECLARE @Dayprog INTEGER
DECLARE @Flags INTEGER

DECLARE @OrigCyc_DP INTEGER
DECLARE @OrigDayprog INTEGER
DECLARE @OrigFlags INTEGER

DECLARE @CurrentDate INTEGER
DECLARE @TmpDate AS SMALLDATETIME

DECLARE @Cycle INTEGER
DECLARE @StartDate INTEGER
DECLARE @StartDay INTEGER

-- What is in the pr_month for each day
DECLARE pr_month_cursor CURSOR FORWARD_ONLY
FOR SELECT pr_key, bookdate, cyc_dp, dayprog, flags1 FROM pr_month WHERE persnr = @PersonId AND bookdate >= @DateFrom AND bookdate <= @DateTill ORDER BY bookdate

-- Create a temporary DayProgramForPerson table
CREATE TABLE #tempDayProgramForPerson(
REC_NR INT IDENTITY (1, 1) NOT NULL,
PR_MONTH_ID INT,
PERSNR INT,
BOOKDATE INT,
DAYPROG INT,
IS_PRIORITARY INT)

SET @CurrentDate = @DateFrom

-- start parsing through records from PR_MONTH (if any)
OPEN pr_month_cursor

FETCH NEXT FROM pr_month_cursor
INTO @PRKey, @BookDate, @Cyc_DP, @Dayprog, @Flags

WHILE @@FETCH_STATUS = 0
BEGIN
-- Make sure we don't have Null values
SET @OrigCyc_DP = ISNULL(@Cyc_DP, 0)
SET @OrigDayProg = ISNULL(@DayProg, 0)
SET @OrigFlags = ISNULL(@Flags, 0)

WHILE @CurrentDate < @BookDate
BEGIN
-- generate missing days till BookDate
SET @DayProg = 0
SET @Flags = 0
EXEC SP_SearchCycleOnDate @PersonId, @CurrentDate, @StartDate OUT, @StartDay OUT,@Cycle OUT
IF @Cycle > 0
EXEC @DayProg = SP_SearchCycleDayprogram @Cycle, @CurrentDate, @StartDate, @StartDay

INSERT INTO #tempDayProgramForPerson(PR_MONTH_ID, PERSNR, BOOKDATE, DAYPROG, IS_PRIORITARY )
VALUES (0, @PersonID, @CurrentDate, @DayProg, 0)
-- increment Current Date
EXEC sp_AddToDate 1,@CurrentDate OUTPUT
END

-- add the info fom PR_MONTH
IF @OrigFlags & 1 = 0
BEGIN
IF @OrigCyc_DP > 0
SET @OrigDayProg = @OrigCyc_DP

IF @OrigDayProg = 0
BEGIN
EXEC SP_SearchCycleOnDate @PersonId, @CurrentDate, @StartDate OUT, @StartDay OUT,@Cycle OUT
IF @Cycle > 0
EXEC @OrigDayProg = SP_SearchCycleDayprogram @Cycle, @CurrentDate, @StartDate, @StartDay
END
END
INSERT INTO #tempDayProgramForPerson(PR_MONTH_ID, PERSNR, BOOKDATE, DAYPROG, IS_PRIORITARY )
VALUES (@PRKey, @PersonID, @CurrentDate, @OrigDayProg, @OrigFlags)

-- increment Current Date to skip the record from pr_month
EXEC sp_AddToDate 1,@CurrentDate OUTPUT

-- next record from cursor
FETCH NEXT FROM pr_month_cursor
INTO @PRKey, @BookDate, @Cyc_DP, @Dayprog, @Flags

END
CLOSE pr_month_cursor
DEALLOCATE pr_month_cursor
--add the final days

WHILE @CurrentDate <= @DateTill
BEGIN
-- generate missing days till BookDate
SET @DayProg = 0
SET @Flags = 0
EXEC SP_SearchCycleOnDate @PersonId, @CurrentDate, @StartDate OUT, @StartDay OUT,@Cycle OUT
IF @Cycle > 0
EXEC @DayProg = SP_SearchCycleDayprogram @Cycle, @CurrentDate, @StartDate, @StartDay

INSERT INTO #tempDayProgramForPerson(PR_MONTH_ID, PERSNR, BOOKDATE, DAYPROG, IS_PRIORITARY )
VALUES (0, @PersonID, @CurrentDate, @DayProg, 0)
-- increment Current Date
EXEC sp_AddToDate 1,@CurrentDate OUTPUT
END

-- get generated info
SELECT * FROM #tempDayProgramForPerson

-- drop the temporary table
DROP TABLE #tempDayProgramForPerson

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-04-08 : 07:57:18

Can you provide the code for the other procedures it calls, like SP_SearchCycleOnDate, SP_SearchCycleDayprogram, and sp_AddToDate? (that last one sounds like it could be replaced with a simple DateAdd call)

Don't know if this can get turned into a view but can probably get rid of the cursor.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-04-08 : 07:57:29

well... it would be a lot of work

What you've got there is a nested loop that calls further stored procs that set variables. You are then doing inserts (one row at a time) into your temp table.

You'd need to post the code of:

SP_SearchCycleDayprogram
SP_SearchCycleOnDate

to go a little further.

Basically you would need to turn these procedures that operation on 1 article of data into a set based operation. Once that's done then they can be included in your parent code as derived tables and JOINED. After that you can rewrite the loops in a set based way.

It doesn't look like it's going to be quick or easy.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

ropep
Starting Member

8 Posts

Posted - 2009-04-08 : 08:52:08

You really are very helpful i must say, i'm considering becoming a 'SQL-Server guy' :-)

AddToDate is a DateAdd function yes, DateDiff (called in SP i'm posting now) is also simple dateDiff function.

SP_SearchCycleDayp (i'm changing names a little)
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[SP_SearchCycleDayp] @Cyclus INTEGER, @Date INTEGER, @BegDate INTEGER, @StartDay INTEGER
AS

DECLARE @DayProg INTEGER
DECLARE @NrOfDays SMALLINT
DECLARE @DagInCyclus SMALLINT
DECLARE @diff INTEGER

SELECT @NrOfDays = NROFDAYS FROM CYCLIQ WHERE CYCLIQ = @Cyclus
IF @NrOfDays > 0
BEGIN
IF @StartDay < 1
SET @StartDay = 1
EXEC @diff = sp_datediff @BegDate, @date
SET @DagInCyclus = 1 + ((@diff + @StartDay - 1) % @NrOfDays)
IF @DagInCyclus <= 0
SET @DagInCyclus = @DagInCyclus + @NrOfDays
SELECT @DayProg = DAYPROG FROM CYC_DP WHERE CYCLIQ = @Cyclus AND DAYNR = @DagInCyclus
END

IF @DayProg IS NULL
SET @DayProg = 0
RETURN @DayProg

---------------------

SP_SearchCycleOnD(a)(t)(e):

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[SP_SearchCycleOnD]
@PersonID INTEGER,
@Date INTEGER,
@StartDate INTEGER OUT,
@StartDay INTEGER OUT,
@Cycle INTEGER OUT
AS

SET @StartDate=0
SET @StartDay=0
--The profiletype for the cycliq org is 4
EXEC SP_SearchProfileOnDate @PersonID,@Date,4,0,@Cycle OUTPUT,@StartDate OUTPUT,0,@StartDay OUTPUT

----------------------------------

SP_SearchProfileOnD(a)(t)(e):

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[SP_SearchProfileOnD]
-- INPUT
@PersNr INTEGER, -- Person who's Profile to find
@Date INTEGER, -- Find the Person's Profile for this date
@ProfileType INTEGER, -- 1 = CostGroup; 2 = CostProgram; 3 = Person_Rights; ...
-- OUTPUT
@RecID INTEGER OUTPUT, -- Return the RecID of the requested Profile
@ProfileID INTEGER OUTPUT, -- Return the profile id of the requested profile
@StartDate INTEGER OUTPUT, -- Return the Startdate of the requested profile
@EndDate INTEGER OUTPUT, -- Return the last valid date ('99999999' means no End Date)
@DayNumber INTEGER OUTPUT -- Return the daynumber of the requested profile
AS

DECLARE @ISPRIOCOSTPROG INTEGER --If a costprogram is painted, then this flag is set
--Initialize
SET @RecID = 0
SET @ProfileID = 0
SET @StartDate = 0
SET @EndDate = 0
SET @DayNumber = 0

-- costprogram may be painted ! => search table PR_Month first
IF @ProfileType=2
BEGIN
--The prioCostprog is the 7't bit of flags1 column --> so 64
SELECT @ProfileID=COSTCENTERGROUP, @ISPRIOCOSTPROG=FLAGS1 & 64 FROM PR_MONTH WHERE BOOKDATE=@Date AND PERSNR=@PersNr
IF (@ProfileID > 0 AND @ISPRIOCOSTPROG>0)
BEGIN
SET @StartDate = @Date
SET @EndDate = @Date
END
ELSE
SET @ProfileID=0
END

IF @ProfileID=0
BEGIN
SELECT TOP 1 @RecID=REC_NR,@ProfileID=PROFILE,@StartDate=DATEFROM,@DayNumber=DAYNUMBER FROM LNK_CURRIC_PROFILE
WHERE PERSNR=@PersNr AND PROFILETYPE=@ProfileType AND DATEFROM<=@Date
ORDER BY DATEFROM DESC

IF NOT @RecID IS NULL --We found something
BEGIN
SELECT TOP 1 @EndDate=DATEFROM FROM LNK_CURRIC_PROFILE
WHERE PERSNR=@PersNr AND PROFILETYPE=@ProfileType AND DATEFROM>@Date
ORDER BY DATEFROM
IF (@EndDate IS NULL or @EndDate=0)
SET @EndDate = 99999999 --No end date, the period is still running
ELSE
EXECUTE SP_ADDTODATE -1, @EndDate OUTPUT --Found it, now return it one day before
END
--ELSE => we return empty output params
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources