Menu
Is there a way to access columns by their index within a stored procedure in SQL Server?
The purpose is to compute lots of columns. I was reading about cursors, but I do not know how to apply them.
Let me explain my problem:
I have a row like:
field_1 field_2 field_3 field_4 ...field_d Sfield_1 Sfield_2 Sfield_3...Sfield_n
1 2 3 4 d 10 20 30 n
I need to compute something like (field_1*field1) - (Sfield_1* Sfiled_1) / more...
So the result is stored in a table column d times.
So the result is a d column * d row table.
As the number of columns is variable, I was considering making dynamic SQL, getting the names of columns in a string and splitting the ones I need, but this approach makes the problem harder. I thought getting the column number by index could make life easier.
765
You can use the sp_helpindex to view all the indexes of one table. And for all the indexes, you can traverse sys. objects to get all the indexes for each table. Only problem with this is that it only includes the index key columns, not the included columns.
To get the column name of a table we use sp_help with the name of the object or table name. sp_columns returns all the column names of the object. The following query will return the table's column names: sp_columns @table_name = 'News'
SQL AS keyword is used to give an alias to table or column names in the queries. In this way, we can increase the readability and understandability of the query and column headings in the result set.
You use column-ref to reference a column (database field), optionally qualified by a table and/or schema name. The column reference comprises the data type of the database field that the column represents (see Data Types).
No, you can not use the ordinal (numeric) position in the SELECT clause.
Only in the ORDER BY clause can you use the ordinal position, because it's based on the column(s) specified in the SELECT clause.
73
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
