Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

IsNull() Returns Values in Query but not in Stored Procedure....


Hi,

I have a canundrum....  I have a query that pulls 10 derived columns, using
the IsNull to return 0 if there's no result, in a straight query the values
are returned (using Management Console).  However, when I add "Create
Procedure sp_xxxx" then execute, my values are not returned.  Anyone have a
similar problem?  I have tried workarounds, and it opens bigger problems .

Thanks!

How about some sample code, sample tables, sample data and sample results so
we can help you better?

Also, best practice is to not name sprocs "sp_...".

--
TheSQLGuru
President
Indicium Resources, Inc.

"Fred" <F@discussions.microsoft.com> wrote in message

news:E272806B-4BA8-433A-9857-13FEC5A843C4@microsoft.com...

Here's the main content of the query... I populate the variables @xxx <for
doc purposes, not the actual code> before issuing this SELECT....  If I
submit this in Management Studio the Pipe Units and Pipe Fees return the
correct values from my data table....  If I save it as a query (create
procedure tst_pullpipelinedata as....) and then execute, the Pipe Units and
Fees return 0's.

Thanks for your input....  F

SELECT          a.FullName,
                        a.LoanOfficer,
                        IsNull((SELECT count(UID)
                                        from pipeline_borrower pb
                                        where pb.loanofficer = a.loanofficer
                                        and ClosingDate between @FirstCutBeginDate and @SecondCutEndDate),0),
                        IsNull((SELECT  convert(decimal(10,2),sum(brokerfee+processingfee+yieldspread))
                                        from pipeline_borrower pb
                                        where pb.loanofficer = a.loanofficer
                                        AND ClosingDate between @FirstCutBeginDate and @SecondCutEndDate),0),                  
                        IsNull((SELECT
convert(decimal(10,2),sum(brokerfee+processingfee+yieldspread))
                                        from pipeline_borrower pb
                                        where pb.loanofficer = a.loanofficer
                                        AND ClosingDate between @FirstCutBeginDate and @SecondCutEndDate and
LoanIsClosed = 1),0),                          
                        IsNull((SELECT
convert(decimal(10,2),sum(brokerfee+processingfee+yieldspread))
                                        from pipeline_borrower pb1
                                        where pb1.loanofficer = a.loanofficer
                                        AND ClosingDate between @FirstCutBeginDate and @SecondCutEndDate)
                                        -
                                        (SELECT convert(decimal(10,2),sum(brokerfee+processingfee+yieldspread))
                                         from pipeline_borrower pb2
                                         where pb2.loanofficer = a.loanofficer
                                         AND ClosingDate between @FirstCutBeginDate and @SecondCutEndDate and
LoanIsClosed = 1),0),                                    
                        IsNull((select count(*)
                                        from pipeline_borrower pb
                                        where Datesigned between @CurrentWeekBegDate and @CurrentWeekEndDate
                                        and pb.loanofficer = a.loanofficer),0),
                        IsNull((select
convert(decimal(10,2),sum(brokerfee+processingfee+yieldspread))
                                        from pipeline_borrower pb
                                        where DateSigned BETWEEN @CurrentWeekBegDate and @CurrentWeekEndDate
                                        and pb.loanofficer = a.loanofficer),0),
                        IsNull((select count(*)
                                        from pipeline_borrower pb
                                        where ClosingDate between @FirstCutBeginDate and @SecondCutEndDate
                                        and pb.loanofficer = a.loanofficer
                                        and DateSigned < @CurrentWeekBegDate),0),
                        IsNull((Select
convert(decimal(10,2),sum(brokerfee+processingfee+yieldspread))
                                        from pipeline_borrower pb
                                        where ClosingDate BETWEEN @FirstCutBeginDate and @SecondCutEndDate
                                        and pb.loanofficer = a.loanofficer
                                        and DateSigned < @CurrentWeekBegDate),0)                                    
FROM        Pipeline_LoanOfficers a
WHERE a.Role = 'Loan Officer'
=====Sample return when submitted as a query in management studio====
Arthur  art@xxx.net       8       56371.84        19992.84        36379.00        0       0.00    8       56371.84
Jonathan        jonat@xxx.net     6       40020.28        17605.90        22414.38        0       0.00    6       40020.28
Will    w@xxx.net 5       26917.00        8015.00 18902.00        0       0.00    5       26917.00
Anne    a@xxx.net 1       6702.50 6702.50 0.00    0       0.00    1       6702.50
Candace         cand@xxx.net      1       3894.00 3894.00 0.00    0       0.00    1       3894.00
Claudia         clau@xxx.net      1       6115.00 0.00    0.00    0       0.00    1       6115.00
=========Example of stored procedure run in exact environment=====
Arthur  art@xxx.net       8       56371.84        19992.84        36379.00        0       0.00    0       0.00
Jonathan        jonat@xxx.net     6       40020.28        17605.90        22414.38        0       0.00    0       0.00
Will    w@xxx.net 5       26917.00        8015.00 18902.00        0       0.00    0       0.00
Anne    a@xxx.net 1       6702.50 6702.50 0.00    0       0.00    0       0.00
Candace         cand@xxx.net      1       3894.00 3894.00 0.00    0       0.00    0       0.00
Claudia         clau@xxx.net      1       6115.00 0.00    0.00    0       0.00    0       0.00

Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc