Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

help in my procedure Expand / Collapse
Author
Message
Posted Tuesday, December 14, 2010 11:51 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, January 1, 2011 11:40 PM
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
Post #1034908
Posted Wednesday, December 15, 2010 2:38 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:41 AM
Points: 271, Visits: 388
please provide table structure and test data
Post #1034986
Posted Wednesday, December 15, 2010 3:05 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 14, 2011 12:59 PM
Points: 73, Visits: 209
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
Post #1034992
Posted Wednesday, December 15, 2010 10:56 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 7:45 AM
Points: 1,945, Visits: 2,885
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. Please learn to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

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. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html

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

2) Why are you modeling temporal data with strings?

3) Why are you wasting time with dynamic SQL?

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

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

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

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

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

9) That CASE & COALESCE is needlessly complex:

CASE WHEN silly_flag IS NULL
THEN A.company_name
ELSE B.company_name END
AS company_name,

10) When using date ranges, we expect a predicate like this in the code; not silly, slow substring calls that cannot be optimized.
Varizi.payment_date
BETWEEN @in_report_start_date AND @in_report_end_date

11) The procedure header should have been something like this;

CREATE PROCEDURE Report_Something_Company_Date -- complete name in <verb><object> form
@in_report_start_date DATE, -- proper data type
@in_report_end_date DATE, -- proper data type
@in_company_name VARCHAR(25)) -- 50 was probably too long and did you really need UNICODE?
AS
SELECT DATEPART(YEAR, something_date) AS report_year,
DATEPART(MONTH, something_date) AS report_month,
Varizi.something_amt, Varizi.bill_nbr,
FROM ..;

11) Why do you have six LEFT OUTER JOINs that keep going back to the same tables?

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?








Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1035337
Posted Wednesday, December 15, 2010 11:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:36 PM
Points: 6,593, Visits: 8,874
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
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
Post #1035356
Posted Wednesday, December 15, 2010 11:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:40 AM
Points: 7,172, Visits: 6,322
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 Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1035363
Posted Wednesday, December 15, 2010 11:45 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:40 AM
Points: 7,172, Visits: 6,322
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 Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1035366
Posted Wednesday, December 15, 2010 12:51 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:44 AM
Points: 6,235, Visits: 7,371
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
Post #1035412
Posted Wednesday, December 15, 2010 12:59 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 7:45 AM
Points: 1,945, Visits: 2,885
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.


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1035419
Posted Wednesday, December 15, 2010 1:08 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:36 PM
Points: 6,593, Visits: 8,874
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
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
Post #1035432
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse