none
use stored procedure in scalar value function

    Question

  • hi
    i don't know english very weel sorry about that and i want to ask a questions..

    i have a stored procedure and a scalar valued function.. i use stored procedure in SVF.. i use mssql 2005..

    dbo.defdayekle --> my stored procedure and that's code below

    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    go


    ALTER PROCEDURE [dbo].[defdayekle]
    @fisno int,
    @sira int,
    @birim int,
    @alyil int,
    @hesapno varchar(100),
    @sicilno varchar(100),
    @obirim varchar(100),
    @demir_id int,
    @stokadi varchar(100),
    @tarih smalldatetime,
    @cesit varchar(100),
    @firma varchar(100),
    @bfiyati varchar(100),
    @kdv int,
    @defdayid int,
    @toplam int,
    @miktar decimal,
    @giris decimal,
    @durum int output

    AS
    BEGIN
    begin try

    insert into tasdaydef (ıd,fısno,sıra,bırım,alyıl,hesapno,sıcılno,obırım,demır_ıd,stokadı,tarıh,cesıt,fırma,bfıyatı,mıktar,kdv,gırıs)
    values (@defdayid,@fisno,@sira,@birim,@alyil,@hesapno,@sicilno,@obirim,@demir_id,@stokadi,@tarih,@cesit,@firma,@bfiyati,@miktar,@kdv,@giris)
    set @durum=1

    end try 
    begin catch

    set @durum=0

    end catch
    return @durum
    END

    dbo.TasDayDefteBulunmayanKayitlariEkle--> my SVF and code below

    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    go

    ALTER function [dbo].[TasDayDefteBulunmayanKayitlariEkle] (@id int) returns int
    WITH EXECUTE AS CALLER
    AS
    BEGIN

    declare @urunadi varchar(50)
    declare @idd int
    declare @tasid int
    declare @fisno int
    declare @sira int
    declare @birim int
    declare @alyil int
    declare @hesapno varchar(100)
    declare @sicilno varchar(100)
    declare @obirim varchar(100)
    declare @demir_id int
    declare @stokadi varchar(100)
    declare @tarih smalldatetime
    declare @cesit varchar(100)
    declare @firma varchar(100)
    declare @bfiyati varchar(100)
    declare @kdv int
    declare @defdayid int
    declare @toplam int
    declare @miktar decimal
    declare @giris decimal

    set @toplam=0
    declare c cursor for SELECT DISTINCT dbo.TASDET08.ID
    FROM         dbo.TASDET08 LEFT OUTER JOIN
                          dbo.TASDAYDEF ON dbo.TASDET08.DEFDAY_ID = dbo.TASDAYDEF.ID
    WHERE     (NOT (dbo.TASDET08.HESAPNO LIKE '150%')) AND (tasdaydef.ıd IS NULL) AND (dbo.TASDET08.SICILNO IS NOT NULL) AND (TASDET08.DEFDAY_ID!=0)
    open c

    fetch next from c into @idd

    while @@fetch_status=0 begin 
    set @toplam=@toplam+1
    select @tasid=(select tas_ıd from tasdet08 where ıd=@idd)
    select @fisno=(select fısno from tasdet08 where ıd=@idd)
    select @sira=(select sıra from tasdet08 where ıd=@idd)
    select @birim=(select bırım from tasdet08 where ıd=@idd)
    select @alyil=(select substring( convert(varchar(10),tarıh,104),7,4) from tas08 where ıd=@tasid)
    select @hesapno=(select hesapno from tasdet08 where ıd=@idd)
    select @sicilno=(select sıcılno from tasdet08 where ıd=@idd)
    select @obirim=(select obırım from tasdet08 where ıd=@idd)
    select @demir_id=(select demır_ıd from tasdet08 where ıd=@idd)
    select @stokadi=(select stokadı from tasdet08 where ıd=@idd)
    select @tarih=(select tarıh from tas08 where ıd=@tasid)
    select @cesit=(select cesıt from tas08 where ıd=@tasid)
    select @firma=(select fırma from tas08 where ıd=@tasid)
    select @bfiyati=(select bfıyatı from tasdet08 where ıd=@idd)
    select @miktar=(select mıktar from tasdet08 where ıd=@idd)
    select @kdv=(select kdv from tasdet08 where ıd=@idd)
    select @defdayid=(select defday_ıd from tasdet08 where ıd=@idd)
    set @giris=1
    set @toplam = @toplam + (select dbo.defdayekle(@defdayid,@fisno,@sira,@birim,@alyil,@hesapno,@sicilno,@obirim,@demir_id,@stokadi,@tarih,@cesit,@firma,@bfiyati,@miktar,@kdv,@giris) as deger)
    fetch next from c into @idd
    end 
    close c
    Deallocate c
    return @toplam
    END

    and when i execute below code

    select dbo.TasDayDefteBulunmayanKayitlariEkle(213) as deger

    mssql gives me orror

    "Cannot find either column "dbo" or the user-defined function or aggregate "dbo.defdayekle", or the name is ambiguous."

    i can't solve that please help me

    thanks
    Wednesday, December 09, 2009 9:25 AM

Answers

  • You cannot change the database from a function.

    I changed it to a nested stored procedures structure. Let us know if works.

    CREATE PROCEDURE [dbo].[defdayekle]
    @fisno int,
    @sira int,
    @birim int,
    @alyil int,
    @hesapno varchar(100),
    @sicilno varchar(100),
    @obirim varchar(100),
    @demir_id int,
    @stokadi varchar(100),
    @tarih smalldatetime,
    @cesit varchar(100),
    @firma varchar(100),
    @bfiyati varchar(100),
    @kdv int,
    @defdayid int,
    @toplam int,
    @miktar decimal,
    @giris decimal,
    @pdurum int output


    AS
    BEGIN
    DECLARE @durum int
    begin try


    insert into tasdaydef (ıd,fısno,sıra,bırım,alyıl,hesapno,sıcılno,obırım,demır_ıd,stokadı,tarıh,cesıt,fırma,bfıyatı,mıktar,kdv,gırıs)
    values (@defdayid,@fisno,@sira,@birim,@alyil,@hesapno,@sicilno,@obirim,@demir_id,@stokadi,@tarih,@cesit,@firma,@bfiyati,@miktar,@kdv,@giris)
    set @durum=1

    end try
    begin catch


    set @durum=0


    end catch
    set @pDurum= @durum
    END
    GO

    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    GO


    CREATE PROC [dbo].[TasDayDefteBulunmayanKayitlariEkle] @id int, @pToplam int OUTPUT
    AS
    BEGIN


    declare @urunadi varchar(50)
    declare @idd int
    declare @tasid int
    declare @fisno int
    declare @sira int
    declare @birim int
    declare @alyil int
    declare @hesapno varchar(100)
    declare @sicilno varchar(100)
    declare @obirim varchar(100)
    declare @demir_id int
    declare @stokadi varchar(100)
    declare @tarih smalldatetime
    declare @cesit varchar(100)
    declare @firma varchar(100)
    declare @bfiyati varchar(100)
    declare @kdv int
    declare @defdayid int
    declare @toplam int
    declare @miktar decimal
    declare @giris decimal
    declare @pDurum int


    set @toplam=0
    declare c cursor for SELECT DISTINCT dbo.TASDET08.ID
    FROM         dbo.TASDET08 LEFT OUTER JOIN
                          dbo.TASDAYDEF ON dbo.TASDET08.DEFDAY_ID = dbo.TASDAYDEF.ID
    WHERE     (NOT (dbo.TASDET08.HESAPNO LIKE '150%')) AND (tasdaydef.ıd IS NULL) AND (dbo.TASDET08.SICILNO IS NOT NULL) AND (TASDET08.DEFDAY_ID!=0)
    open c


    fetch next from c into @idd


    while @@fetch_status=0 begin
    set @toplam=@toplam+1
    select @tasid=(select tas_ıd from tasdet08 where ıd=@idd)
    select @fisno=(select fısno from tasdet08 where ıd=@idd)
    select @sira=(select sıra from tasdet08 where ıd=@idd)
    select @birim=(select bırım from tasdet08 where ıd=@idd)
    select @alyil=(select substring( convert(varchar(10),tarıh,104),7,4) from tas08 where ıd=@tasid)
    select @hesapno=(select hesapno from tasdet08 where ıd=@idd)
    select @sicilno=(select sıcılno from tasdet08 where ıd=@idd)
    select @obirim=(select obırım from tasdet08 where ıd=@idd)
    select @demir_id=(select demır_ıd from tasdet08 where ıd=@idd)
    select @stokadi=(select stokadı from tasdet08 where ıd=@idd)
    select @tarih=(select tarıh from tas08 where ıd=@tasid)
    select @cesit=(select cesıt from tas08 where ıd=@tasid)
    select @firma=(select fırma from tas08 where ıd=@tasid)
    select @bfiyati=(select bfıyatı from tasdet08 where ıd=@idd)
    select @miktar=(select mıktar from tasdet08 where ıd=@idd)
    select @kdv=(select kdv from tasdet08 where ıd=@idd)
    select @defdayid=(select defday_ıd from tasdet08 where ıd=@idd)
    set @giris=1


    exec  dbo.defdayekle @defdayid,@fisno,@sira,@birim,@alyil,@hesapno,@sicilno,@obirim,@demir_id,@stokadi,@tarih,@cesit,@firma,@bfiyati,@miktar,@kdv,@giris, @pDurum OUTPUT
    set @toplam = @toplam + @pDurum
    fetch next from c into @idd
    end
    close c
    Deallocate c
    set @pToplam=@toplam
    END
    GO
    -- Execute stored procedure
    declare @pToplam int
    exec dbo.TasDayDefteBulunmayanKayitlariEkle 213, @pToplam output
    SELECT @pToplam



    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    • Marked as answer by neurocranium Wednesday, December 09, 2009 10:38 AM
    Wednesday, December 09, 2009 10:21 AM
  • thanks a lot.. can you explain to me what's problem? it is "You cannot change the database from a function"?

    Very important distinction between stored procedures and functions.

    BOL: "The following statements are valid in a function:
    • Assignment statements.
    • Control-of-Flow statements except TRY...CATCH statements.
    • DECLARE statements defining local data variables and local cursors.
    • SELECT statements that contain select lists with expressions that assign values to local variables.
    • Cursor operations referencing local cursors that are declared, opened, closed, and deallocated in the function. Only FETCH statements that assign values to local variables using the INTO clause are allowed; FETCH statements that return data to the client are not allowed.
    • INSERT, UPDATE, and DELETE statements modifying local table variables.

    • EXECUTE statements calling extended stored procedures.
      "

    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    Wednesday, December 09, 2009 11:15 AM

