how to change a stored procedure to eliminate repeted code

[ersatz]

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!

 

[gmmastros]

Is the repeated code a problem? Why do you want to remove it?

The only way that I can think of to remove some of the repeated code would be to combine your 3 XML fragments in to one. I doubt that would help, though.

If I was a betting man, I would guess that you are having performance problems with this stored procedure, and you (incorrectly) think it's because of the repeated code. If I'm right (about the performance issues), I suggest you post the rest of the code. We can probably help you speed it up a bit.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ersatz]

Hi George,

For the moment I don't have any perf problem because the stored procedure is not yet in prod environment. I want to move this code in an external storproc or function because the same code is used by many procedures and of coarse, I am afraid that later this will be a performance problem.

Many tanks for your answer!

I forgot to mention that I use sql server 2005.

 

[gmmastros]

I'd almost like to say, "Don't worry about it". But I hesitate, because I don't know anything about your system. I suspect that the repeated code will execute quickly, especially if the XML code fragments are relatively small.

Of course, if this is in a highly transactional system, then even the slightest performance problem can add up to something huge (when there are 1000's of users connected).

It appears as though you want to be able to pass a list of integers in to this stored procedure. Each of the 3 parameters appears to be XML so that you can pass multiple ID's for each parameter. If this is true, you may want to compare the performance of this method (XML) with passing a comma delimited list of values. This, of course, assumes that each parameter will actually store a list of values (not multiple lists of values). If so, you may want to look in to some of the following FAQ's, each of which contains a split function.

faq183-3979
faq183-5207
faq183-6684

You should compare the performance between the XML method and all/several of the split methods. Of course, if you decide to use the split method, you'll need to modify the way to pass your parameters in to the stored procedure. You may even want to test the performance of creating the XML in your front end versus creating a simple comma delimited list.

If this were my project, I would probably leans towards the comma delimited list. The only time I use XML for a parameter is when I have multiple attributes for each row that I want to pass in. Think about a list of bus stops along a route. Each stop has a location, number of passengers, and driving directions. I pass in X number of stops, with a dozen attributes for each stop. This saves me from making a database hit for each stop, ultimately making the 'save route' functionality faster.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ersatz]

Wow.

I really appreciate it.

The developer wants to use this stor proc to create reports.

To create the reports, we need information about co, dept and div. So, he takes parameters from the web pages like xml and we can have one value or many values for each parameter (div, dept or co).

You told me that I can use a comma delimited list of values. I my mind was that it is not a good idea to use IN(….) and I must avoid it, if possible (performance problem) .

There is my procedure. What I want to know is if I can change the code between -- Xml var and -- Xml var to not repeat it many times and if your example is a solution for my case.

Thanks again for your response and all these links.

CREATE PROCEDURE [RptTst]
@startDate DATETIME
, @endDate DATETIME
, @userId INT
, @MngCo varchar(4000)
, @MngDiv varchar(4000)
, @MngDept varchar(4000)
, @orderByCode INT
, @ignoreDate INT
AS
BEGIN
SET @startDate = Convert( Datetime, Convert(Varchar(12), @startDate, 101) )
SET @endDate = Convert( Datetime, Convert(Varchar(12), @endDate, 101) )

-- Xml var
DECLARE @idoc INT;
EXEC sp_xml_preparedocument @idoc OUTPUT, @MngCo;
SELECT I AS MD_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 MD_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 MD_DEPT_NO
INTO #MngDept
FROM OPENXML (@idoc, '/R/C',1) WITH (I INT)
EXEC sp_xml_removedocument @idoc;

-- Xml var

--select * from #MngCo
--select * from #MngDiv
--select * from #MngDept

CREATE table #tmpZ (MD_PROJ_NO INT, MD_CO_NO INT, MD_DIV_NO INT, MD_DEPT_NO INT);

INSERT INTO #tmpZ
SELECT PP.MD_PROJ_NO, PP.MD_CO_NO, 0, 0
FROM dbo.Tst_PPW AS PP
INNER JOIN #MngCo CO ON CO.MD_CO_NO = PP.MD_CO_NO
WHERE Tst_PERMT_USER_NO = @userId AND PP.ISACTIVE = 1

INSERT INTO #tmpZ
SELECT PP.MD_PROJ_NO, 0, PP.MD_DIV_NO, 0
FROM dbo.Tst_PPW AS PP
INNER JOIN #MngDiv DIV ON DIV.MD_DIV_NO = PP.MD_DIV_NO
WHERE Tst_PERMT_USER_NO = @userId AND PP.ISACTIVE = 1


INSERT INTO #tmpZ
SELECT PP.MD_PROJ_NO, 0, 0, PP.MD_DEPT_NO
FROM dbo.Tst_PPW AS PP
INNER JOIN #MngDept DEPT ON DEPT.MD_DEPT_NO = PP.MD_DEPT_NO
WHERE Tst_PERMT_USER_NO = @userId AND PP.ISACTIVE = 1


END
--and more code
GO