sql server - Scalar Function vs Stored Proc vs INSERT INTO new Table -

a) after creating function , calling in query, noticed returns both ‘qualified’ , ‘null’ results in ‘reports’ column. , not obvious reasons. simplifying logic behind what’s driving me nuts. how add clause - clause cannot added? have alter function conversion, insert results table , function, maybe variable? missing?

b) whole argument is, why can not add clause when querying [dbo].[factinternetsales] table function in select statement? should alteration made in function able return result clause? , if so, how go doing that?

alter function [dbo].[extaxqual]                     (@val1 money                      ,@val2 money)                      returns char(4)     begin          declare @result char(4)               if (@val1 + @val2) > 1000                 set @result = 'qualified'                 else                 set @result = null             return                     @result     end 

and query:

select          [extendedamount]        ,[taxamt]        ,[dbo].[extaxqual]([extendedamount]                 ,[taxamt]) 'reports'         [dbo].[factinternetsales] 

result..

select [extendedamount] ,[taxamt] ,[dbo].[extaxqual]([extendedamount] ,[taxamt]) 'reports' [dbo].[factinternetsales] ------------------------*/

(60398 row(s) affected)

which not consistent enough.

b) maybe example below bit clearer

select [extendedamount]       ,[taxamt]       ,[dbo].[extaxqual](                          [extendedamount]                         ,[taxamt])  report  [dbo].[factinternetsales]  [extendedamount] + [taxamt] = 'qualified' ------------------------------vs select [extendedamount],        [taxamt],        'qualified' reports   [dbo].[factinternetsales]  [extendedamount] + [taxamt] > 1000 

if want take report qualified records use this.

select [extendedamount],        [taxamt],        'qualified' reports   [dbo].[factinternetsales]  [extendedamount] + [taxamt] > 1000 

this query gives report of qualified , not qualified records. here wont require function @ all.

select [extendedamount],        [taxamt],        case          when [extendedamount] + [taxamt] > 1000 'qualified'          else 'not qualified'        end reports   [dbo].[factinternetsales]