Modify stored procedure in code

Question

3.00/5 (2 votes)

See more:

Hello,

I have a stored procedure my code must use, however, the stored procedure is lacking "FOR XML AUTO" in its definition. I am not allowed to modify the stored procedure to include it. Is there a way to include "FOR XML AUTO" when setting on the SqlCommand component.?

By the way I am using C# to write my code.

Posted 16-Aug-13 10:02am

noobville

Add a Solution

Comments

Richard C Bishop 16-Aug-13 16:07pm

Can you not parse the returned data into XML? I can't see how you could add functionality to a stored procedure without altering it.

noobville 16-Aug-13 16:13pm

The returned data is a integer, but when I run the sp in SQL Server studio I can see the result.

Richard C Bishop 16-Aug-13 16:15pm

Ok then, can you not then create your own XML with that integer as one of the nodes?

noobville 16-Aug-13 16:17pm

No, the integer is useless in relation to the data I need. The sp is being used by an old application, but the owners and my boss want to continue using them.

Maciej Los 16-Aug-13 16:08pm

Sorry, could you be more specific and provide more details?

noobville 16-Aug-13 16:15pm

Is it possible to send this to the SqlCommand object?
"EXEC [dbo].[sp_GetSomeData] @SomeDataID FOR XML AUTO"
The sp is missing the "FOR XML AUTO", otherwise I cannot use the ExecuteXmlReader to give a XmlReader.

ZurdoDev 16-Aug-13 16:22pm

No, but why do you want to?

noobville 16-Aug-13 16:24pm

because FOR XML AUTO formats the data exactly the way I need it.

noobville 16-Aug-13 16:25pm

http://stackoverflow.com/questions/1087200/calling-stored-procedure-via-query-and-getting-return-value-in-c-sharp-without-o
found the above link, there seems to be some way to perform a "exec" via code that can alter a sp...not following the example though

ZurdoDev 16-Aug-13 16:25pm

Perhaps you could execute it into a temp table and then do a select off of that, but that seems like overkill. But it may be what you need.

iCanDivideByZero 16-Aug-13 16:45pm

It does, but can you elaborate with some psuedo code please.

ZurdoDev 16-Aug-13 16:48pm

Something along the lines of
DECLARE @tempTable TABLE (ID int, Field1 NVARCHAR (50))
INSERT INTO @tempTable
EXEC sp_SPName

-- Then select FOR XML AUTO from your temp table

iCanDivideByZero 16-Aug-13 16:52pm

Oh so your saying create another SP that simply does the same function, but at the necessary code to it. That would work, BUT I am not even able to see the code in the SP I am working with. That level of access was not granted. I feel like i am being screwed here lol

ZurdoDev 16-Aug-13 17:00pm

No. Create another stored procedure that executes the original stored procedure and dumps into a table. Then do your for xml on that table.

deepakdynamite 20-Aug-13 6:43am

You won't be able to alter your procedure because of insufficient rights and that is what you are facing right now. Better to work on getting alter rights on Database

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)