I am selecting values to one table and populating them in another table, and I'm using a case statement to check if the word is the string 'NULL' then I replace it with value null.
e.g CASE WHEN [Gross_Amount] = 'NULL' THEN NULL ELSE CAST([Gross_Amount] AS DECIMAL(18,2)) END[Gross_Amount]
So I'm thinking I can write a function that can check to see the value if it is a string NULL, if so replace it with null or return Gross_Amount if not. and rewrite that whole CASE statement, to objective here is to minimise the lines of code.
no need. better to keep it inline rather than making it a scalar UDF as scalr UDFs used over larger datasets can cause performance issues as they'll get expanded and executed once for each row in resultset
if you want you can simplify case when expression as follows