IF EXISTS with SQL Server 2008

sql-server-2008, stored-procedures

Solution

I solved it using Cursor..

create table #Result(BookCode varchar(20),BookRate float)
create table #bill(bilno varchar(max) )
insert into #bill ('A1')
insert into #bill ('A2')

    SET @Ordnum = CURSOR FOR
        SELECT bilno FROM #bill 
                OPEN @Ordnum
            FETCH NEXT
            FROM @Ordnum INTO @billno
                WHILE @@FETCH_STATUS = 0
                 BEGIN                  
                        EXEC ('ALTER TABLE #temp ADD [' + @billno+ '] varchar(30)') 
                            SET @getbookcode = CURSOR FOR
                                SELECT BookCode , BookQuantity , BookRate FROM OrderDetail where BillNo = @billno
                            OPEN @getbookcode
                                FETCH NEXT
                                    FROM @getbookcode INTO @Tbookcode,@TbookQty,@TbookRate
                                        WHILE @@FETCH_STATUS = 0
                                        BEGIN
                                             if exists(select BookCode from #Result where BookCode = @Tbookcode and BookRate = @TbookRate)
                                                begin
                                                    EXEC('Update #Result set [' + @billno+']= '+@TbookQty+' where BookCode = '''+@Tbookcode+''' and  BookRate = '+@TbookRate+'')                 
                                                end                          
                                             else 
                                                begin
                                                    EXEC('Insert Into #Result ([' + @bno + '],BookCode,BookRate) values ( '+@TbookQty +' , '''+@Tbookcode+''','+@TbookRate+')')                                  
                                                end              
                                            FETCH NEXT FROM @getbookcode INTO @Tbookcode,@TbookQty , @TbookRate
                                        END
                                    CLOSE @getbookcode
                                    DEALLOCATE @getbookcode                              
                    FETCH NEXT FROM @Ordnum INTO @bno
                 END
                CLOSE @Ordnum
            DEALLOCATE @Ordnum

 select * from #Result

Here #bill is a Temporary table in which Billnum are stored.

Problem

I have a table order in which there are 3 columns ``` BookCode Quantity Billno -------------------------- Ed001 2 A1 Ed002 3 A1 Ed003 1 A1 py001 5 A2 ed001 1 A2 ed005 2 A2 ed003 4 A2 ``` Now I want to show my result to user as table Result ``` BookCode A1 A2 ---------------------- Ed001 2 1 Ed002 3 0 Ed003 1 4 py001 0 5 ed005 0 1 ``` Result is my temporary table, I alter it and add column according to `Billno` this works fine Now I select `BookCode` and `Quantity` from table `Order` and insert it into result What I want that if there is already a `bookcode` exist then just update result table otherwise insert a new row with that bookcode. What should I do for that?

Original source