Sql – pass parameter in table valued function using select statement

sqlsql serversql-functionsql-server-2008tsql

I have created a table valued return function which returns me a table . here is call of my function as follow

SELECT * FROM dbo.[StateFixedTaxesCalculation](3020,16,1,1006)

and its working OK for me , now i want to use this function call in a select statment , so i can pass 16 which is basically employeeId dynamically.

So i have decided to use inner join with table returned by that function . Like this

SELECT * FROM Employee as E
INNER JOIN  dbo.[StateFixedTaxesCalculation](3020,16,1,1006) as TC   ON TC.EmployeeId=E.EmployeeId

but now how can i pass 16 as dynamic value of all employeeId one by one .

Best Answer

use outer/cross apply:

select *
from Employee as E
    cross apply dbo.[StateFixedTaxesCalculation](3020, E.EmployeeId, 1, 1006) as TC

if you still have to filter by TC.EmployeeId = E.EmployeeId, you can do this with subquery:

select *
from Employee as E
    cross apply (
        select TT.*
        from dbo.[StateFixedTaxesCalculation](3020, E.EmployeeId, 1, 1006) as TT
        where TT.EmployeeId = E.EmployeeId
    ) as TC

Related Solutions

Sql – Exclude a column using SELECT * [except columnA] FROM tableA

You can try it this way:

/* Get the data into a temp table */
SELECT * INTO #TempTable
FROM YourTable
/* Drop the columns that are not needed */
ALTER TABLE #TempTable
DROP COLUMN ColumnToDrop
/* Get results and drop temp table */
SELECT * FROM #TempTable
DROP TABLE #TempTable

Sql-server – Update a table using JOIN in SQL Server

You don't quite have SQL Server's proprietary UPDATE FROM syntax down. Also not sure why you needed to join on the CommonField and also filter on it afterward. Try this:

UPDATE t1
  SET t1.CalculatedColumn = t2.[Calculated Column]
  FROM dbo.Table1 AS t1
  INNER JOIN dbo.Table2 AS t2
  ON t1.CommonField = t2.[Common Field]
  WHERE t1.BatchNo = '110';

If you're doing something really silly - like constantly trying to set the value of one column to the aggregate of another column (which violates the principle of avoiding storing redundant data), you can use a CTE (common table expression) - see here and here for more details:

;WITH t2 AS
(
  SELECT [key], CalculatedColumn = SUM(some_column)
    FROM dbo.table2
    GROUP BY [key]
)
UPDATE t1
  SET t1.CalculatedColumn = t2.CalculatedColumn
  FROM dbo.table1 AS t1
  INNER JOIN t2
  ON t1.[key] = t2.[key];

The reason this is really silly, is that you're going to have to re-run this entire update every single time any row in table2 changes. A SUM is something you can always calculate at runtime and, in doing so, never have to worry that the result is stale.

Best Answer

Related Solutions

Sql – Exclude a column using SELECT * [except columnA] FROM tableA

Sql-server – Update a table using JOIN in SQL Server

Related Topic