All replies

  • I just added a couple of GO-s to form batches for compiles. Can you retest it?

    ALTER PROCEDURE [dbo].[defdayekle]
    @fisno int,
    @sira int,
    @birim int,
    @alyil int,
    @hesapno varchar(100),
    @sicilno varchar(100),
    @obirim varchar(100),
    @demir_id int,
    @stokadi varchar(100),
    @tarih smalldatetime,
    @cesit varchar(100),
    @firma varchar(100),
    @bfiyati varchar(100),
    @kdv int,
    @defdayid int,
    @toplam int,
    @miktar decimal,
    @giris decimal,
    @durum int output
    
    
    AS
    BEGIN
    begin try
    
    
    insert into tasdaydef (ıd,fısno,sıra,bırım,alyıl,hesapno,sıcılno,obırım,demır_ıd,stokadı,tarıh,cesıt,fırma,bfıyatı,mıktar,kdv,gırıs)
    values (@defdayid,@fisno,@sira,@birim,@alyil,@hesapno,@sicilno,@obirim,@demir_id,@stokadi,@tarih,@cesit,@firma,@bfiyati,@miktar,@kdv,@giris)
    set @durum=1
    
    
    end try 
    begin catch
    
    
    set @durum=0
    
    
    end catch
    return @durum
    END
    GO
    
    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    GO
    
    
    ALTER function [dbo].[TasDayDefteBulunmayanKayitlariEkle] (@id int) returns int
    WITH EXECUTE AS CALLER
    AS
    BEGIN
    
    
    declare @urunadi varchar(50)
    declare @idd int
    declare @tasid int
    declare @fisno int
    declare @sira int
    declare @birim int
    declare @alyil int
    declare @hesapno varchar(100)
    declare @sicilno varchar(100)
    declare @obirim varchar(100)
    declare @demir_id int
    declare @stokadi varchar(100)
    declare @tarih smalldatetime
    declare @cesit varchar(100)
    declare @firma varchar(100)
    declare @bfiyati varchar(100)
    declare @kdv int
    declare @defdayid int
    declare @toplam int
    declare @miktar decimal
    declare @giris decimal
    
    
    set @toplam=0
    declare c cursor for SELECT DISTINCT dbo.TASDET08.ID
    FROM         dbo.TASDET08 LEFT OUTER JOIN
                          dbo.TASDAYDEF ON dbo.TASDET08.DEFDAY_ID = dbo.TASDAYDEF.ID
    WHERE     (NOT (dbo.TASDET08.HESAPNO LIKE '150%')) AND (tasdaydef.ıd IS NULL) AND (dbo.TASDET08.SICILNO IS NOT NULL) AND (TASDET08.DEFDAY_ID!=0)
    open c
    
    
    fetch next from c into @idd
    
    
    while @@fetch_status=0 begin 
    set @toplam=@toplam+1
    select @tasid=(select tas_ıd from tasdet08 where ıd=@idd)
    select @fisno=(select fısno from tasdet08 where ıd=@idd)
    select @sira=(select sıra from tasdet08 where ıd=@idd)
    select @birim=(select bırım from tasdet08 where ıd=@idd)
    select @alyil=(select substring( convert(varchar(10),tarıh,104),7,4) from tas08 where ıd=@tasid)
    select @hesapno=(select hesapno from tasdet08 where ıd=@idd)
    select @sicilno=(select sıcılno from tasdet08 where ıd=@idd)
    select @obirim=(select obırım from tasdet08 where ıd=@idd)
    select @demir_id=(select demır_ıd from tasdet08 where ıd=@idd)
    select @stokadi=(select stokadı from tasdet08 where ıd=@idd)
    select @tarih=(select tarıh from tas08 where ıd=@tasid)
    select @cesit=(select cesıt from tas08 where ıd=@tasid)
    select @firma=(select fırma from tas08 where ıd=@tasid)
    select @bfiyati=(select bfıyatı from tasdet08 where ıd=@idd)
    select @miktar=(select mıktar from tasdet08 where ıd=@idd)
    select @kdv=(select kdv from tasdet08 where ıd=@idd)
    select @defdayid=(select defday_ıd from tasdet08 where ıd=@idd)
    set @giris=1
    set @toplam = @toplam + (select dbo.defdayekle(@defdayid,@fisno,@sira,@birim,@alyil,@hesapno,@sicilno,@obirim,@demir_id,@stokadi,@tarih,@cesit,@firma,@bfiyati,@miktar,@kdv,@giris) as deger)
    fetch next from c into @idd
    end 
    close c
    Deallocate c
    return @toplam
    END
    GO
    
    select dbo.TasDayDefteBulunmayanKayitlariEkle(213) as deger

    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    Wednesday, December 09, 2009 9:32 AM
  • thanks for reply

    unfortunately it gives same error
    Wednesday, December 09, 2009 9:36 AM
  • i read that article 


    and says 

    "Calling extended stored procedures from functions

    The extended stored procedure, when called from inside a function, cannot return result sets to the client. Any ODS APIs that return result sets to the client will return FAIL.  The extended stored procedure could connect back to Microsoft® SQL Server™; however, it should not attempt to join the same transaction as the function that invoked the extended stored procedure.

    Similar to invocations from a batch or stored procedure, the extended stored procedure will be executed in the context of the Windows® security account under which SQL Server is running. The owner of the stored procedure should consider this when giving EXECUTE privileges on it to users."

    do you think that problem about permissions or another security problem?
    Wednesday, December 09, 2009 9:45 AM
  • Hi,

    dbo.defdayekle is a procedure and it cannot be used as

    select dbo.defdayekle(@defdayid,@fisno,@sira,@birim,@alyil,@hesapno,@sicilno,@obirim,@demir_id,@stokadi,@tarih,@cesit,@firma,@bfiyati,@miktar,@kdv,@giris)
    Only functions can be used as column in the select list

    please change it to function.

    Thanks

    Gnanasekar Babu Note: Please click on the vote button if the answer helps you
    Wednesday, December 09, 2009 9:57 AM
  • There are 2 issues:

    1. You cannot call a stored procedure like this:

    set @toplam = @toplam + (select dbo.defdayekle(@defdayid,@fisno,@sira,@birim,@alyil,@hesapno,@sicilno,@obirim,@demir_id,@stokadi,@tarih,@cesit,@firma,@bfiyati,@miktar,@kdv,@giris) as deger)

    That is how you can call a scalar-valued function. You call a stored procedure like EXEC sprocAlpha.


    2. You cannot change the database within a function.

    The solution is probably 2 stored procedures.

    Let us know if helpful.
    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    Wednesday, December 09, 2009 9:58 AM
  • Hi,

    dbo.defdayekle is a procedure and it cannot be used as

    select dbo.defdayekle...

    please change it to function.

    Thanks

    Gnanasekar Babu Note: Please click on the vote button if the answer helps you
    when i try to insert a record in function it gives error about insert statement and i search that error code i see cannot insert a record in functions.. i must use store procedure
    Wednesday, December 09, 2009 10:02 AM
  • There are 2 issues:

    1. You cannot call a stored procedure like this:

    set @toplam = @toplam + (select dbo.defdayekle(@defdayid,@fisno,@sira,@birim,@alyil,@hesapno,@sicilno,@obirim,@demir_id,@stokadi,@tarih,@cesit,@firma,@bfiyati,@miktar,@kdv,@giris) as deger)<br/><br/>
    
    That is how you can call a scalar-valued function. You call a stored procedure like EXEC sprocAlpha.


    2. You cannot change the database within a function.

    The solution is probably 2 stored procedures.

    Let us know if helpful.
    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    stored procedure return value 1 or 0 and i sum that for how many record inserted.. how can i use stored procedure in function as such return value??

    if i not change database it gives 

    'defdayekle' is not a recognized built-in function name.

    error

    thanks for reply
    Wednesday, December 09, 2009 10:06 AM

  • Hi,

    Yes you cannot insert in the function when you try to select...

    Let us know your requirement..so that we test for alternative solution

    Thanks


    Gnanasekar Babu Note: Please click on the vote button if the answer helps you
    Wednesday, December 09, 2009 10:09 AM

  • Hi,

    Yes you cannot insert in the function when you try to select...

    Let us know your requirement..so that we test for alternative solution

    Thanks


    Gnanasekar Babu Note: Please click on the vote button if the answer helps you
    i want to insert 1631 records to another table.. and info in another 2 table.. 

    insert records in stored procedure and select info in functions 

    tasdet09 columns

    ID decimal(18, 0)
    TAS_ID decimal(18, 0)
    FISNO decimal(18, 0)
    DEFDAY_ID decimal(18, 0)
    BIRIM decimal(18, 0)
    CIKBIRIM decimal(18, 0)
    OBIRIM varchar(50)
    STOKADI varchar(100)
    DEMIR_ID decimal(18, 0)
    HID decimal(18, 0)
    HESAPNO varchar(30)
    GIRIS bit
    MIKTAR decimal(18, 2)
    KALAN decimal(18, 2)
    BFIYATI decimal(18, 6)
    KDV smallint
    TUTAR decimal(18, 2)
    KDVTUTAR decimal(18, 4)
    SICILNO varchar(50)
    SIRA decimal(18, 0)
    GECICI bit
    SIL bit
    GIPTAL bit
    IPTAL bit
    GONAY bit
    ONAY bit
    DEVIR bit
    GIRIS_ID decimal(18, 0)

    tas09 columns

    ID decimal(18, 0)
    BIRIM decimal(18, 0)
    FISNO decimal(18, 0)
    TARIH smalldatetime
    KABTAR smalldatetime
    KABNO varchar(20)
    FATTAR smalldatetime
    FATNO varchar(20)
    FIRMA_ID decimal(18, 0)
    GIRIS bit
    SIL bit
    IL varchar(30)
    ILKOD varchar(5)
    BRMAD varchar(100)
    TASHARKOD varchar(20)
    TASAMBKOD varchar(20)
    MUHBIRIMA varchar(100)
    MUHBIRIM varchar(10)
    CESIT varchar(50)
    FIRMA varchar(100)
    KIME varchar(100)
    CIKBIRIM decimal(18, 0)
    CIKBIRIMAD varchar(100)
    CIKBIRIMYER varchar(50)
    CIKBRHKOD varchar(50)
    HESAP varchar(3)
    GIRDON bit
    GGIRDON bit
    GIR_ID decimal(18, 0)
    KDVFARK decimal(18, 2)
    TUTARFARK decimal(18, 2)
    SICIL bit
    GECICI bit
    IPTAL bit
    GIPTAL bit
    GONAY bit
    KITAP bit
    ONAY bit
    TASYETKI char(35)
    TASUNVAN char(35)
    DEVIR bit
    and i want to insert that table 

    tasdaydef columns 

    ID decimal(18, 0)
    FISNO decimal(18, 0)
    SIRA decimal(18, 0)
    BIRIM decimal(18, 0)
    ALYIL smallint
    HESAPNO varchar(50)
    SICILNO varchar(50)
    OBIRIM varchar(30)
    DEMIR_ID decimal(18, 0)
    STOKADI varchar(100)
    TARIH smalldatetime
    CESIT varchar(50)
    FIRMA varchar(100)
    BFIYATI decimal(18, 2)
    MIKTAR decimal(18, 2)
    KDV decimal(18, 2)
    GIRIS bit

    and tas09.id=tasdet09.tas.id

    i must get some info from tas09,tasdet09

    i hope that information help for solution..

    thanks for reply
    Wednesday, December 09, 2009 10:19 AM
  • You cannot change the database from a function.

    I changed it to a nested stored procedures structure. Let us know if works.

    CREATE PROCEDURE [dbo].[defdayekle]
    @fisno int,
    @sira int,
    @birim int,
    @alyil int,
    @hesapno varchar(100),
    @sicilno varchar(100),
    @obirim varchar(100),
    @demir_id int,
    @stokadi varchar(100),
    @tarih smalldatetime,
    @cesit varchar(100),
    @firma varchar(100),
    @bfiyati varchar(100),
    @kdv int,
    @defdayid int,
    @toplam int,
    @miktar decimal,
    @giris decimal,
    @pdurum int output


    AS
    BEGIN
    DECLARE @durum int
    begin try


    insert into tasdaydef (ıd,fısno,sıra,bırım,alyıl,hesapno,sıcılno,obırım,demır_ıd,stokadı,tarıh,cesıt,fırma,bfıyatı,mıktar,kdv,gırıs)
    values (@defdayid,@fisno,@sira,@birim,@alyil,@hesapno,@sicilno,@obirim,@demir_id,@stokadi,@tarih,@cesit,@firma,@bfiyati,@miktar,@kdv,@giris)
    set @durum=1

    end try
    begin catch


    set @durum=0


    end catch
    set @pDurum= @durum
    END
    GO

    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    GO


    CREATE PROC [dbo].[TasDayDefteBulunmayanKayitlariEkle] @id int, @pToplam int OUTPUT
    AS
    BEGIN


    declare @urunadi varchar(50)
    declare @idd int
    declare @tasid int
    declare @fisno int
    declare @sira int
    declare @birim int
    declare @alyil int
    declare @hesapno varchar(100)
    declare @sicilno varchar(100)
    declare @obirim varchar(100)
    declare @demir_id int
    declare @stokadi varchar(100)
    declare @tarih smalldatetime
    declare @cesit varchar(100)
    declare @firma varchar(100)
    declare @bfiyati varchar(100)
    declare @kdv int
    declare @defdayid int
    declare @toplam int
    declare @miktar decimal
    declare @giris decimal
    declare @pDurum int


    set @toplam=0
    declare c cursor for SELECT DISTINCT dbo.TASDET08.ID
    FROM         dbo.TASDET08 LEFT OUTER JOIN
                          dbo.TASDAYDEF ON dbo.TASDET08.DEFDAY_ID = dbo.TASDAYDEF.ID
    WHERE     (NOT (dbo.TASDET08.HESAPNO LIKE '150%')) AND (tasdaydef.ıd IS NULL) AND (dbo.TASDET08.SICILNO IS NOT NULL) AND (TASDET08.DEFDAY_ID!=0)
    open c


    fetch next from c into @idd


    while @@fetch_status=0 begin
    set @toplam=@toplam+1
    select @tasid=(select tas_ıd from tasdet08 where ıd=@idd)
    select @fisno=(select fısno from tasdet08 where ıd=@idd)
    select @sira=(select sıra from tasdet08 where ıd=@idd)
    select @birim=(select bırım from tasdet08 where ıd=@idd)
    select @alyil=(select substring( convert(varchar(10),tarıh,104),7,4) from tas08 where ıd=@tasid)
    select @hesapno=(select hesapno from tasdet08 where ıd=@idd)
    select @sicilno=(select sıcılno from tasdet08 where ıd=@idd)
    select @obirim=(select obırım from tasdet08 where ıd=@idd)
    select @demir_id=(select demır_ıd from tasdet08 where ıd=@idd)
    select @stokadi=(select stokadı from tasdet08 where ıd=@idd)
    select @tarih=(select tarıh from tas08 where ıd=@tasid)
    select @cesit=(select cesıt from tas08 where ıd=@tasid)
    select @firma=(select fırma from tas08 where ıd=@tasid)
    select @bfiyati=(select bfıyatı from tasdet08 where ıd=@idd)
    select @miktar=(select mıktar from tasdet08 where ıd=@idd)
    select @kdv=(select kdv from tasdet08 where ıd=@idd)
    select @defdayid=(select defday_ıd from tasdet08 where ıd=@idd)
    set @giris=1


    exec  dbo.defdayekle @defdayid,@fisno,@sira,@birim,@alyil,@hesapno,@sicilno,@obirim,@demir_id,@stokadi,@tarih,@cesit,@firma,@bfiyati,@miktar,@kdv,@giris, @pDurum OUTPUT
    set @toplam = @toplam + @pDurum
    fetch next from c into @idd
    end
    close c
    Deallocate c
    set @pToplam=@toplam
    END
    GO
    -- Execute stored procedure
    declare @pToplam int
    exec dbo.TasDayDefteBulunmayanKayitlariEkle 213, @pToplam output
    SELECT @pToplam



    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    • Marked as answer by neurocranium Wednesday, December 09, 2009 10:38 AM
    Wednesday, December 09, 2009 10:21 AM
  • You cannot change the database from a function.

    I changed it to a nested stored procedures structure. Let us know if works.

    CREATE PROCEDURE [dbo].[defdayekle]
    @fisno int,
    @sira int,
    @birim int,
    @alyil int,
    @hesapno varchar(100),
    @sicilno varchar(100),
    @obirim varchar(100),
    @demir_id int,
    @stokadi varchar(100),
    @tarih smalldatetime,
    @cesit varchar(100),
    @firma varchar(100),
    @bfiyati varchar(100),
    @kdv int,
    @defdayid int,
    @toplam int,
    @miktar decimal,
    @giris decimal,
    @pdurum int output


    AS
    BEGIN
    DECLARE @durum int
    begin try


    insert into tasdaydef (ıd,fısno,sıra,bırım,alyıl,hesapno,sıcılno,obırım,demır_ıd,stokadı,tarıh,cesıt,fırma,bfıyatı,mıktar,kdv,gırıs)
    values (@defdayid,@fisno,@sira,@birim,@alyil,@hesapno,@sicilno,@obirim,@demir_id,@stokadi,@tarih,@cesit,@firma,@bfiyati,@miktar,@kdv,@giris)
    set @durum=1

    end try
    begin catch


    set @durum=0


    end catch
    set @pDurum= @durum
    END
    GO

    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    GO


    CREATE PROC [dbo].[TasDayDefteBulunmayanKayitlariEkle] @id int, @pToplam int OUTPUT
    AS
    BEGIN


    declare @urunadi varchar(50)
    declare @idd int
    declare @tasid int
    declare @fisno int
    declare @sira int
    declare @birim int
    declare @alyil int
    declare @hesapno varchar(100)
    declare @sicilno varchar(100)
    declare @obirim varchar(100)
    declare @demir_id int
    declare @stokadi varchar(100)
    declare @tarih smalldatetime
    declare @cesit varchar(100)
    declare @firma varchar(100)
    declare @bfiyati varchar(100)
    declare @kdv int
    declare @defdayid int
    declare @toplam int
    declare @miktar decimal
    declare @giris decimal
    declare @pDurum int


    set @toplam=0
    declare c cursor for SELECT DISTINCT dbo.TASDET08.ID
    FROM         dbo.TASDET08 LEFT OUTER JOIN
                          dbo.TASDAYDEF ON dbo.TASDET08.DEFDAY_ID = dbo.TASDAYDEF.ID
    WHERE     (NOT (dbo.TASDET08.HESAPNO LIKE '150%')) AND (tasdaydef.ıd IS NULL) AND (dbo.TASDET08.SICILNO IS NOT NULL) AND (TASDET08.DEFDAY_ID!=0)
    open c


    fetch next from c into @idd


    while @@fetch_status=0 begin
    set @toplam=@toplam+1
    select @tasid=(select tas_ıd from tasdet08 where ıd=@idd)
    select @fisno=(select fısno from tasdet08 where ıd=@idd)
    select @sira=(select sıra from tasdet08 where ıd=@idd)
    select @birim=(select bırım from tasdet08 where ıd=@idd)
    select @alyil=(select substring( convert(varchar(10),tarıh,104),7,4) from tas08 where ıd=@tasid)
    select @hesapno=(select hesapno from tasdet08 where ıd=@idd)
    select @sicilno=(select sıcılno from tasdet08 where ıd=@idd)
    select @obirim=(select obırım from tasdet08 where ıd=@idd)
    select @demir_id=(select demır_ıd from tasdet08 where ıd=@idd)
    select @stokadi=(select stokadı from tasdet08 where ıd=@idd)
    select @tarih=(select tarıh from tas08 where ıd=@tasid)
    select @cesit=(select cesıt from tas08 where ıd=@tasid)
    select @firma=(select fırma from tas08 where ıd=@tasid)
    select @bfiyati=(select bfıyatı from tasdet08 where ıd=@idd)
    select @miktar=(select mıktar from tasdet08 where ıd=@idd)
    select @kdv=(select kdv from tasdet08 where ıd=@idd)
    select @defdayid=(select defday_ıd from tasdet08 where ıd=@idd)
    set @giris=1


    exec  dbo.defdayekle @defdayid,@fisno,@sira,@birim,@alyil,@hesapno,@sicilno,@obirim,@demir_id,@stokadi,@tarih,@cesit,@firma,@bfiyati,@miktar,@kdv,@giris, @pDurum OUTPUT
    set @toplam = @toplam + @pDurum
    fetch next from c into @idd
    end
    close c
    Deallocate c
    set @pToplam=@toplam
    END
    GO
    -- Execute stored procedure
    declare @pToplam int
    exec dbo.TasDayDefteBulunmayanKayitlariEkle 213, @pToplam output
    SELECT @pToplam

     


    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    thanks a lot it works

    give another error through my fault i solve problem..

    thanks a lot.. can you explain to me what's problem? it is "You cannot change the database from a function"?
    Wednesday, December 09, 2009 10:38 AM
  • Hello,

    try this... 
    ALTER PROCEDURE [dbo].[defdayekle]
    @fisno int,
    @sira int,
    @birim int,
    @alyil int,
    @hesapno varchar(100),
    @sicilno varchar(100),
    @obirim varchar(100),
    @demir_id int,
    @stokadi varchar(100),
    @tarih smalldatetime,
    @cesit varchar(100),
    @firma varchar(100),
    @bfiyati varchar(100),
    @kdv int,
    @defdayid int,
    @toplam int,
    @miktar decimal,
    @giris decimal,
    @durum int output
    
    
    AS
    BEGIN
    begin try
    
    
    insert into tasdaydef (ıd,fısno,sıra,bırım,alyıl,hesapno,sıcılno,obırım,demır_ıd,stokadı,tarıh,cesıt,fırma,bfıyatı,mıktar,kdv,gırıs)
    values (@defdayid,@fisno,@sira,@birim,@alyil,@hesapno,@sicilno,@obirim,@demir_id,@stokadi,@tarih,@cesit,@firma,@bfiyati,@miktar,@kdv,@giris)
    set @durum=1
    
    
    end try 
    begin catch
    
    
    set @durum=0
    
    
    end catch
    SELECT @durum
    END
    GO
    
    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    GO
    
    
    ALTER function [dbo].[TasDayDefteBulunmayanKayitlariEkle] (@id int) returns int
    WITH EXECUTE AS CALLER
    AS
    BEGIN
    
    
    declare @urunadi varchar(50)
    declare @idd int
    declare @tasid int
    declare @fisno int
    declare @sira int
    declare @birim int
    declare @alyil int
    declare @hesapno varchar(100)
    declare @sicilno varchar(100)
    declare @obirim varchar(100)
    declare @demir_id int
    declare @stokadi varchar(100)
    declare @tarih smalldatetime
    declare @cesit varchar(100)
    declare @firma varchar(100)
    declare @bfiyati varchar(100)
    declare @kdv int
    declare @defdayid int
    declare @toplam int
    declare @miktar decimal
    declare @giris decimal
    
    DECLARE @Table TABLE (ID INT)
    DECLARE @Val INT
    
    set @toplam=0
    declare c cursor for SELECT DISTINCT dbo.TASDET08.ID
    FROM         dbo.TASDET08 LEFT OUTER JOIN
                          dbo.TASDAYDEF ON dbo.TASDET08.DEFDAY_ID = dbo.TASDAYDEF.ID
    WHERE     (NOT (dbo.TASDET08.HESAPNO LIKE '150%')) AND (tasdaydef.ıd IS NULL) AND (dbo.TASDET08.SICILNO IS NOT NULL) AND (TASDET08.DEFDAY_ID!=0)
    open c
    
    
    fetch next from c into @idd
    
    
    while @@fetch_status=0 begin 
    set @toplam=@toplam+1
    select @tasid=(select tas_ıd from tasdet08 where ıd=@idd)
    select @fisno=(select fısno from tasdet08 where ıd=@idd)
    select @sira=(select sıra from tasdet08 where ıd=@idd)
    select @birim=(select bırım from tasdet08 where ıd=@idd)
    select @alyil=(select substring( convert(varchar(10),tarıh,104),7,4) from tas08 where ıd=@tasid)
    select @hesapno=(select hesapno from tasdet08 where ıd=@idd)
    select @sicilno=(select sıcılno from tasdet08 where ıd=@idd)
    select @obirim=(select obırım from tasdet08 where ıd=@idd)
    select @demir_id=(select demır_ıd from tasdet08 where ıd=@idd)
    select @stokadi=(select stokadı from tasdet08 where ıd=@idd)
    select @tarih=(select tarıh from tas08 where ıd=@tasid)
    select @cesit=(select cesıt from tas08 where ıd=@tasid)
    select @firma=(select fırma from tas08 where ıd=@tasid)
    select @bfiyati=(select bfıyatı from tasdet08 where ıd=@idd)
    select @miktar=(select mıktar from tasdet08 where ıd=@idd)
    select @kdv=(select kdv from tasdet08 where ıd=@idd)
    select @defdayid=(select defday_ıd from tasdet08 where ıd=@idd)
    set @giris=1
    
    INSERT INTO @Table 
    EXEC dbo.defdayekle @defdayid,@fisno,@sira,@birim,@alyil,@hesapno,@sicilno,@obirim,@demir_id,@stokadi,@tarih,@cesit,@firma,@bfiyati,@miktar,@kdv,@giris
    
    SELECT @Val = ID FROM @Table
    
    DELETE FROM @Table
    set @toplam = @toplam + @Val
    fetch next from c into @idd
    end 
    close c
    Deallocate c
    return @toplam
    END
    GO
    
    select dbo.TasDayDefteBulunmayanKayitlariEkle(213) as deger



    Hope helpful...

    Pavan http://www.ggktech.com
    Wednesday, December 09, 2009 10:38 AM
  • thanks for reply it solved
    Wednesday, December 09, 2009 11:08 AM
  • thanks a lot.. can you explain to me what's problem? it is "You cannot change the database from a function"?

    Very important distinction between stored procedures and functions.

    BOL: "The following statements are valid in a function:
    • Assignment statements.
    • Control-of-Flow statements except TRY...CATCH statements.
    • DECLARE statements defining local data variables and local cursors.
    • SELECT statements that contain select lists with expressions that assign values to local variables.
    • Cursor operations referencing local cursors that are declared, opened, closed, and deallocated in the function. Only FETCH statements that assign values to local variables using the INTO clause are allowed; FETCH statements that return data to the client are not allowed.
    • INSERT, UPDATE, and DELETE statements modifying local table variables.

    • EXECUTE statements calling extended stored procedures.
      "

    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    Wednesday, December 09, 2009 11:15 AM