December 14, 2010 at 11:51 pm
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
December 15, 2010 at 2:38 am
please provide table structure and test data
December 15, 2010 at 3:05 am
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
December 15, 2010 at 11:29 am
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
December 15, 2010 at 11:42 am
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.
December 15, 2010 at 11:45 am
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.
December 15, 2010 at 12:51 pm
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.
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
December 15, 2010 at 1:08 pm
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
December 15, 2010 at 2:05 pm
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.
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
December 15, 2010 at 2:11 pm
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".
December 15, 2010 at 3:00 pm
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
December 15, 2010 at 3:03 pm
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
December 15, 2010 at 4:14 pm
I'm not sure if this is the offical standard, but here you go:
December 16, 2010 at 1:26 am
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
December 16, 2010 at 1:49 am
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