CODEWITHSUNDEEP
sql-serversql-function
Umer Waheed
Umer Waheed

Reputation: 4584

SQL Server : function return column values that can be used in where clause?

I want to fetch record from table with WHERE IN clause like this:

Select * 
From Table_A
Where table_id IN (Query X that returns multiple or single id)

Now I want to move X query to a SQL Server function because it will use multiple places and want to call query like that:

Select * 
From Table_A
Where table_id IN dbo.function(parameters)

Is it possible to do this? If yes then which will be the type of function (scalar or table function)?

Upvotes: 2

Views: 8160

Answers (2)

mordechai
mordechai

Reputation: 851

Try this:

CREATE FUNCTION fncName (@params ...)
RETURNS TABLE
AS
RETURN  SELECT id FROM tableName WHERE @params...
go
SELECT * FROM query WHERE id IN (SELECT id FROM fncName(@params...))

Upvotes: 0

Chanukya
Chanukya

Reputation: 5893

i think in this way u need to pass 

CREATE FUNCTION function_name(@inputparams ...)
RETURNS TABLE
AS
RETURN  SELECT id FROM table WHERE @inputparams...
go
    select * 
    FROM    table
    WHERE   table_id IN ( SELECT column_name FROM function_name(inputparametes) )

Upvotes: 4

Related Questions