CODEWITHSUNDEEP
sqlsql-server
user2096512
user2096512

Reputation: 469

SQL: Use table from table valued function

I'm sure there is a better way of doing this as I am running the same Table Valued Function several times, is there a way to get the table from the TVF just once and then run the queries against it to set my variables?

DECLARE @RUNS INT
DECLARE @WON INT
DECLARE @PERC FLOAT

SET @RUNS = (SELECT COUNT(*) FROM tvf_horse_going('Allow Me', 'HEAVY'))
SET @WON = (SELECT COUNT(*) FROM tvf_horse_going('Allow Me', 'HEAVY') WHERE PosNum = '1')
SET @PERC = (@WON * 100) / @RUNS

SELECT @RUNS As Runs, @WON As Won, @PERC As Perc

Upvotes: 1

Views: 64

Answers (3)

Raju Padhara
Raju Padhara

Reputation: 707

You can also use hash table (temporary table).

DECLARE @RUNS INT
DECLARE @WON INT
DECLARE @PERC FLOAT

SELECT * INTO #TEMP FROM tvf_horse_going('Allow Me', 'HEAVY')

SET @RUNS = (SELECT COUNT(*) FROM #TEMP)
SET @WON = (SELECT COUNT(*) FROM #TEMP WHERE PosNum = '1')
SET @PERC = (@WON * 100) / @RUNS

SELECT @RUNS As Runs, @WON As Won, @PERC As Perc

Upvotes: 1

roman
roman

Reputation: 117337

select
    @RUNS = count(*),
    @WON = sum(case when PosNum = 1 then 1 else 0 end)
from tvf_horse_going('Allow Me', 'HEAVY')

set @PERC = (@WON * 100) / @RUNS

Upvotes: 1

gvee
gvee

Reputation: 17161

SELECT runs
     , won
     , won * 100.0 / runs As perc
FROM   (
        SELECT Count(*) As runs
             , Sum(CASE WHEN PosNum = 1 THEN 1 ELSE 0 END) As won
        FROM   tvf_horse_going('Allow Me', 'HEAVY')
       ) As results

Note the * 100.0. This is a quick and easy way to avoid using integer math. Other [less lazy] methods would include: Cast(won As decimal(5,2)) / Cast(runs As decimal(5,2)) or similar

Upvotes: 1

Related Questions