Honestly, the function posted is fraught with issues. There is all kinds of casting/converting both explicitly and implicitly.
If we are going to demonstrate how to do somebody's homework we should actually go to the next level and so them the right way to do this. Scalar functions are notoriously poor regarding performance. Instead of a scalar function converting this to an Inline Table Valued Function (iTVF) would be orders of magnitude better than the scalar function proposed.
Something like this.
CREATE FUNCTION ODD_or_Even
RETURNS table with schemabinding
select case when @Digit % 2 = 0 then 1 else 0 end as IsEven
OK. So now we have a function but so what? We could
easily create a select statement and stick that after a create procedure. That would fulfill the requirements but misses the point entirely. How about if we create a stored proc that will return the numbers from 1 to x and state if each of them is Odd or Even. This is easy now that we made our function an iTVF instead of a scalar.
create procedure GetOddOrEvenValues
E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
from cteTally t
cross apply dbo.ODD_or_Even(t.N)
where t.N <= @NumResults
Alright. Now we have a stored proc that is utilizing a function.
All that is left is to call our newly created stored proc.
exec GetOddOrEvenValues 123 --change the 123 to any positive number and it will return that many rows.
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/
for best practices on asking questions.
Need to split a string? Try Jeff Moden's splitter
.Cross Tabs and Pivots, Part 1 – Converting Rows to Columns Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs Understanding and Using APPLY (Part 1)Understanding and Using APPLY (Part 2)