I am trying to pass a column name and value to a SQL function so that I can call the function and get the results I need. So far I have been unsuccessful.
The call would be something like
SELECT * FROM myfunction('THISCOLUMN','0082')
I've tried a couple of ways without success like
CREATE FUNCTION [dbo].[myfunction] (@psdcolumn varchar(10), @vall varchar(5))
RETURNS @RESULT TABLE
ACTUAL_HOURS numeric(11, 0)
-- there is code that does all this prep work, but the result is something like these values
SET @hmsdate = '20130731'
SET @newPrj = '1'
-- The last part with the "AND @psdcolumn=thisvalue" is where I'm stuck
SET @actualHours = (SELECT SUM(PSD_MIN)/60 FROM PSLINES LEFT JOIN PSDETAIL ON PSD_PSL=PSL_KEY WHERE PSL_PRJ=@newPrj AND PSD_DATE <= @hmsdate AND @psdcolumn=thisvalue)
INSERT INTO @RESULT VALUES (@val1, @actualHours)
-- My goal is to return a TABLE so I thought the function would be the easiest but I am open to other suggestions if this is the wrong way to go at it.