Computed Columns

By Bill Graziano on 10 August 2000 | 1 Comment | Tags: Queries


John writes "Hi, Is it possible to generate psuedo columns in a stored procedure and if so how? I.e. returns a result set with a column which doesn't exists on any of the tables involved in the query. I've looked into using a local variable but to no avail. Thanks." In this article we'll discuss creating computed columns based on other columns.

We'll start with a basic query using the pubs database:

SELECT au_fname, au_lname FROM authors

Let's say you want one field that is the combination of the first and last name. In that case your query would look like:

SELECT full_name = au_fname + ' ' + au_lname FROM authors

This creates a column called FULL_NAME that is calculated based on the other two fields. You can also use this type of query to create a view.

You could also use a local variable to generate a calculated column. Let's say you wanted all of the members of the authors table to be part of the Smith family. You could write code like this:

DECLARE @lname char(30)
SELECT @lname = 'Smith'
SELECT full_name = au_fname + ' ' + @lname FROM authors


I know that isn't the best example but you can see how it works.

Now, when I read your question I also thought you might want something like "I'm select all the columns from table X and table Y and now I want this column from table Z but I don't want to join to it." If that's the case you can use a subquery to return the value into the query or you can load it into a variable before you run the query and use the variable as I've described. I hope this answers your question John. If not, just drop us an email with some sample code and we'll take another shot.

Discuss this article: 1 Comment so far. Print this Article.

If you like this article you can sign up for our weekly newsletter. There's an opt-out link at the bottom of each newsletter so it's easy to unsubscribe at any time.

Email Address:

Related Articles

Using Dynamic SQL in Stored Procedures (7 March 2011)

Joining to the Next Sequential Row (2 April 2008)

Writing Outer Joins in T-SQL (11 February 2008)

Aggregating Correlated Sub-Queries (23 October 2007)

How to Use GROUP BY with Distinct Aggregates and Derived tables (31 July 2007)

How to Use GROUP BY in SQL Server (30 July 2007)

Returning Complex Data from User-Defined Functions with CROSS APPLY (11 June 2007)

Returning a week number for any given date and starting fiscal month (2 May 2007)

Other Recent Forum Posts

T-SQL Code Help (12 Replies)

Database keeps growing by 5 gigs, what should I do (0 Replies)

Comparing Rows in same table (1 Reply)

T-test (2 Replies)

Conditional Join creating duplicates (5 Replies)

Return error of a timeouted server (2 Replies)

Retrieving Current and Prior Year Total on one row (1 Reply)

Randomly Assign to Group (5 Replies)

Subscribe to SQLTeam.com

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -