_Select Update

/********************************************************************************************************
** Original Name: _Select
** Name: ps_GetColumnNames
** 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: Database name, Table name, an alias and a column alias bit
** Retr: select with all the columns from tablename, followed by alias (optional)
** Original Auth: D Simmons 
** Updated By: J Rice
**
** Mod Date:
** 05.15.07 - dts Original version
** 05.21.07 - 1. Added dynamic feature to query tables in other databases (not linked servers) from one sp.
**            2. Enabled table name to be sent with owner qualification.
**		    3. Added column alias bit parameter to enable/disable column aliases.
*********************************************************************************************************/

alter PROCEDURE [dbo].[_Select] (
	@DB		VARCHAR(100),
	@Table		VARCHAR(100),
	@Alias		VARCHAR(20),
	@ColumnAlias 	bit = 0
)

AS

 SET NOCOUNT ON

	-- ------------------------------------------------------------------------
	-- DECLARATION and TABLE CREATION
	-- ------------------------------------------------------------------------

	DECLARE	@CurrOrd	INT,
			@SQL		VARCHAR(4000),
			@CurrCol	VARCHAR(200),
			@LF		CHAR(1),
			@TAB		CHAR(1),
			@SQ		CHAR(1),
			@ColumnName     VARCHAR(200),
			@CurrOrd1_Sql   NVARCHAR(4000),
			@CurrOrd2_Sql   NVARCHAR(4000),
			@CurrCol_Sql    NVARCHAR(4000),
			@Param          NVARCHAR(100)

	-- ------------------------------------------------------------------------
	-- INITIALIZE
	-- ------------------------------------------------------------------------

	SET @SQL = ''
	SET @LF = CHAR(10)
	SET @TAB = CHAR(9)
	SET @SQ = CHAR(39)
	--remove table owner if included in parameter
	SET @Table = right(@Table, len(@Table) - charindex('.', @Table))
	--dynamic sql to allow use in any db on server from one sp
	SET @CurrOrd1_Sql = N' select @CO = ( SELECT MIN(Ordinal_Position) ' +
					N' FROM ' + @DB + '.INFORMATION_SCHEMA.COLUMNS ' +
    					N' WHERE Table_Name = ''' + @Table + ''' )'
	SET @CurrOrd2_Sql = N' select @CO = ( SELECT MIN(Ordinal_Position) ' +
					N' FROM ' + @DB + '.INFORMATION_SCHEMA.COLUMNS ' +
    					N' WHERE Ordinal_Position > @CO ' +
				     N' AND Table_Name = ''' + @Table + ''' )'
	SET @CurrCol_Sql =  N' SELECT @CC = ( SELECT Column_Name ' +
				     N' FROM ' + @DB + '.INFORMATION_SCHEMA.COLUMNS ' +
				     N' WHERE Ordinal_Position = @CO ' +
				     N' AND Table_Name = ''' + @Table + ''' )'

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

	-- get the first column for this table
	select @Param = N'@CO int output'
	exec sp_executesql @CurrOrd1_Sql, @Param, @CO = @CurrOrd output

	-- 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
		select @Param = N'@CO int, ' +
					 N'@CC nvarchar(200) output'
		exec sp_executesql @CurrCol_Sql, @Param, @CurrOrd, @CC = @CurrCol output

		-- 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 if bit is set
		SET @SQL = @SQL + case when @ColumnAlias = 0 
							then @ColumnName + ' ' + @SQ + @ColumnName + @SQ + ', ' 
							else @ColumnName + ', ' end

		-- get the next column ordinal
		select @Param = N'@CO int output'
		exec sp_executesql @CurrOrd2_Sql, @Param, @CO = @CurrOrd output
		
	END
	
	-- strip off the last comma
	SET @SQL = LEFT(@SQL, LEN(@SQL) - 1 )			

	-- 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