Computed Columns

By Bill Graziano on 10 August 2000 | 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.