dynamic stored procedure

Question

0.00/5 (No votes)

See more:

Hi!
Please allow me to illustrate my question. I have a table called employee with the columns empid, last, first, mid, address and city. I've cloned this table to employeecl with the columns empid, last and first.
In my storeprocedure I want to select employeecl(empid,last,first) from employee so the next time I want to add a field to be selected in my SP I just have to add it in my clone table without anymore editing my SP, meaning columns that will be specified in my SP will depend on the columns available in the clone table which is also available in the source table. My cloned table does not contain values only fields. Do you think this is possible if so please help me I will use this in my project as I'm assigned to make our SPs dynamically.

Your help would be a great help.

Thank you!

In my question above iam using sql server 2005 actually i just want to make my stored procedure dynamic meaning i want to create select statement inside sp "Can i store fields in a table no records just fields and use that tablefields in my select statement?"

Posted 19-Sep-12 2:17am

JOEY G. MOYA

Updated 19-Sep-12 15:27pm

v4

Add a Solution

2 solutions

Solution 1

What you should do here is use the Create View[^] feature. In case you're not using a MS SQL DBMS flavor, this type of construct is supported by all major RDMBs.

Regards,

— Manfred

Posted 19-Sep-12 2:35am

Manfred Rudolf Bihy

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)

Ajay J Singh · Accepted Answer · 2012-09-19T19:04:00

Solution 2

for example

declare @mainquery varchar(max);
declare @subquery varchar(max);

set @subquery = 'name , address';
set @mainquery= 'select id,'+ cast(@subquery as varchar )+' from table' ;

exec sp_executesql @mainquery

Posted 19-Sep-12 19:04pm

Ajay J Singh

Comments

JOEY G. MOYA 21-Sep-12 1:16am

Think this solution would help me Thank you very much