在 SQL Server 2008 中返回可变列数 [英] Return variable number of columns in SQL Server 2008

问题描述

我的表包含以下字段，Name,Age,Salary,Phone,DOB.根据设置表，我只需要选择一些字段.比如我在设置里说，只需要Name和Phone.我如何使用存储过程来做到这一点?

My table contains the following fields, Name,Age,Salary,Phone,DOB. Based on a settings table, I have to select only some fields. For example, I say in settings, only Name and Phone is required. How can I do it using stored procedure ?

哪个好.

1. 从表中选择必填字段.
2. 选择所有列并在 ASP.NET 页面中，使用 .Visibility 属性隐藏或显示列

1. Select the required fields from the table.
2. Select all columns and in ASP.NET page, use .Visibility property to hide or show columns

推荐答案

SQL 是一种固定的列语言:不能动态"添加或删除列

SQL is a fixed column language: columns can not be added or removed "on the fly"

您需要使用动态 SQL 来构建 SELECT 语句，或者使用 IF 语句来执行不同的语句.但是，您会遇到缓存、安全和注入问题.

You would need to use dynamic SQL to build a SELECT statement, or use IF statements to execute different ones. However, you open up caching, security and injection issues.

就我个人而言，我会忽略客户端代码中的列，并使用简单、单一的高效 SQL 查询.SQL Server 和客户端之间的契约或 API 应该是静态的和可预测的.如果在 SQL Server 中应用了设置表，您的客户端不知道需要哪些列.如果您的客户确实知道，那么它可以忽略它们.

Personally, I'd ignore columns in the client code and have a simple, single efficient SQL query. The contract or API between SQL Server and the client should be static and predictable. If the settings table is applied in SQL Server, your client doesn't know what columns to expect. If your client does know, then it can ignore them.

编辑后，选项 2，有点.

After your edit, option 2, kind of.

但是数据应该在在页面中呈现之前被删除.

But the data should be removed before being rendered in the page.

保持简单:不要尝试优化任何东西

Keep it simple: don't try to optimise anything yet

这篇关于在 SQL Server 2008 中返回可变列数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！