There is a field in the table that contains the salary value (type nvarchar (10), not I did). It is necessary that the request displays a bonus and total salary and bonus. Below is an example of a calculated premium.

declare @temp_table table ( user_salary nvarchar(10) ) insert into @temp_table (user_salary) values ('') insert into @temp_table (user_salary) values (NULL) insert into @temp_table (user_salary) values ('123') select (convert(float,isnull((case user_salary when '' then 0 else user_salary end),0))/2) as [prem] from @temp_table

Is it possible to use this calculated value in the future or will I have to duplicate the code?

UPD. It is understood that the salary, the bonus, and their total amount should be displayed on the basis of the request.

  • one
    And what do you mean by "calculated value": a float from an expression or the result of a query? - Mirdin
  • You can wrap this into another select and use the calculated value. those. select prem, perm + oklad from (ваш запрос) A - Mike
  • @Mirdin result, which will turn out in the column prem - Ajmda

3 answers 3

You can enclose your query in one more select and at the top level the calculated columns will be available for any work with them:

 select [prem], [prem]+... from ( select user_salary, (convert(float,isnull((case user_salary when '' then 0 else user_salary end),0))/2) as [prem] from @temp_table ) A
  • thank. Exactly what is needed. - Ajmda

Use SQL block . It is possible to use variables and their substitution in queries.

  • And what does pl / sql have to do with the question on the ms sql server (although this is certainly a guess, the author did not indicate, but this declaration of temporary tables clearly indicates this) - Mike
  • and what does ms sql server have to do with the question? - Smithson
  • We can clarify with the author of course, but he uses the declare table and the table with the dog and the conclusion of column names in square brackets, this syntax is only in ms sql (well, maybe in the sybase from which ms sql occurred) - Mike
  • one
    I understand, mona? The author asked, "as in sql", I answered, as in sql. If the author needs ms sql - he will clarify, but the next one who will find this question by search will see the answer "as in sql". And not just as in ms. - Smithson
  • And by the way, the community does not welcome when the whole essence of the answer is contained by reference, because the link may not be working. ru.stackoverflow.com/help/how-to-answer - Mike

You can declare another variable and assign it the value of your expression in the query, and continue to work with it.

DECLARE @x float; select @x = (convert(float,isnull((case user_salary when '' then 0 else user_salary end),0))/2) as [prem] from @temp_table

  • You can work in the same query, i.e. write select @x=..., @x+column2 from ... where @x>100 ? (I think the author of the question meant it) - Mike
  • @Mike I am sorry - yes, I meant it. Those. in one request to display the salary, bonus and total amount. - Ajmda