if------then ----else in select statement

  • hi guys,

    i need help in my Stored procedure.

    select empname ,(select case when empno is null then

    begin

    declare salary

    declare @salary int

    declare c1 CURSOR FOR

    select distinct salary from dept

    open c1

    FETCH NEXT FROM c1

    INTO @salary

    WHILE @@FETCH_STATUS = 0

    ------some calculation---------------here i need to call another sp

    close c1;

    end

    else

    maxsalary

    END) AS salary

    from dept

    can we use like this in sql select , or do i need to use case statement,

    i dont have just 1 statement ,i have block of statement .so i think i cant use case

    any idea?

  • Please post the entire stored procedure. Can't really help with only partial code.

  • It would help if you formatted your code using the IFCode Shortcuts code="sql" so that we could more easily read your code.

    select empname ,(

    select case when empno is null then

    begin

    declare @salary int

    declare c1 CURSOR FOR

    select distinct salary

    from dept

    open c1

    FETCH NEXT FROM c1

    INTO @salary

    WHILE @@FETCH_STATUS = 0

    ------some calculation

    --here i need to call another sp

    close c1;

    end

    else -- CASE

    maxsalary

    END -- CASE

    ) AS salary

    from dept

    The short answer is NO, you cannot do this.

    The long answer is that a SELECT is a single operation that operates on a set. It is necessarily atomic, so you cannot use features like CURSORS, WHILE statements, or calls to stored procedures that are not inherently atomic within a SELECT statement.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • thanks drew,is there any other way.

    i cannot use udf , as i cannot call sp inside it.

    i need to get result of cursor in select statement.

    where can i put this code and get the result

  • You surely can write your query without the cursor.

    You can use table-valued funcions (inlined or not), and make calculations set-by-set,

    not row-by-row.

    Temp tables or table variables could help.

    If you would describe exactly what you want, maybe you will get a more precise answer.

    _____________________________________________________
    Microsoft Certified Master: SQL Server 2008
    XDetails Addin - for SQL Developers
    blog.sqlxdetails.com - Transaction log myths
  • hbtkp (4/18/2012)


    thanks drew,is there any other way.

    i cannot use udf , as i cannot call sp inside it.

    i need to get result of cursor in select statement.

    where can i put this code and get the result

    hbtkp,

    Please don't hijack other forums. We have tried to assist you but have failed to provide any of us with the information we have requested from you in an effort to help you. If you need help, please start a thread in the appropriate forum, ask your question and provide all the relevent information needed to help you.

  • inline table valued function can call sp?thats my question

  • No, unfortunately you cannot call sp from a function. There are also other limitations (no PRINT, RAISERROR, no try-catch, temp tables not visible, no changing of db tables allowed etc).

    But, even without that there always is a way how to elegant do what you want...

    _____________________________________________________
    Microsoft Certified Master: SQL Server 2008
    XDetails Addin - for SQL Developers
    blog.sqlxdetails.com - Transaction log myths
  • hbtkp (4/18/2012)


    inline table valued function can call sp?thats my question

    No.

  • hbtkp (4/18/2012)


    inline table valued function can call sp?thats my question

    Please don't hijack other people's threads.

    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
  • EDIT: nothing constructive said and thinking about it after the post its better not to post it


    For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

    For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

    Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
    Jeff Moden's Cross tab and Pivots Part 1[/url]
    Jeff Moden's Cross tab and Pivots Part 2[/url]

  • j

  • ok

  • whats wrong with this code,i am getting values again and again.repeating

    declare @P1 nvarchar(32)

    declare @mtd2 float

    declare c1 CURSOR FOR

    select account, MTD from #temp2

    open c1

    FETCH NEXT FROM c1

    INTO @P1,@mtd2

    WHILE @@FETCH_STATUS = 0

    begin

    if(@mtd2 IS NULL )

    BEGIN

    declare @IRR2 float

    EXEC pdashboard

    select @IRR2 = IRR from pdashboard

    SELECT @IRR2 as Last1month

    END

    ELSE

    BEGIN

    select @mtd2 as last2month

    END

    -------------

    temp2

    create table #temp2(account varchar(31)

    mtd float)

    i am getting @mtd2 values repeating ,there should no of rows in temp2

  • Well the cursor is the problem. 😛

    You don't have a fetch_next inside the body of your cursor so it is an endless loop.

    Depending on what your proc "pdashboard" does you probably don't need a cursor for this at all.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Viewing 15 posts - 1 through 15 (of 67 total)

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