Post reply

Doubt in sql-functions

  • January 2, 2009 at 3:19 am

    #383664

    dear friends,

    Can i use if condition in function ?

    Please give me a example

    thank you

  • January 2, 2009 at 3:38 am

    #920159

    Yes, you can.

    CREATE FUNCTION [dbo].[Sign]

    (

    @C Char(1)

    )

    RETURNS int AS

    BEGIN

    declare @I int

    IF @C = 'S'

    set @I = 1

    ELSE

    set @I = -1

    Return @I

    END

  • January 2, 2009 at 3:49 am

    #920164

    venki.ffcl (1/2/2009)

    dear friends,

    Can i use if condition in function ?

    Please give me a example

    thank you

    Why not check books on line for such a question? The first example of create function uses if statement.

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • January 2, 2009 at 3:49 am

    #920165

    dear friend,

    Can you please clarify the problem

    code :

    ------

    --select * from ventemp ('MAS','MRD',1,'01/01/2008','10/01/2008')

    alter function ventemp (@reg char(3),@brcd char(3),@load int,@sdt datetime,@edt datetime,@ch char(2))

    returns table as

    begin

    IF(@ch='NR')

    select * from nr where nrnm_region=@reg and nrnm_branch=@brcd and nrnm_loadwise=@load and nrnm_date between @sdt and @edt

    end

    Error

    -----

    Msg 170, Level 15, State 31, Procedure ventemp, Line 8

    Line 8: Incorrect syntax near 'BEGIN'.

    how can i rectify ?

    but it should return only table

  • January 2, 2009 at 4:17 am

    #920172

    Pleas read about create function statement in Books On Line. You have to decide if you want to create an in-line table valued function or multi-statement table valued function. Currently you mixed the syntax of both types of table valued function.

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • January 2, 2009 at 4:38 am

    #920177

    venki.ffcl (1/2/2009)

    dear friends,

    Can i use if condition in function ?

    Please give me a example

    thank you

    Can you explain how you expect to benefit from putting a simple query into a function? Answering this question is more likely to help you than your original question.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • January 2, 2009 at 5:02 am

    #920188

    If you create an inline table-valued function (which you have, by specifying just RETURNS TABLE), it must be a single select statement. If you want control flow, then you need to create a multi-statement table valued function.

    All of the details are in books online, under the section CREATE FUNCTION

    Also, just note that multi-statement table valued functions do not perform well if they return more than a couple hundred rows.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 2, 2009 at 5:26 am

    #920198

    thank you for all who replied to my post.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply