help in my procedure

  • HI

    i Write a procedure that you can see my Code Here

    {----- Code

    set ANSI_NULLS ON

    set QUOTED_IDENTIFIER ON

    go

    ALTER PROCEDURE [dbo].[DateReport]

    @DateStart nvarchar(50),

    @EndDate nvarchar(50),

    @CompName nvarchar(50)

    AS

    BEGIN

    declare @Query nvarchar(4000)

    set @Query ='Select [year].[month],

    [year].[year],

    [year].[money],

    Tvarizi.[money] AS TVM,

    Tvarizi.BillNum AS TVB,

    Tvarizi.DateOfPayBill AS TDOPB,

    Tcash.[Money] AS TCM,

    Tcash.BillNum AS TCB,

    Tcash.Recievedate AS TCRD,

    case isnull(Tvarizi.WCashID,0) when 0 then TCashTCompany.CompanyName else TvariziTCompany.CompanyName end AS TCom,

    case isnull(Tvarizi.WCashID,0) when 0 then TCashMoney.TotallValue else TvariziMoney.TotallValue end AS TMon

    From [year]

    Left outer join TVarizi On substring( TVarizi.DateOfPayBill,3,5)=[year].Date

    Left outer join TCash On substring( TCash.Recievedate,3,5) = [year].Date

    Left outer join TCompany as TCashTCompany On (TCashTCompany.CompanyName = TCash.companyName)

    Left outer join TCompany as TvariziTCompany On (TvariziTCompany.CompanyName = Tvarizi.companyName)

    Left outer join [Money] as TCashMoney On (TCashTCompany.CompanyName = TCashMoney.companyName)

    Left outer join [Money] as TvariziMoney On (TvariziTCompany.CompanyName = TvariziMoney.companyName)

    Where 1=1 '

    --print @Query

    if isnull(@DateStart,'')<>''

    set @Query = @Query + ' and ([Year].Date >='''+@DateStart+''')'

    --print '1'

    --print @Query

    if isnull(@EndDate,'')<>''

    set @Query = @Query + ' and ([Year].Date <='''+@EndDate+''')'

    --print '2'

    --print @Query

    if isnull(@CompName,'')<>''

    set @Query = @Query + ' and (case isnull(Tvarizi.WCashID,0) when 0 then TCashTCompany.CompanyName else TvariziTCompany.CompanyName end ='''+@CompName+''')'

    --print '3'

    --print @Query

    set @Query = @Query + 'order by TCom, [year].[year], [year].[month]'

    print @Query

    exec (@Query)

    END

    ---End code}

    when I use this Code exec DateReport null,null,null it return all fields empty but when i add StartDate or endate even companyName it returns nothing.

    what is my mistake ?????

    Tcash and Tvarizi are my tables.

    please Help me

    thank you

  • please provide table structure and test data

  • Use this one... I think u were missing conditions in between...

    set ANSI_NULLS ON

    set QUOTED_IDENTIFIER ON

    go

    ALTER PROCEDURE [dbo].[DateReport]

    @DateStart nvarchar(50),

    @EndDate nvarchar(50),

    @CompName nvarchar(50)

    AS

    BEGIN

    declare @Query nvarchar(4000)

    set @Query ='Select [year].[month],

    [year].[year],

    [year].[money],

    Tvarizi.[money] AS TVM,

    Tvarizi.BillNum AS TVB,

    Tvarizi.DateOfPayBill AS TDOPB,

    Tcash.[Money] AS TCM,

    Tcash.BillNum AS TCB,

    Tcash.Recievedate AS TCRD,

    case isnull(Tvarizi.WCashID,0) when 0 then TCashTCompany.CompanyName else TvariziTCompany.CompanyName end AS TCom,

    case isnull(Tvarizi.WCashID,0) when 0 then TCashMoney.TotallValue else TvariziMoney.TotallValue end AS TMon

    From [year]

    Left outer join TVarizi On substring( TVarizi.DateOfPayBill,3,5)=[year].Date

    Left outer join TCash On substring( TCash.Recievedate,3,5) = [year].Date

    Left outer join TCompany as TCashTCompany On (TCashTCompany.CompanyName = TCash.companyName)

    Left outer join TCompany as TvariziTCompany On (TvariziTCompany.CompanyName = Tvarizi.companyName)

    Left outer join [Money] as TCashMoney On (TCashTCompany.CompanyName = TCashMoney.companyName)

    Left outer join [Money] as TvariziMoney On (TvariziTCompany.CompanyName = TvariziMoney.companyName)

    Where 1=1 '

    if isnull(@CompName,'')<>''

    set @Query = @Query + ' and (case isnull(Tvarizi.WCashID,0) when 0 then TCashTCompany.CompanyName else TvariziTCompany.CompanyName end ='''+@CompName+''')'

    if isnull(@DateStart,'')<>'' and isnull(@EndDate,'')<>''

    set @Query = @Query + ' and ([Year].Date between '''+@DateStart+''' and '''+@EndDate+''')'

    if isnull(@DateStart,'')<>''

    set @Query = @Query + ' and ([Year].Date >='''+@DateStart+''')'

    if isnull(@EndDate,'')<>''

    set @Query = @Query + ' and ([Year].Date <='''+@EndDate+''')'

    set @Query = @Query + 'order by TCom, [year].[year], [year].[month]'

    print @Query

    exec (@Query)

    END

  • CELKO (12/15/2010)


    Please learn to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats.

    Hey Joe - can you send these to us, or otherwise make them publicly available for free? Last time I checked, these things cost a pretty penny (nearly $1000 for the 11179), and my company isn't willing to spend that.

    IMO, if it's going to cost like that, it isn't really a standard, but a "I want to grow up and be a standard". It might be a good idea to follow standards, but we can't afford to.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • CELKO (12/15/2010)


    5) Why did you use MONEY? Do you know about the math problems it has?

    There's math problems with money? Please, do tell. I haven't heard anything about them and as I use that field quite a bit, I need to know.

    CELKO (12/15/2010)


    6) ISNULL() is dialect; use COALESCE instead.

    Dialect??? Not sure what you mean by that. Please explain.

    CELKO (12/15/2010)


    7) Never put a "T-" prefix on a table name.

    This is a personal preference issue. Using "T-" as a naming standards is at best annoying to type, but shouldn't cause any problems unless it's a reserved keyword issue that I'm unaware of.

    CELKO (12/15/2010)


    12) Cash is not an entity; it is a type of asset. Why would it be in a separate table? Do you keep muktiple sets of books for purposes of fraud?

    Careful, Joe. That's not only insulting but it opens you up to libel and defamation suits. Is your online rep worth so much to your ego that you can afford to be in court for making comments like this?

    BIG EDIT BELOW:

    CELKO (12/15/2010)


    6) ISNULL() is dialect; use COALESCE instead.

    Wow. I just learned an interesting thing from Books Online. I do not think COALESCE in SQL Server means what you think it means, Joe.

    SQL Server Books Online


    ISNULL and COALESCE though equivalent, can behave differently. An expression involving ISNULL with non-null parameters is considered to be NOT NULL, while expressions involving COALESCE with non-null parameters is considered to be NULL.

    Now Microsoft may or may not have implemented COALESCE() by ANSI-Standards. I don't know. But given that no one can compare one unknown value (NULL) to another unknown value, I don't think using COALESCE() will help the OP. Especially since COALESCE returns NULL if all the expressions being evaluated are NULL.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • CELKO (12/15/2010)


    Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are.

    Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it.

    1) Why are you using reserved words, like YEAR and MONTH for data element names?

    4) Why do you have IF-THEN control flow instead of CASE expressions?

    8) "WHERE 1=1" is useless; a SELECT.. FROM statement does not need this.

    For the record, I do agree with Celko on the above statements. Please do post your DDL for better assistance.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Actually, the WHERE 1=1 is a neat little trick to avoid doing extra if statements to determine if something starts the clause and needs an AND. Since 1=1 is always true, it never evaluates (as far as a limited testing I did showed), but saves you some complex semantics, since all statements, no matter if first or not, can start with the AND field = @_param1 structure.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • CELKO (12/15/2010)


    ISO makes its money from selling documents. ANSI makes its money from membership fees. Other national Standards bodies are government agencies and get tax money.

    If you go on-line you can find most of the ISO stuff explained in enough detail to use it.

    You can also often find the full documents on line. But they are written in "Standards-speak" which is a specialized member of the "Legaleze" language family. It took me most of my first year on ANSI X3H2 to learn the basic rules.

    ISO-11179 is big and hard to read. That is why I put it into English in my SQL PROGRAMMING STYLE book. The DoD and Feds are hot for it and it is showing up in contracts now. It is also the basis for the Metadata group's work.

    So... we should follow this standard because then we'll buy your book to understand it?

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • CELKO (12/15/2010)


    =================

    DECLARE

    @mon1 MONEY,

    @mon2 MONEY,

    @mon3 MONEY,

    @mon4 MONEY,

    @num1 DECIMAL(19,4),

    @num2 DECIMAL(19,4),

    @num3 DECIMAL(19,4),

    @num4 DECIMAL(19,4);

    SELECT

    @mon1 = 100, @mon2 = 339, @mon3 = 10000,

    @num1 = 100, @num2 = 339, @num3 = 10000

    SET @mon4 = @mon1/@mon2*@mon3

    SET @num4 = @num1/@num2*@num3

    SELECT @mon4 AS moneyresult,

    @num4 AS numericresult

    Output: 2949.0000 2949.8525

    The problem here isn't necessarily in the money type, but it's in the override that occurs.

    In particular, @Num1/@Num2 turns into either a float, or a decimal (20, 20)... leading me to assume the float.

    Expanding on your code above:

    DECLARE

    @mon1 MONEY,

    @mon2 MONEY,

    @mon3 MONEY,

    @mon4 MONEY,

    @num1 DECIMAL(19,4),

    @num2 DECIMAL(19,4),

    @num3 DECIMAL(19,4),

    @num4 DECIMAL(19,4);

    SELECT

    @mon1 = 100, @mon2 = 339, @mon3 = 10000,

    @num1 = 100, @num2 = 339, @num3 = 10000

    SET @mon4 = @mon1/@mon2*@mon3

    SET @num4 = @num1/@num2*@num3

    SELECT @mon4 AS moneyresult,

    @num4 AS numericresult

    select @mon1, @num1

    select @mon2, @num2

    select @mon3, @num3

    SELECT @mon1/@mon2, @Num1/@Num2

    SELECT CONVERT( DECIMAL( 19, 4), @Num1) / CONVERT( DECIMAL( 19, 4), @Num2)

    SELECT CONVERT( DECIMAL( 19, 4), @Num1 / @Num2)

    SELECT @Mon2*@Mon3, @Num2*@Mon3

    SELECT 0.2949*10000, 0.2949852507374631268 * 10000

    In particular, the results from SELECT @mon1/@mon2, @Num1/@Num2 are incredibly disparate, which is causing the difference.

    The reconversion from the float into the correct # of significant digits also changes the resultant rounding, a known issue with float. It depends on what level of accuracy to what significance you want.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • CELKO (12/15/2010)


    ...

    ISO-11179 is big and hard to read. That is why I put it into English in my SQL PROGRAMMING STYLE book. The DoD and Feds are hot for it and it is showing up in contracts now. ...

    Really? You made DoD and Feds to include your book as one of their contract requirements???

    That really makes it easy: If they require your book as a standard they either have to provide a copy of it or the price will be included in the quotation.

    I can assure you, this specific cost will be a single item all by itself... (including a nice and warm side note regarding a "wanna-be-standard").

    If the backlink from "it" to "your book" is a misinterpretation from my side, please replace it with "wanna-be-standard".



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • CELKO (12/15/2010)


    >> There's math problems with money? Please, do tell. I haven't heard anything about them and as I use that field [sic: columns are not fields] quite a bit, I need to know. <<

    Actually, (when dealing with SQL Server) money is neither a field or column. Money is a data type.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • Craig Farrell (12/15/2010)


    Actually, the WHERE 1=1 is a neat little trick to avoid doing extra if statements to determine if something starts the clause and needs an AND. Since 1=1 is always true, it never evaluates (as far as a limited testing I did showed), but saves you some complex semantics, since all statements, no matter if first or not, can start with the AND field = @_param1 structure.

    I think that this should be qualified with: "when writing dynamic SQL". It is incredibly useful then; and utterly useless any other time.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • I'm not sure if this is the offical standard, but here you go:

    http://metadata-stds.org/11179/

  • WayneS (12/15/2010)


    Craig Farrell (12/15/2010)


    Actually, the WHERE 1=1 is a neat little trick to avoid doing extra if statements to determine if something starts the clause and needs an AND. Since 1=1 is always true, it never evaluates (as far as a limited testing I did showed), but saves you some complex semantics, since all statements, no matter if first or not, can start with the AND field = @_param1 structure.

    I think that this should be qualified with: "when writing dynamic SQL". It is incredibly useful then; and utterly useless any other time.

    The optimizer can strip off this kind of expressions using a feature called "contradiction detection", that prevents the engine from evaluating expressions that are always true or false (tautologies or contradictions).

    Tautological expressions don't affect performance, on the contrary, they can boost them. Try issuing that query changing "1 = 1" to "1 = 2" and look at the exec plan: it will be a single constant scan.

    -- Gianluca Sartori

  • CELKO (12/15/2010)


    3) Why are you wasting time with dynamic SQL?

    Actually, dynamic SQL is the most efficient way to handle optional parameters in stored procedures.

    Using COALESCE, ISNULL, CASE and OR to express the same in static SQL leads to severe performance problems:

    1) They all lead to index scans instead of seeks

    2) Static SQL has issues with plan caching and parameter sniffing. See Erland Sommarskog's site for a more detailed explanation.

    -- Gianluca Sartori

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

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