how to change a stored procedure to eliminate repe

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

.NET Inside SQL Server (2005)

how to change a stored procedure to eliminate repe

Author

Topic

joanne
Starting Member

46 Posts

Posted - 2009-01-02 : 11:35:05

I need your help!
I am a dba, with not much experience like developer.
I want to modify a stored procedure where a part of the same code is repeated 3 times (every time for a different parameter).
CREATE PROCEDURE [RptCtrl]
@startDate DATETIME
, @endDate DATETIME
, @userId INT
, @MngCo varchar(4000)
, @MngDiv varchar(4000)
, @MngDept varchar(4000)
, @orderByCode INT
, @ignoreDate INT
AS
BEGIN
…..………..
…………..
…….

DECLARE @idoc INT;
EXEC sp_xml_preparedocument @idoc OUTPUT, @MngCo;
SELECT I AS CO_NO
INTO #MngCo
FROM OPENXML (@idoc, '/R/C',1) WITH (I INT)
EXEC sp_xml_removedocument @idoc;

EXEC sp_xml_preparedocument @idoc OUTPUT, @MngDiv;
SELECT I AS DIV_NO
INTO #MngDiv
FROM OPENXML (@idoc, '/R/C',1) WITH (I INT)
EXEC sp_xml_removedocument @idoc;

EXEC sp_xml_preparedocument @idoc OUTPUT, @MngDept;
SELECT I AS DEPT_NO
INTO #MngDept
FROM OPENXML (@idoc, '/R/C',1) WITH (I INT)
EXEC sp_xml_removedocument @idoc;
….
…..

The parameters used could be like :

@MngCo = '<R></R>'
@MngDiv = '<R><C I="323"/></R>'
@MngDept = '<R></R>'
It’s here a possibility to change the stored procedure to not repeat this part of code?
I tried to create this udf.

Create function udf_xml(@parm_xml varchar(4000))
Returns @retFindReports TABLE
(
-- columns returned by the function
out_int int
)
as
begin
declare @iDoc int

Exec sp_xml_preparedocument @iDoc OUTPUT, @parm_xml
INSERT @retFindReports
SELECT I as out_int FROM OPENXML(@idoc, '/R/C',1)
WITH (I INT)
Exec sp_xml_removedocument @iDoc
return
end
GO

When I test my function (SELECT * from dbo.udf_xml('<R><C I="323"/></R>'))
I have this error message:

Only functions and extended stored procedures can be executed from within a function.
I suppose that I can not use sp_xml_preparedocument/sp_xml_removedocument in UDF

Do you have any other solution for my stored procedcure?

Thanks in advance for your help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-02 : 11:38:46

Are you using sql 2005?

joanne
Starting Member

46 Posts

Posted - 2009-01-02 : 19:24:48

yes i use sql server 2005. thanks for your answer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources