Retrieving Values from a table by column and row

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Retrieving Values from a table by column and row

Author

Topic

mobile@digitaltrendz.co.z
Starting Member

25 Posts

Posted - 2008-08-26 : 11:59:40

Hi all

I am trying to do the following. I have a table where some of the columns are created dynamically at run time. Different clients will have different column names. So the table names are not static.

RECORD ID | column A | column B | column C | column D
1 | 4.5 | 6.3 | 3.4 | 8.6
2 | 1.3 | 2.4 | 7.5 | 3.1

The Parameters required are
@RECORDID
@ColumnName eg (column A ,column B ,column C or column D)

I need to get the returned value by column and row. For example I want to retrieve RecordID 2 under column C, which will return the value 7.5. The parameters passed to the function are retrieved from the actual update statement below

Pseudo code:Stored Procedure

UPDATE Table1
SET [Column1] = dbo.GetValue(Table1.RecordID,Table1.ColumnName) -- The Parameters are from the current record in table 1
FROM Table1
WHERE (Department = 'Sales')

I tried something similar below but it cannot be done this way as you cannot execute a sql string in a function. This also stops you passing a column name as a variable as it needs to be executed dynamically

Pseudo code:
Function dbo.GetValue(@ColumnName varchar(50),@RecordID, @ReturnValue Decimal(18,4) = NULL OUTPUT)
AS
BEGIN
DECLARE @SQL NVARCHAR(4000)
SET @SQL = 'SELECT @returnVar = '+@ColumnName+' FROM Table1 WHERE RECORDID =''+ @RecordID +''
EXEC sp_executeSql @SQL, N'@returnVar Decimal(18,4) OUTPUT', @ReturnValue OUTPUT
SELECT @ReturnValue
END

I have tried function above but I cannot pass a column name as a variable as functions do not allow you to use the EXEC dynamic SQL. I have tried to get the value from a stored procedure which works well, however you cannot execute a SP call within a update statement. The value returned must be able to be used in a UPDATE SET [Column1] = Returnedvalue

Is there not a built in MS SQL function that can do this. Can somebody please point me in the correct direction or even give me an example as I have searched the web like crazy to no avail.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-26 : 13:29:07

what you could do is to try using OPENROWSET and execute sp and get return value and update. have a look at below link for a start

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/26/select-columns-from-exec-procedure-name-is-this-possible.aspx

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-26 : 16:55:57

HI again,

You really are running into all the gotchas of doing things this crazy dynamic way aren't you. Also your design seems to be entirely itterativly based so you are going to get awfull performance from your database. You asked me last time if there was any way not to do this dynamically -- I take it the suggestion I posted isn't a good fit fot you?

Anyway, regarding this, I think you are missing a simple step.

this is assuming that you are only wtanting to update 1 row in the target table (or want to update a set with the same static value, rather than relating it to some other column)

This is what you have so far? Correct me if I am wrong.

1) You've got your table name and column name as variables

2) You run some dynamic stored proc to return a value from the table as a variable

-- this is the stage you are at now isn't it.

3) You now have : the table name, the column name, and the value you want to set it to.

4) You can do some dynamic update with this (using sp_executeSql again).

I've assumed that
1) Table name is stored in a NVARCHAR variable called @tableName
2) Column Name is stored in a NVARCHAR variable called @columnName
3) Your returned value is stored in a variable called @returnedValue -- I think this was a DECIMAL last time?


DECLARE @Sql NVARCHAR(4000)

SET @sql = '
UPDATE [' + @tableName + ']
SET
    [' + @columnName + '] = @yourValue
WHERE
    [department] = ''sales'''

EXEC sp_executeSql @sql
    , N'@yourValue <DATATYPE>'
    , @returnedValue

-------------
Charlie

mobile@digitaltrendz.co.z
Starting Member

25 Posts

Posted - 2008-08-26 : 20:51:11

Hi Charlie
I am at the end of my teather. The calculations that I need to perform are numerous and a lot of them depend on the answer from previous calculations in the same rows. There are multiple tables of data and they are linked up with one to many relationships So most of them have common columns and values. Most of the tables are straight forword but 2 of them are like the example I showed above. At the moment I am running the calculations row by row using VB.net and just using update SP's. I am using a datagridview in my application to show the values used by the formulas ands the results.

This creates a problem as I need to loop through the records row by row. This causes a speed problem. With 100 records the speed degrades. I no my client can and will have up to 10000 records. I dont know If I would be able to do a bulk update in SQL and that I would need to do the updates row by row. When the application runs for the first time it does a calculation on all the rows. When the client starts to analyse the data they then change values per row so the calculation is run per row. However if some of the values are changed it is required to do a full calculation on all the rows.

I opted to use sql do to the amount of records and the speed of calculations.To give you an idea what I am trying to achieve, this is the code in vb.net which I have to run to do the full calc. I am not sure if you are familiar with vb.net.

Sub Calculate()

ToolStripProgressBar1.Minimum = 0
ToolStripProgressBar1.Maximum = Me.DSReview.Review_tbl.Rows.Count

For Each row As DataRow In Me.DSReview.Review_tbl.Rows

AddedIndex = DGBenefits.Rows.Add()
Dim rowIndex As Integer = DSReview.Review_tbl.Rows.IndexOf(row)

DGBenefits.Rows(rowIndex).Cells("PID").Value = Me.DSReview.Review_tbl.Rows(rowIndex)("PID")
DGBenefits.Rows(rowIndex).Cells("Employee No").Value = Me.DSReview.Review_tbl.Rows(rowIndex)("Employee No")

'SET DEFUALT PERCENTILE
If Me.DSReview.Review_tbl.Rows(rowIndex)("Percentile") Is DBNull.Value Then
Me.DSReview.Review_tbl.Rows(rowIndex)("Percentile") = "25th %"
End If

'SET DEFAULT RANGE
If Me.DSReview.Review_tbl.Rows(rowIndex)("Range") Is DBNull.Value Then
Me.DSReview.Review_tbl.Rows(rowIndex)("Range") = "Mid"
End If

'COMPARISON DECLARE
Dim Basic_Salary As Double = Me.DSReview.Review_tbl.Rows(rowIndex)("Basic Salary")
Dim Sel_Mkt As String = Me.DSReview.Review_tbl.Rows(rowIndex)("Market Structure")
Dim Paterson_Grade As String = Me.DSReview.Review_tbl.Rows(rowIndex)("Paterson Grade")
Dim Percentile As String = Me.DSReview.Review_tbl.Rows(rowIndex)("Percentile")
Dim Sel_Range As String = Me.DSReview.Review_tbl.Rows(rowIndex)("Range")
Dim TGC_Current As Double = Me.DSReview.Review_tbl.Rows(rowIndex)("Current TGC")
Dim Basic_Esc_Min, TGC_Esc_Min As New Double
Dim Basic_Esc_Mid, TGC_Esc_Mid As New Double
Dim Basic_Esc_Max, TGC_Esc_Max As New Double
Dim Curr_Basic_Perc_Mkt As New Double
Dim Basic_Esc, TGC_Esc As Double
Dim Basic_Range, TGC_Range As New Double

'
Dim R1_Grade_Incr_Perc As New Double
Dim R1_Grade_Incr As New Double
Dim R1_Basic_Salary As New Double
Dim R1_Basic_Salary_Perc_Mkt As New Double
Dim R1_TGC As New Double

'DECLARE
Dim R2_Mkt_ShortFall_Perc As New Double
Dim R2_Mkt_Match_Incr As New Double
Dim R2_Mkt_Match_Incr_Perc As New Double
Dim R2_Basic_Salary As New Double
Dim R2_Basic_Salary_Perc_Mkt As New Double
Dim BasicPlusPercentBenefit2, BasicPlusPercentBenefit As Double
Dim Percent As Double

'DECLARE
Dim R3_Increase_Value As Double
Dim R3_Increase_Perc As Double
Dim R3_BasicSalary As Double
Dim R3_Basic_Perc As Double
Dim R3_Fixed_Value As Double

'DECLARE
Dim TGC_Basic_Current As Double
Dim TGC_Basic_Market As Double
Dim TGC_Basic_Revised As Double
Dim TGC_New As Double

' VALUES
Me.Market_DataTableAdapter.Fill(Me.DSReview.Market_Data, Paterson_Grade, Sel_Mkt, _
GetMarketValues(Me.DSReview.Review_tbl.Rows(rowIndex)("Percentile")))

' REQUIRED MONTH BY BAND
Basic_Esc = GetBasicEscalation(Paterson_Grade)

' MONTH BY BAND
TGC_Esc = GetTGCEscalation(Paterson_Grade)

'ESCALATAED
Basic_Esc_Min = (Me.DSReview.Market_Data.Rows(0)("Min") * Basic_Esc) + Me.DSReview.Market_Data.Rows(0)("Min")
Basic_Esc_Mid = (Me.DSReview.Market_Data.Rows(0)("Mid") * Basic_Esc) + Me.DSReview.Market_Data.Rows(0)("Mid")
Basic_Esc_Max = (Me.DSReview.Market_Data.Rows(0)("Max") * Basic_Esc) + Me.DSReview.Market_Data.Rows(0)("Max")

'ESCALATED
TGC_Esc_Min = (Me.DSReview.Market_Data.Rows(1)("Min") * TGC_Esc) + Me.DSReview.Market_Data.Rows(1)("Min")
TGC_Esc_Mid = (Me.DSReview.Market_Data.Rows(1)("Mid") * TGC_Esc) + Me.DSReview.Market_Data.Rows(1)("Mid")
TGC_Esc_Max = (Me.DSReview.Market_Data.Rows(1)("Max") * TGC_Esc) + Me.DSReview.Market_Data.Rows(1)("Max")

'GET SELECTED RANGE VALUE FOR BASIC DATA
Curr_Basic_Perc_Mkt = Basic_Salary / GetRange(Sel_Range, rowIndex, Basic_Esc_Min, Basic_Esc_Mid, Basic_Esc_Max)
Basic_Range = GetRange(Sel_Range, rowIndex, Basic_Esc_Min, Basic_Esc_Mid, Basic_Esc_Max)

'GET SELECTED RANGE VALUE FOR TGC DATA
TGC_Range = GetRange(Sel_Range, rowIndex, TGC_Esc_Min, TGC_Esc_Mid, TGC_Esc_Max)

' CALCULATION
R1_Grade_Incr_Perc = GetGradeIncrease(Paterson_Grade)
R1_Grade_Incr = (Basic_Salary * R1_Grade_Incr_Perc)
R1_Basic_Salary = (Basic_Salary * R1_Grade_Incr_Perc) + Basic_Salary
R1_Basic_Salary_Perc_Mkt = R1_Basic_Salary / Basic_Range

'GET
GetBenefitTotals(Me.DSReview.Review_tbl.Rows(rowIndex)("PID"), Sel_Mkt, Paterson_Grade, R1_Basic_Salary, True) ' R1_Basic_Salary should be revised3 salary
BasicPlusPercentBenefit = R1_Basic_Salary + Rev2TotalPercents
Percent = (TGC_Range - Rev2TotalValues) / BasicPlusPercentBenefit '42289 Market tgc

' CALCULATION
R2_Mkt_ShortFall_Perc = GetShortFallPercentage(Paterson_Grade)

If Percent <= 1 Then ' If percent is <=1 then person s competitive
R2_Basic_Salary = R1_Basic_Salary
Else
R2_Basic_Salary = (((R1_Basic_Salary * Percent) - R1_Basic_Salary) * R2_Mkt_ShortFall_Perc) + R1_Basic_Salary
End If

R2_Mkt_Match_Incr = R2_Basic_Salary - R1_Basic_Salary
R2_Mkt_Match_Incr_Perc = R2_Mkt_Match_Incr / R1_Basic_Salary
R2_Basic_Salary_Perc_Mkt = R2_Basic_Salary / Basic_Range

'CALCULATION
If Not (Me.DSReview.Review_tbl.Rows(rowIndex)("Rev 3 Increase") Is DBNull.Value) Then

'AS VALUE
R3_Increase_Value = Me.DSReview.Review_tbl.Rows(rowIndex)("Rev 3 Increase")
R3_BasicSalary = R2_Basic_Salary + R3_Increase_Value
R3_Basic_Perc = R3_BasicSalary / Basic_Range

'GET VALUES 2ND PASS
GetBenefitTotals(Me.DSReview.Review_tbl.Rows(rowIndex)("PID"), Sel_Mkt, Paterson_Grade, R2_Basic_Salary, False) ' R1_Basic_Salary should be revised3 salary
BasicPlusPercentBenefit2 = R2_Basic_Salary + Rev3TotalPercents
Percent = (TGC_Range - Rev3TotalValues) / BasicPlusPercentBenefit2 '42289 Market tgc

ElseIf Not (Me.DSReview.Review_tbl.Rows(rowIndex)("Rev 3 Increase %") Is DBNull.Value) Then

'AS PERCENTAGE
R3_Increase_Perc = Me.DSReview.Review_tbl.Rows(rowIndex)("Rev 3 Increase %")
R3_BasicSalary = (R2_Basic_Salary * R3_Increase_Perc) + R2_Basic_Salary
R3_Basic_Perc = R3_BasicSalary / Basic_Range

'GET VALUES 2ND PASS
GetBenefitTotals(Me.DSReview.Review_tbl.Rows(rowIndex)("PID"), Sel_Mkt, Paterson_Grade, R2_Basic_Salary, False) ' R1_Basic_Salary should be revised3 salary
BasicPlusPercentBenefit2 = R2_Basic_Salary + Rev3TotalPercents
Percent = (TGC_Range - Rev3TotalValues) / BasicPlusPercentBenefit2 '42289 Market tgc

ElseIf Not (Me.DSReview.Review_tbl.Rows(rowIndex)("Rev 3 Revised Fixed Salary") Is DBNull.Value) Then
'AS FIXED
R3_Fixed_Value = Me.DSReview.Review_tbl.Rows(rowIndex)("Rev 3 Revised Fixed Salary")
R3_BasicSalary = R3_Fixed_Value
R3_Basic_Perc = R3_Fixed_Value / Basic_Range

'GET VALUES 2ND PASS
GetBenefitTotals(Me.DSReview.Review_tbl.Rows(rowIndex)("PID"), Sel_Mkt, Paterson_Grade, R2_Basic_Salary, False) ' R1_Basic_Salary should be revised3 salary
BasicPlusPercentBenefit2 = R2_Basic_Salary + Rev3TotalPercents
Percent = (TGC_Range - Rev3TotalValues) / BasicPlusPercentBenefit2 '42289 Market tgc

Else
'DEFAULT VALUES
R3_BasicSalary = R2_Basic_Salary
R3_Basic_Perc = R2_Basic_Salary / Basic_Range

'GET VALUES 2ND PASS
GetBenefitTotals(Me.DSReview.Review_tbl.Rows(rowIndex)("PID"), Sel_Mkt, Paterson_Grade, R2_Basic_Salary, False) ' R1_Basic_Salary should be revised3 salary
BasicPlusPercentBenefit2 = R2_Basic_Salary + Rev3TotalPercents
Percent = (TGC_Range - Rev3TotalValues) / BasicPlusPercentBenefit2 '42289 Market tgc

End If

'CALCULATION
TGC_Basic_Current = Basic_Salary / TGC_Current 'Current Basic as % of Current TGC
TGC_Basic_Market = Basic_Esc_Mid / TGC_Esc_Mid 'Market Basic as % of Market TGC

'GET VALUES 3RD PASS

Rev3TotalValues = Nothing
Rev3TotalPercents = Nothing

GetBenefitTotals(Me.DSReview.Review_tbl.Rows(rowIndex)("PID"), Sel_Mkt, Paterson_Grade, R3_BasicSalary, False)

'FINAL CALC
TGC_New = (R3_BasicSalary + Rev3TotalPercents + Rev3TotalValues) / TGC_Range '42289 Market tgc

'If TGCnew < to tgc Current then
' change cell colour

' R1_TGC = R3_BasicSalary + Rev3TotalPercents + Rev3TotalValues '42289 Market tgc

TGC_Basic_Revised = R3_BasicSalary / (R3_BasicSalary + Rev3TotalPercents + Rev3TotalValues) 'TGC_Esc_Mid 'Revised Basic as % of Revised TGC

Me.DSReview.Review_tbl.Rows(rowIndex)("Market Basic Salary Min") = Basic_Esc_Min
Me.DSReview.Review_tbl.Rows(rowIndex)("Market Basic Salary Mid") = Basic_Esc_Mid
Me.DSReview.Review_tbl.Rows(rowIndex)("Market Basic Salary Max") = Basic_Esc_Max
Me.DSReview.Review_tbl.Rows(rowIndex)("Current Basic as % of") = Curr_Basic_Perc_Mkt

Me.DSReview.Review_tbl.Rows(rowIndex)("Rev 1 Grade Increase %") = R1_Grade_Incr_Perc
Me.DSReview.Review_tbl.Rows(rowIndex)("Rev 1 Grade Increase") = R1_Grade_Incr
Me.DSReview.Review_tbl.Rows(rowIndex)("Rev 1 Basic Salary") = R1_Basic_Salary
Me.DSReview.Review_tbl.Rows(rowIndex)("Rev 1 Basic as % of") = R1_Basic_Salary_Perc_Mkt

Me.DSReview.Review_tbl.Rows(rowIndex)("Rev 2 Market Shortfall policy %") = R2_Mkt_ShortFall_Perc
Me.DSReview.Review_tbl.Rows(rowIndex)("Rev 2 Market Match Increase") = R2_Mkt_Match_Incr
Me.DSReview.Review_tbl.Rows(rowIndex)("Rev 2 Market Match Increase %") = R2_Mkt_Match_Incr_Perc
Me.DSReview.Review_tbl.Rows(rowIndex)("Rev 2 Basic Salary") = R2_Basic_Salary
Me.DSReview.Review_tbl.Rows(rowIndex)("Rev 2 Basic Salary as % of") = R2_Basic_Salary_Perc_Mkt

Me.DSReview.Review_tbl.Rows(rowIndex)("Rev 3 Basic Salary") = R3_BasicSalary
Me.DSReview.Review_tbl.Rows(rowIndex)("Rev 3 Basic Salary as % of") = R3_Basic_Perc

Me.DSReview.Review_tbl.Rows(rowIndex)("Market TGC Min of") = TGC_Esc_Min
Me.DSReview.Review_tbl.Rows(rowIndex)("Market TGC Mid of") = TGC_Esc_Mid
Me.DSReview.Review_tbl.Rows(rowIndex)("Market TGC Max of") = TGC_Esc_Max

Me.DSReview.Review_tbl.Rows(rowIndex)("Current Basic as % of Current TGC") = TGC_Basic_Current
Me.DSReview.Review_tbl.Rows(rowIndex)("Market Basic as % of Market TGC") = TGC_Basic_Market
Me.DSReview.Review_tbl.Rows(rowIndex)("Revised Basic as % of Revised TGC") = TGC_Basic_Revised

Me.DSReview.Review_tbl.Rows(rowIndex)("New TGC % of") = TGC_New

Rev2TotalValues = Nothing
Rev2TotalPercents = Nothing

ToolStripProgressBar1.Value = rowIndex
Next

Me.Review_tblTableAdapter.Update(Me.DSReview.Review_tbl)

End Sub 'Calculations

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-27 : 04:22:44

quote:
Originally posted by mobile@digitaltrendz.co.z

Hi Charlie
I am at the end of my teather. The calculations that I need to perform are numerous and a lot of them depend on the answer from previous calculations in the same rows. There are multiple tables of data and they are linked up with one to many relationships So most of them have common columns and values. Most of the tables are straight forword but 2 of them are like the example I showed above. At the moment I am running the calculations row by row using VB.net and just using update SP's. I am using a datagridview in my application to show the values used by the formulas ands the results.

This creates a problem as I need to loop through the records row by row. This causes a speed problem. With 100 records the speed degrades. I no my client can and will have up to 10000 records. I dont know If I would be able to do a bulk update in SQL and that I would need to do the updates row by row. When the application runs for the first time it does a calculation on all the rows. When the client starts to analyse the data they then change values per row so the calculation is run per row. However if some of the values are changed it is required to do a full calculation on all the rows.

I opted to use sql do to the amount of records and the speed of calculations.To give you an idea what I am trying to achieve, this is the code in vb.net which I have to run to do the full calc. I am not sure if you are familiar with vb.net.

.. Some vb code ....

Unfortunately I don't use vb but I've got no reason to assume that your VB code is wrong..

The reason you are running into these problems is because of the way you have decided to plan your data-model. There's no reason I can see why you must give different clients different (dynamic) COLUMNS in the database. A better normalised approach would be to have a client(s) table with a primary key and then represent your dynamic columns as rows (with a column called [columnName] if you must) in some other table with a foreign key to the client to make sure that different clients can have the same column name if necessary. If each client has their own database then even this isn't necessary: Converting the dynamic columns to rows will be enough.

If you have a pressing reason not to do this please post it: there are many here who will be able to help you out.

If you are at the stage where you must use this model for some reason then there are still things you can do.

It looks like right now you are:

getting a value row by row from the database and then writing it back row by row, this is terribly inefficient.

I don't know vb but I have to assume there is some data structure that you can store a results set into and then iterate through. (an array)

What you should be doing is this.

1) Get a result set that contains all the data you want to change.

2) ITERATE THROUGH THIS IN VB

3) Write the changed result set to the database.

As you, yourself, has found out as the number of data elements increase, your run time is going to rise in direct relation.

I think you need to stop and go back and reconsider the storage of data.

I assume that you are getting paid to do this work. Maybe you / your employer should consider hiring a sql contractor or similar.

-------------
Charlie

mobile@digitaltrendz.co.z
Starting Member

25 Posts

Posted - 2008-08-27 : 06:53:47

Hi Charlie

I understand what you are getting at. I have gone back to the drawing board. I used MS Access for many years which made it easy by how the DBMS worked so I am familiar with using databasing. I will re-design my data model. I found that most of the speed lost during calculating is due to the retrieving of the data and due to calculating row by row. I have also started going through my vb.net code to see what i can run in vb.net and what I can run in SQL to improve performance. I really appreciate your patients. Do you have any url's where I can read up more about data modeling tips, tricks and techniques for SQL

Thanks again to you and all the other people.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources