Scalar Function vs Stored Proc vs INSERT INTO new Table

Question

A) After creating the function and calling it in my query, I noticed that it returns both ‘Qualified’ and ‘NULL’ results in the ‘Reports’ column. And this is not good for obvious reasons. Simplifying the logic behind it is what’s driving me nuts. How do I add a clause - where a clause cannot be added? Do I have to alter the function with some conversion, insert the results into a table and then do a function, maybe another variable? What am I missing?

B) My whole argument is, why can I not add a WHERE clause when querying the [dbo].[FactInternetSales] table with the function in the SELECT statement? Should this alteration be made in the function itself to be able to return a result with a WHERE clause? and if so, how do I go about doing just that?

ALTER FUNCTION [dbo].[ExTaxQual]
                    (@Val1 money 
                    ,@Val2 money) 
                    RETURNS char(4) AS
    BEGIN 
        DECLARE @result char(4) 

            IF (@Val1 + @Val2) > 1000
                SET @result = 'Qualified'
                ELSE
                SET @result = NULL
            RETURN
                    @result
    END

And my query:

SELECT 
        [ExtendedAmount]
       ,[TaxAmt]
       ,[dbo].[ExTaxQual]([ExtendedAmount]
                ,[TaxAmt]) AS 'Reports'
FROM
        [dbo].[FactInternetSales]

Result..

SELECT [ExtendedAmount] ,[TaxAmt] ,[dbo].[ExTaxQual]([ExtendedAmount] ,[TaxAmt]) AS 'Reports' FROM [dbo].[FactInternetSales] ------------------------*/

(60398 row(s) affected)

Which is not nearly consistent enough.

B) Maybe the example below is a bit clearer

SELECT [ExtendedAmount]
      ,[TaxAmt]
      ,[dbo].[ExTaxQual](
                         [ExtendedAmount]
                        ,[TaxAmt]) 
AS Report

FROM [dbo].[FactInternetSales]

WHERE [ExtendedAmount] + [TaxAmt] = 'Qualified'
------------------------------VS
SELECT [ExtendedAmount],
       [TaxAmt],
       'Qualified' AS Reports
FROM   [dbo].[FactInternetSales]
WHERE  [ExtendedAmount] + [TaxAmt] > 1000

And while you're at it: What is your input? Which parameter values result in a NULL value? — Onkel Toob
– Onkel Toob, Commented Apr 1, 2015 at 10:46
@papatoob the params are the two amounts added to be equated to the 'result'>1000. I do not want the function to show where the param is NULL — Basic_
– Basic_, Commented Apr 1, 2015 at 11:04
I didn't get that at all from the question. See NewUser's answer then... — Onkel Toob
– Onkel Toob, Commented Apr 1, 2015 at 11:11

StackUser · Accepted Answer · 2015-04-01 10:51:26Z

1

If you want to take report only for qualified records then use this.

SELECT [ExtendedAmount],
       [TaxAmt],
       'Qualified' AS Reports
FROM   [dbo].[FactInternetSales]
WHERE  [ExtendedAmount] + [TaxAmt] > 1000

This query gives report of qualified and not qualified records. Here you wont require function at all.

SELECT [ExtendedAmount],
       [TaxAmt],
       CASE
         WHEN [ExtendedAmount] + [TaxAmt] > 1000 THEN 'Qualified'
         ELSE 'Not Qualified'
       END AS Reports
FROM   [dbo].[FactInternetSales]

answered Apr 1, 2015 at 10:51

StackUser

5,3983 gold badges29 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Basic_ Over a year ago

This helped. its funny because I did run it a few times before I asked the question and it kept returning the column names without data. it was probably a bad query. ThanQ man :)

Basic_ Over a year ago

The first one is what I needed to query against the function. Works perfect. unfortunately I cannot vote your answer as I am still new.

Collectives™ on Stack Overflow

Scalar Function vs Stored Proc vs INSERT INTO new Table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related