Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


help in my procedure


help in my procedure

Author
Message
gh.abtahi
gh.abtahi
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 46
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
Abhijeet Chavan
Abhijeet Chavan
SSC Veteran
SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)

Group: General Forum Members
Points: 271 Visits: 396
please provide table structure and test data
sumit.agrawal
sumit.agrawal
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 Visits: 220
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
WayneS
WayneS
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6230 Visits: 10398
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

Brandie Tarvin
Brandie Tarvin
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7752 Visits: 8726
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.

[b]SQL Server Books Online[b]

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/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.
Brandie Tarvin
Brandie Tarvin
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7752 Visits: 8726
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/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.
Evil Kraig F
Evil Kraig F
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5695 Visits: 7660
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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
WayneS
WayneS
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6230 Visits: 10398
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search