2

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)?

jjj
  • 1,136
  • 3
  • 18
  • 28
Umer Waheed
  • 4,044
  • 7
  • 41
  • 62
  • Yes it's possible but I think it's better done with a join. Subselects can be real performance-killers for SQL-server. – Pieter B Feb 09 '17 at 09:29

2 Answers2

4

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) )
Chanukya
  • 5,833
  • 1
  • 22
  • 36
0

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...))
arghtype
  • 4,376
  • 11
  • 45
  • 60
mordechai
  • 829
  • 1
  • 7
  • 23
  • Welcome to stack overflow :-) Please look at [answer]. You should provide some information why your code solves the problem. Code-only answers aren't useful for the community. – JimHawkins Feb 09 '17 at 09:26