How to reach the values of dynamic columns in a table in SQL Server?

Question

This is my Invoice table:

CREATE TABLE [dbo].[InvoiceTable] 
(
    [Id]                 INT           IDENTITY (1, 1) NOT NULL,
    [CodeColumn]         NVARCHAR (50) NOT NULL,
    [NameColumn]         NVARCHAR (50) NOT NULL,
    [QTYColumn]          INT           NULL,
    [UnitCostColumn]     INT           NOT NULL,
    [DiscountRateColumn] FLOAT (53)    DEFAULT ((0)) NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

I want to have a Total Row, TotalQTY, and a Total Cost column. I created the Total Row and TotalQTY with this code (adding a dynamic column) in a stored procedure:

CREATE PROCEDURE GetInvoice
AS
    SELECT 
        ROW_NUMBER() OVER (ORDER BY NameColumn) AS RowNumberColumn,
        CodeColumn,
        NameColumn,
        SUM(QTYColumn) AS TotalQTY,
        UnitCostColumn,
        DiscountRateColumn,
        UnitCostColumn * SUM(QTYColumn) AS TotalRow
    FROM 
        InvoiceTable
    GROUP BY 
        CodeColumn, NameColumn, UnitCostColumn, DiscountRateColumn

My problem: I'm not able to have Total Row's value in the invoice table. Why? Because when I do SELECT * FROM INVOICE TABLE, it shows all columns except TotalRow and TotalQTY. So when I don't have these in the table how can I have the Total Cost column? I was thinking that if I had the total Row column, I would be able to use ExecuteScalar somehow in my C# program to sum up Total Row as Total Column. I would appreciate if someone could tell me how to use ExecuteScalar in this example.

You've created a query inside store procedure but you're looking for the column inside the table? — Salman A, Nov 19 '18 at 18:46
@absolute455, SalmanA was saying what's wrong; the "dynamic columns" you've created aren't available on your table, so when you query your table you don't see them. Rather, you've created a stored procedure which runs a query; so to see the results of that query, you'd need to execute the stored procedure (`exec GetInvoice`) and look at the results of that. — JohnLBevan, Nov 19 '18 at 20:07
@JohnLBevan ok fine I got it. But how can I actually have a dynamic column and be able on my table I dont understand this. whats the correct way to do what I do with my store procedure. I want to know these. :( I would appreciate it if you tell me. — Daniel_Ranjbar, Nov 21 '18 at 05:46
See the answer by ZekiGumus: https://stackoverflow.com/a/53380926/361842 on how to get the results in a query, or Estaban if you want these calculated columns to appear on your table https://stackoverflow.com/a/53381025/361842. What about their suggestions doesn't work for you? Are you not seeing the output you expect, getting an exception, etc? If so, try sharing more of your code as perhaps there's something else that also needs to be amended down the line... — JohnLBevan, Nov 21 '18 at 07:04
@JohnLBevan Okay thank you so much. I will do as you said and I will inform you if it worked for me or not. I **thank you**. by the way I just wanted to ask you **2 questions**.`1)` i wrote a query inside a store procedure because this was the only way I knew to execute a query in my c# program. **is there a way to execute an actual query in a c# program?** I know how to write queries inside double qoutes in c# program is this the only way? `2) `could you please tell me _(beside Esteban's answer)_ **a way to create dynamic columns which would be available in table**. — Daniel_Ranjbar, Nov 21 '18 at 09:34
Here's an example of C# code to execute a query: https://stackoverflow.com/a/21709663/361842. You can run queries, execute storedprocedures, or return all results from a named table/view. See https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.commandtype?view=netframework-4.7.2 — JohnLBevan, Nov 21 '18 at 10:00
For the "dynamic"/computed columns piece, Esteban's answer is the way to do it. Here's the related MS documentation: https://learn.microsoft.com/en-us/sql/relational-databases/tables/specify-computed-columns-in-a-table?view=sql-server-2017. If that's not meeting your requirement, say what you need to do and we can provide ways to meet that requirement; but if your requirement is to have a computed column defined on the table, this is the way it's done... If the issue is that your table already exists, try using `alter table` (see previous MS docs link for example) — JohnLBevan, Nov 21 '18 at 10:03

score 2 · Answer 1 · answered Nov 19 '18 at 18:50

2

I believe you need something like that:

SELECT *
        ,SUM(QTYColumn) OVER (PARTITION BY Id) TotalQty
        ,SUM(UnitCostColumn*QTYColumn) OVER (PARTITION BY Id) TotalRow
FROM InvoiceTable

answered Nov 19 '18 at 18:50

Zeki Gumus

1,484
7
14

score 2 · Accepted Answer · edited Nov 21 '18 at 10:17

for each of the total product valuations, you could support yourself with a computed column.

CREATE TABLE [dbo].[InvoiceTable] (
    [Id]                 INT           IDENTITY (1, 1) NOT NULL,
    [CodeColumn]         NVARCHAR (50) NOT NULL,
    [NameColumn]         NVARCHAR (50) NOT NULL,
    [QTYColumn]          INT           NULL,
    [UnitCostColumn]     INT           NOT NULL,
    [DiscountRateColumn] FLOAT (53)    DEFAULT ((0)) NULL,
    [Total w/Discount] AS ([QTYColumn] * [UnitCostColumn] * (1-[DiscountRateColumn])) PERSISTED, /* In case you need data persistence */
    [Total Cost] AS ([QTYColumn] * [UnitCostColumn]) PERSISTED, /* In case you need data persistence */
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

If you already have a table and need to add a computed column you can use the ALTER TABLE command; e.g.

ALTER TABLE [dbo].[InvoiceTable]
ADD [Total Cost] AS ([QTYColumn] * [UnitCostColumn]) PERSISTED

About PERSISTED

The PERSISTED word is optional.

If PERSISTED is specified, the value for this field is calculated when the row is inserted or updated; ensuring that the calculated value is calculated once, so each query doesn't incur the cost of calculation. However, this will create a performance penalty for the insert/update, as it will have to be calculated at that time. It will also use more space, since you're now remembering (persisting) the calculated value, so have to store this data somewhere.
If PERSISTED is not specified, the value is calculated each time the field is returned from a query, which may have an impact on the query's performance (i.e. time to perform the calculation for each row's computed column(s)), but you'll have less overhead on your insert/update statements, and will require less space.

These are general guidelines; SQL will do things under the covers to try to always give the best performance it can with the information available... more information on that here: https://dba.stackexchange.com/a/136740/10820 (though that's probably going too deep for now).

If in doubt, I'd say go with PERSISTED... in most scenarios you read data more often than you write it, and the amount of additional space to store this information is negligible (plus storage is pretty cheap). Once you've got a bit more experience, you can review that decision and amend if you determine that it's not the best for your specific scenario.

okay fine but it doesnt solve my problem I need `sum QTYcolumn as totalQTY` and `totalQTY * unitcostcolumn as TotalRow`... — Daniel_Ranjbar, Nov 19 '18 at 19:04
for a data store to be considered persistent, it must write to non-volatile storage. (In-memory) — arce.est, Nov 19 '18 at 19:58

How to reach the values of dynamic columns in a table in SQL Server?

2 Answers2

About PERSISTED