Technical Article

Dynamic and Unique field Select creator

,

DESCRIPTION:
This procedure produces a SELECT statement which explicitly lists all fields for 'tablename' with fieldname and alias as the column name. The SELECT it produces is ready to run or if you need to join several tables just copy the column names off the SELECT it generates. I use this a lot when I create INNER JOINS with several tables and want to put the data into #temp tables which require unique field names.

LIMITATIONS:
* The way it's written it won't run in master database as a sp_ proc

INSTRUCTIONS:
_SELECT 'tablename', 'alias'

EXAMPLE: (using pubs database)
_select 'authors', 'a'

Please feel free to modify to fit your needs.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/********************************************************************************************************
** Name: _Select
** Desc: creates a select statement which specifies all columns for a table
**       doesn't have all the [] around the columns that the native SQL create does
** plus provides unique titles for all the fields  
** Parm: Table name and an alias 
** Retr: select with all the columns from tablename followed by alias
** Auth: D Simmons
**
** Mod Date:
** 05.15.07 - dts Original version
*********************************************************************************************************/
CREATE PROCEDURE [dbo].[_Select] (
@TableVARCHAR(100),
@AliasVARCHAR(20)
)

AS

 SET NOCOUNT ON

-- ------------------------------------------------------------------------
-- DECLARATION and TABLE CREATION
-- ------------------------------------------------------------------------
DECLARE@CurrOrdINT,
@SQLVARCHAR(4000),
@CurrColVARCHAR(200),
@LFCHAR(1),
@TABCHAR(1),
@SQCHAR(1),
@ColumnName VARCHAR(200)

-- ------------------------------------------------------------------------
-- INITIALIZE
-- ------------------------------------------------------------------------
SET @SQL = ''
SET @LF = CHAR(10)
SET @TAB = CHAR(9)
SET @SQ = CHAR(39)

-- ------------------------------------------------------------------------
-- LOGIC
-- ------------------------------------------------------------------------

-- get the first column for this table
SET @CurrOrd =(SELECTMIN(Ordinal_Position)
 FROMINFORMATION_SCHEMA.COLUMNS
 WHERETable_Name = @Table 
) 

-- if it is NULL the table name is bad
IF @CurrOrd IS NULL BEGIN
PRINT 'Table name ' + @Table + ' does not exist!'
RETURN
END


-- while we have a column
WHILE @CurrOrd IS NOT NULL BEGIN

-- get the current column for this ordinal position
SET @CurrCol = ( SELECT Column_Name 
 FROMINFORMATION_SCHEMA.COLUMNS
 WHERETable_Name = @Table 
AND Ordinal_Position = @CurrOrd)

-- will be used to pull the column and also as it's title
-- so it will be unique 
SET @ColumnName = @Alias + '.' + @CurrCol

-- append to @SQL the alias.columnName followed by a comma
SET @SQL = @SQL + @ColumnName + ' ' + @SQ + @ColumnName + @SQ + ', '

-- get the next column ordinal
SET @CurrOrd = ( SELECTMIN(Ordinal_Position)
 FROMINFORMATION_SCHEMA.COLUMNS
 WHERETable_Name = @Table 
AND Ordinal_Position > @CurrOrd)


END

SET @SQL = LEFT(@SQL, LEN(@SQL) - 1 )-- strip off the last comma

-- create the SQL for the table
SET @SQL = 'SELECT ' + @TAB + @SQL + @LF 
SET @SQL = @SQL + 'FROM ' + @TAB + 'dbo.' + @Table + ' ' + @Alias

-- display it to the screen
PRINT @SQL

Rate

4 (1)

You rated this post out of 5. Change rating

Share

Share

Rate

4 (1)

You rated this post out of 5. Change rating