YVS1102 YVS1102 - 15 days ago 5
SQL Question

Codeigniter with Temp Table SQL

I'm trying to create a query with a

Stored Procedure
. I'm able to create it and there is no problem when i running it. Here is My StoredProcedure

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE SP_Penilaian
@Nip varchar(50)
AS
BEGIN

select * INTO #tMP from historyposition where nip = ''+@Nip+'' order by approveddate desc

declare @NewPosition varchar(50), @NewPositionLast varchar(50), @ApproveDate datetime, @ApproveDateLast datetime
select top 1 @NewPositionLast = NewPosition, @ApproveDateLast=ApprovedDate from #tmp
WHILE EXISTS (SELECT * FROM #tMP)
BEGIN
select top 1 @NewPosition = NewPosition, @ApproveDate=ApprovedDate from #tmp
if (@NewPosition = @NewPositionLast)
begin
set @NewPositionLast = @NewPosition
set @ApproveDateLast = @ApproveDate
end
else
begin
break
end
delete top(1) #tMP
END
select @NewPositionLast 'LatesUpgradePosition' , @ApproveDateLast 'LatesUpgradeDate'
END
GO


this how i run it

sp_penilaian '1500060'


And here is the resullt

enter image description here

Ok. The problem is when i'm trying to call my SP in Codeigniter.

$nip = '1500060';
$filt_outlet = $this->db->query("SP_Penilaian '".$nip."'")->row();


and then i'm trying to check my data with this

echo "<pre>".print_r($filt_outlet)."</pre>";


But i get empty data. So how to use temp table with CodeIgniter? or if i can't use temp table can i just create table and drop it in my Stored Procedure?

Sorry for my bad english.

Answer

Try Add SET NOCOUNT ON; after BEGIN

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE SP_Penilaian
    @Nip varchar(50)
AS
BEGIN

SET NOCOUNT ON; //<--Add here

    select * INTO #tMP from historyposition where nip = ''+@Nip+'' order by approveddate desc

declare @NewPosition varchar(50), @NewPositionLast varchar(50), @ApproveDate datetime, @ApproveDateLast datetime
select top 1 @NewPositionLast = NewPosition, @ApproveDateLast=ApprovedDate from #tmp
WHILE EXISTS (SELECT * FROM #tMP)
BEGIN
    select top 1 @NewPosition = NewPosition, @ApproveDate=ApprovedDate from #tmp
    if (@NewPosition = @NewPositionLast)
    begin 
        set @NewPositionLast = @NewPosition 
        set @ApproveDateLast = @ApproveDate
    end 
    else
    begin
        break
    end
    delete top(1) #tMP
END
select  @NewPositionLast 'LatesUpgradePosition' , @ApproveDateLast 'LatesUpgradeDate'
END
GO