iterate through table valued function in stored procedure

Question

Hi,   I have written a sp which inserts certain data. Am calling table valued function inside that sp for one field(assume for date field) .Since table valued function is returning multiple values insert statement is terminating saying that the subquery returning more than one value.  Can I get some suggestions asap for iterating the function values inside the sp itself..   Regards

Rafnas T P · Answer

try this add one colum for rownomber in temprary table which is auto incremented as below ROWNUM int identity(00,01)    then   declare  @id  int ;   declare  @tcount  int ;   declare  @rown  int ;   set  @tcount= select   count (*)  from  #tempDateTable;     set  @rown=1;   while (@tcount>0)   BEGIN     set  @id= select   Max (id)  from  Employee    INSERT   INTO  Employee   (id, name )      (   Select  @id,( select  temptablename  from  #tempDateTable  where  ROWNUM=@rown    )
SET @tcount = @tcount - 1 ;
set  @rown=@rown+1;   end

Nitin Sontakke · Answer

If such is the case as @Rafnas is suggesting, I highly recommend following syntax:   insert   into  [Employee] ([id], [ name ])   select  @id, td.[ column ]   from  #tempDateTable td   where

Rafnas T P · Answer

hi Ajay Mavatkar, I think in your query you need give where condition like below (id,name) ( Select @id, (SELECT * FROM #tempDateTable where ), name )  something like   SELECT * FROM #tempDateTable where tempempid=id

Nitin Sontakke · Answer

Just google 'cursor example sql server' and you will get loads of examples.

Ajay Mavatkar · Answer

INSERT INTO Employee (id,name) ( Select @id, (SELECT * FROM #tempDateTable), name )   Here #tempDateTable is temp table which holds TVF data. Can you show me how to iterate using cursor, I dont knw much abt cursor?

Nitin Sontakke · Answer

Please share code, if possible. One of the approaches I follow is question the solution itself. What exactly are you trying to achieve. How is it that your TVF returning several values of which you want use only one.    You can iterate through values by using cursor.