动态存储过程 [英] dynamic stored procedure

问题描述

嗨！
请允许我说明我的问题.我有一个名为employee的表，其列为Empid，last，first，mid，address和city.我已将此表克隆为employee，last和first列为employeecl.
在我的存储过程中，我想从员工中选择employeecl(empid，last，first)，所以下次我想在SP中添加要选择的字段时，只需将其添加到克隆表中，而无需再编辑SP，这意味着我的SP中将指定的列将取决于克隆表中可用的列，而克隆表中的列也将在源表中提供.我的克隆表不包含仅值字段.如果可以的话，您是否认为这是可能的，请帮助我，我将在我的项目中使用它，因为我被分配来动态制作我们的SP.

您的帮助将是一个很大的帮助.

谢谢！

在上述关于使用SQL Server 2005的iam的问题中，我实际上只是想使存储过程具有动态含义，所以我想在sp内创建select语句我可以将表中的字段存储为没有记录的字段，而在select语句中使用该tablefields吗?"

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?"

推荐答案

您应该在这里使用创建视图 [^ ] 特征.如果您不使用MS SQL DBMS样式，则所有主要的RDMB都支持这种类型的构造.

问候，

-Manfred

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

例如

声明@mainquery varchar(max);
声明@subquery varchar(max);

设置@subquery =``名称，地址'';
设置@ mainquery =``select id，''+ cast(@subquery as varchar)+''from table'';

exec sp_executesql @mainquery

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

这篇关于动态存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！