Access Columns of a Table by Index Instead of Name in SQL Server Stored Procedure

Access columns of a table by index instead of name in SQL Server stored procedure

First, as OMG Ponies stated, you cannot reference columns by their ordinal position. This is not an accident. The SQL specification is not built for dynamic schema either in DDL or DML.

Given that, I have to wonder why you have your data structured as you do. A sign of a mismatch between schema and the problem domain rears itself when you try to extract information. When queries are incredibly cumbersome to write, it is an indication that the schema does not properly model the domain for which it was designed.

However, be that as it may, given what you have told us, an alternate solution would be something like the following: (I'm assuming that field_1*field1 was meant to be field_1 * field_1 or field_1 squared or Power( field_1, 2 ) )

Select 1 As Sequence, field_1 As [Field], Sfield_1 As [SField], Sfiled_1 As [SFiled]
Union All Select 2, field_2, Sfield_2, Sfiled_2
...
Union All Select n, field_n, Sfield_n, Sfiled_n

Now your query looks like:

With Inputs As
    (
    Select 1 As Sequence, field_1 As [Field], Sfield_1 As [SField], Sfiled_1 As [SFiled]
    Union All Select 2, field_2, Sfield_2, Sfiled_2
    ....
    )
    ,  Results As
    (
    Select Case
            When Sequence = 1 Then Power( [Field], 2 ) - ( [SField] * [SFiled] ) 
            Else 1 / Power( [Field], 2 ) - ( [SField] * [SFiled] ) 
            End
            As Result
    From Inputs
    )
Select Exp( Sum( Log( Result ) ) )
From Results

How to retrieve specified columns instead of all from stored procedure resutls?

You could first dump all columns in a temp table and select only the necessary columns from that.

create table #1 (name varchar(50), system_type_name varchar(50), col3 int, col4 int)
 insert into #1 (name, system_type_name, col3, col4)
        exec dbo.sp_describe_first_result_set
      select name, system_type_name from #1
  drop table #1

How to return all values in the first column of a table without specifying column's name?

USE AdventureWorks;
GO

DECLARE @ObjectName SYSNAME = 'Sales.SalesOrderHeader';

DECLARE @SQL NVARCHAR(MAX);
SELECT
    @SQL = 'SELECT ' + QUOTENAME(name) + ' FROM ' + @ObjectName
FROM
    sys.columns
WHERE
    object_id = OBJECT_ID(@ObjectName)
    AND column_id = 1;
EXEC sp_executesql @SQL;

Select table with column named index

Use square brackets to quote reserved words:

select id, [index]
from item

See also the documentation on Delimited Identifiers.

Query to find out the stored procedure depending on a column

I use this query:

SELECT OBJECT_NAME(M.object_id), M.*
FROM sys.sql_modules M
JOIN sys.procedures P
ON M.object_id = P.object_id
WHERE M.definition LIKE '%blah%'

Obviously you'd have to substitute "blah" for the name of your column.

sql server select column by number

You have to use dynamic SQL to do this:

DECLARE @strSQL AS nvarchar(MAX)
DECLARE @strColumnName AS nvarchar(255)
DECLARE @iCounter AS integer 
DECLARE @curColumns AS CURSOR 

SET @iCounter = 0
SET @strSQL = N'SELECT '

SET @curColumns = CURSOR FOR 
(
    SELECT * FROM 
    (
        SELECT TOP 99999 
            COLUMN_NAME 
        FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_NAME = 'T_Markers' 
        AND ORDINAL_POSITION < 4 
        ORDER BY ORDINAL_POSITION ASC 
    ) AS tempT 
)

OPEN @curColumns
FETCH NEXT FROM @curColumns INTO @strColumnName 
WHILE @@FETCH_STATUS = 0
BEGIN
    -- PRINT @strColumnName 
    IF @iCounter = 0 
        SET @strSQL = @strSQL + N'
     [' + @strColumnName + N'] ' 
    ELSE 
        SET @strSQL = @strSQL + N'
    ,[' + @strColumnName + N'] ' 
    SET @iCounter = @iCounter + 1 
FETCH NEXT FROM @curColumns INTO @strColumnName 
END
CLOSE @curColumns
DEALLOCATE @curColumns 

SET @strSQL = @strSQL + N' 
FROM T_Markers 
'

PRINT @strSQL 

---

Related Topics