Is it possible to add a clause to a Stored Procedure when calling it

Question

0.00/5 (No votes)

See more:

Is it possible to add a clause to a Stored Procedure when calling it from your code; C#? I have Stored Procedure for which I need to add the clause "FOR XML AUTO" or "FOR XML RAW", but I do not have write access to change since its our clients stored procedure. I know the easiest would be to have them change it, however, I do not want to give up on creating our own solution first.

Any ideas?

Posted 19-Aug-13 6:53am

iCanDivideByZero

Add a Solution

Comments

RedDk 19-Aug-13 12:55pm

Which is it "RAW" or "AUTO"?

iCanDivideByZero 19-Aug-13 13:01pm

AUTO

pradiprenushe 19-Aug-13 13:00pm

Not possible,
1.Create new sp add with same defination & add clause into that OR
2. Create if else logic iin sp one will existing & one with you clause

iCanDivideByZero 19-Aug-13 13:01pm

Are there no other options available then?

pradiprenushe 19-Aug-13 13:03pm

1.Create new sp add with same defination & add clause into that OR
2. Create if else logic iin sp one will existing & one with you clause
So no problem to existing application also

iCanDivideByZero 19-Aug-13 13:04pm

Unfortunately, since I don't have "modify" access I cannot see what the stored procedure is actually doing to duplicate its functionality.

bbirajdar 19-Aug-13 13:09pm

you want us to help you to gain access to somebody else's sql server and modify their code????? you pitiable hacker...never....

iCanDivideByZero 19-Aug-13 13:11pm

sorry if it is coming across like that, but I AM NOT TRYING TO HACK ANYTHING! This task was given to by my lead and says its possible to get it done...but according to the comments I am getting it sounds like it is not.

RedDk 19-Aug-13 13:09pm

... good comment ...

pradiprenushe 19-Aug-13 13:10pm

You dont have read access also? Or sp is encrypted?

iCanDivideByZero 19-Aug-13 13:16pm

I have read access to the database, table, views, and stored procedures...however, I cannot modify them it appears. I was given an account with limited access, I am assuming that is all my lead said I required when I was setup.

Akbar Ali Hussain 19-Aug-13 13:21pm

You can execute the SP and store to a DataSet. Then use DataSet.WriteXML() to get the result in XML format.

iCanDivideByZero 19-Aug-13 13:35pm

tried that already, and I believe the FOR XML AUTO must do some formatting that the method you mentioned does as, booleans come back as "true" and/or "false", rather "0" or "1" like when you have the clause in place. That is just one of the difference I've seen; which is major enough.

pradiprenushe 19-Aug-13 13:23pm

Create proper scenario & discuss with your lead. You can alter query through code but you cant append to sp as its compiled.

iCanDivideByZero 19-Aug-13 13:33pm

Given my circumstances do you think it is feasiable for me to be able to alter the sp then? Can you point me to some examples, so that maybe I can attempt that before having to return to him and try desparately to convince him it cannot be done in the manner he assigned it to me.

iCanDivideByZero 19-Aug-13 13:46pm

You know since I do not have write access I doubt that will work lol

What about creating another SP and that calls the old one and wraps the ouput; not the return value, using FOR XML AUTO???

2 solutions

Solution 2

Prepare an SQL as follows to call SP and return the result as XML. Then call this SQL from C#.

e.g.
select * from SPName() for xml auto

Posted 19-Aug-13 12:32pm

Akbar Ali Hussain

Comments

iCanDivideByZero 20-Aug-13 8:21am

Could you please elaborate on this technique, I'm a bit of a noob

Add a Solution

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)

pradiprenushe · Accepted Answer · 2013-08-19T08:00:00

Solution 1

Yes you can do this using result of existing sp. Hope this will be helpful

SQL

DECLARE @cache TABLE (CountCol int NOT NULL)
INSERT @cache EXECUTE usp_Yoursp @Id=123
SELECT CountCol FROM @cache FOR XML AUTO

Posted 19-Aug-13 8:00am

pradiprenushe

Updated 19-Aug-13 18:21pm

v3

Comments

iCanDivideByZero 19-Aug-13 15:42pm

hmmmm...i tried your idea and received the following error.

Column name or number of supplied values does not match table definition. Could not find stored procedure 'CountCol'

Any clue to what is causing this?

pradiprenushe 20-Aug-13 0:21am

Check output of sp & create table variable according to that. I have tried it & it is working.

pradiprenushe 20-Aug-13 0:49am

If your sp returning ID then create table variable with id column

DECLARE @cache TABLE (id int NOT NULL)

INSERT @cache EXECUTE testsp

SELECT id FROM @cache FOR XML AUTO

iCanDivideByZero 20-Aug-13 10:36am

Dude! That is exactly what I am looking for, Thanks! The output from this very closely matches the SQLXML output I have to match up to.

iCanDivideByZero 21-Aug-13 7:58am

Quick question. Can you declare the @cache TABLE without specifying the id or the colums for the table?

pradiprenushe 22-Aug-13 1:39am

no