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.

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

Need Help in SQL query optimization (2d)

Info needed on field "user_scans" of table "sys.dm_db_index_usage_stats" (3d)

Suspect Parameter Sniffing? (3d)

See values of a proc when it is called from another proc? (4d)

Varchar() datatype and C# SqlDataReader truncates string (4d)

Find the extra rows (5d)

How to represent birth and death record in mysql db without duplicating data of an existing persons table existing persons table (5d)

EPR updates issues (5d)

- Advertisement -