SQL SERVER CAL FUNCTION IN IN CLAUSE -
i have function returns set of itemcodes. result correct when call in stored procedure doesn't show anything:
declare @itemgroupcode int set @itemgroupcode=118 declare @todate datetime set @todate='15.april.15' declare @group nvarchar(max) set @group=(select [dbo].[currproduction] (118,'15.april.2015')) select @group select * ( select o.itemcode [item],tm.itemname dscription,o.warehouse wr,obtn.lotnumber, itl1.quantity qty ,tm.u_reusb,@group grp oinm o inner join oitl on oitl.doctype = o .transtype , oitl.docnum = o.base_ref , oitl.docline= o .doclinenum , oitl .loccode = o.warehouse , o .itemcode = oitl.itemcode inner join itl1 on itl1.logentry = oitl.logentry , itl1.itemcode=oitl.itemcode inner join oitm tm on tm.itemcode = itl1.itemcode inner join oitb on tm .itmsgrpcod = oitb .itmsgrpcod inner join owhs oh on o.warehouse=oh.whscode inner join obtn on obtn.sysnumber = itl1.sysnumber , obtn.itemcode = itl1.itemcode , tm.manbtchnum = 'y' --tm.itmsgrpcod=108 , tm.itemcode in (@group) , o.docdate<=@todate ) s pivot ( sum(qty) wr in ( [dis],[g&i],[gd-001],[gd-002],[gd-003],[gd-004], [gd-005],[gd-006],[gd-007],[gd-008],[gd-009],[gd-010], [gd-011],[gd-012],[gd-013],[gd-014],[gd-015],[gd-016],[gd-017],[gd-018], [gd-019],[gd-020],[gd-021],[gd-022],[gd-023],[gd-024],[gd-025],[gd-026], [gd-027],[gd-028],[gd-029],[gd-030],[gd-031],[gd-032],[gd-033],[gd-034],[gd-035], [khi ab],[khi mm],[loan],[lub],[main],[pcg - m1],[pcg - m2], [phs],[pmg],[pro],[rej],[rep],[rep-v],[selling],[skp] ) )as pvt the function returns following when paste in in clause gives me correct result, when pass in result of function call, doesn't show anything
function returns:
'fyct-00063','fycm-00016','fycm-00064','fycf-00018','fycm-00021','fyog-00016','fycm-00004','fycm-00031','fycm-00042' function code following. ** alter function [dbo].[currproduction] ( @unit varchar(10), @date datetime ) returns varchar(max) begin -- declare return variable here declare @resultvar varchar(max) select distinct @resultvar='''' + replace(stuff(( select ',',+cast(owor.itemcode varchar(10)) [text()] owor owor.postdate=@date , owor.u_unit=case when @unit=108 'unit no1' when @unit=118 'unit no 2' when @unit=119 'unit no 3' end , owor.status!='c' xml path('') , type) .value('.','nvarchar(max)'),1,1,' '),',',''',''')+'''' -- return result of function return @resultvar end **
in order want function should inline table valued function. can call in in. example:
create table ttinline(id int) go insert dbo.ttinline values (1),(2),(3) create function fninline() returns table return select * dbo.ttinline go select * dbo.ttinline id in(select * dbo.fninline()) output:
id 1 2 3 edit:
alter function [dbo].[currproduction] ( @unit varchar(10), @date datetime ) returns table return select distinct cast(owor.itemcode varchar(10)) code owor owor.postdate=@date , owor.u_unit=case when @unit=108 'unit no1' when @unit=118 'unit no 2' when @unit=119 'unit no 3' end , owor.status!='c' go declare @itemgroupcode int set @itemgroupcode = 118 declare @todate datetime set @todate = '15.april.15' select * ( select o.itemcode [item] , tm.itemname dscription , o.warehouse wr , obtn.lotnumber , itl1.quantity qty , tm.u_reusb oinm o inner join oitl on oitl.doctype = o.transtype , oitl.docnum = o.base_ref , oitl.docline = o.doclinenum , oitl.loccode = o.warehouse , o.itemcode = oitl.itemcode inner join itl1 on itl1.logentry = oitl.logentry , itl1.itemcode = oitl.itemcode inner join oitm tm on tm.itemcode = itl1.itemcode inner join oitb on tm.itmsgrpcod = oitb.itmsgrpcod inner join owhs oh on o.warehouse = oh.whscode inner join obtn on obtn.sysnumber = itl1.sysnumber , obtn.itemcode = itl1.itemcode , tm.manbtchnum = 'y' --tm.itmsgrpcod=108 , tm.itemcode in ( select code [dbo].[currproduction](118, '15.april.2015') ) , o.docdate <= @todate ) s pivot ( sum(qty) wr in ( [dis], [g&i], [gd-001], [gd-002], [gd-003], [gd-004], [gd-005], [gd-006], [gd-007], [gd-008], [gd-009], [gd-010], [gd-011], [gd-012], [gd-013], [gd-014], [gd-015], [gd-016], [gd-017], [gd-018], [gd-019], [gd-020], [gd-021], [gd-022], [gd-023], [gd-024], [gd-025], [gd-026], [gd-027], [gd-028], [gd-029], [gd-030], [gd-031], [gd-032], [gd-033], [gd-034], [gd-035], [khi ab], [khi mm], [loan], [lub], [main], [pcg - m1], [pcg - m2], [phs], [pmg], [pro], [rej], [rep], [rep-v], [selling], [skp] ) )as pvt