Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-server-2005
hi
 
here is my query but am getting all records from table.i need perticular id records
 

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
 

 

 

 

 
ALTER PROCEDURE [dbo].[usp_RptSalesSummary]
--@UserGroupID int,
@CompanyID int=null,
@PartyName varchar(50)=null,
@DateFrom datetime=null,
@DateTo datetime=null
AS BEGIN
if @CompanyID is null 
Begin
SELECT  tblStockDetails.*,tblStockHeader.*,tblCompanyMaster.*
FROM
tblStockDetails
INNER JOIN
tblStockHeader ON tblStockDetails.sdDID = tblStockHeader.shDID AND 
tblStockDetails.sdDocType = tblStockHeader.shDocType
 
left outer join tblCompanyMaster on tblStockHeader.shPartyID=tblCompanyMaster.cmCompanyID
WHERE (@DateFrom IS NULL OR
tblStockHeader.shDocumentDateTime >= @DateFrom) AND (@DateTo IS NULL OR
tblStockHeader.shDocumentDateTime <= @DateTo)
 
AND tblStockHeader.shIsDelete=0
ORDER BY tblStockHeader.shDocumentDateTime
 
End else if @CompanyID is not null 
Begin
SELECT tblStockDetails.*,tblStockHeader.*,tblCompanyMaster.*
FROM
tblStockHeader
INNER JOIN
tblStockDetails ON tblStockHeader.shDID = tblStockDetails.sdDID AND
tblStockHeader.shDocType = tblStockDetails.sdDocType
 left outer join tblCompanyMaster on tblStockHeader.shPartyID=tblCompanyMaster.cmCompanyID 
 

WHERE 
tblStockHeader.shIsDelete=0
ORDER BY tblStockHeader.shDocumentDateTime
 
End
END
 

please suggest what is the issue
 
thank you
Posted 25-Jan-13 2:08am
ythisbug2.1K

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

When you say
SELECT * FROM ...
You are telling SQL to select all the columns from the source, and return them.
You are selecting all rows from all three tables and returning them! So you are getting exactly what you are asking for...
 
If you want less, then specify less:
SELECT  tblStockDetails.sdDocType, tblStockHeader.shPartyID, tblStockHeader.shDocumentDateTime FROM ...
  Permalink  
Comments
ythisbug at 25-Jan-13 8:53am
   
i want some fields not all.thank u
ythisbug at 25-Jan-13 8:53am
   
i want some fields not all.
OriginalGriff at 25-Jan-13 9:05am
   
Yes. Did you try it?
List the fields you do want...
ythisbug at 25-Jan-13 9:23am
   
ya i tried.i want to join three tables to get some details related to perticular id.i mean cmcompanyid
OriginalGriff at 25-Jan-13 9:30am
   
Yes...and? List the fields you want (prefixed by the table name)
 
BTW: You can reduce the name length for prefixes:
SELECT a.field, b.otherfield FROM myTable a
JOIN myOtherTable b ON a.tableID = b.otherTableID
ythisbug at 25-Jan-13 10:00am
   
thanks griff
OriginalGriff at 25-Jan-13 10:25am
   
You're welcome!
ythisbug at 28-Jan-13 23:41pm
   
which i pasted query for that am getting records but am getting all records..bt not for perticualr id..
in my report format am selecting company name(party name) and two date fields start and end..within that ddate i want to fetch records..
OriginalGriff at 29-Jan-13 2:39am
   
So add an appropriate WHERE clause - probably along the lines of
... WHERE myDateField BETWEEN startDate AND endDate
ythisbug at 29-Jan-13 6:12am
   
ya thats da issue

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Maciej Los 295
1 OriginalGriff 273
2 Aajmot Sk 234
3 Marcin Kozub 205
4 Richard MacCutchan 200
0 OriginalGriff 7,903
1 Sergey Alexandrovich Kryukov 7,127
2 DamithSL 5,604
3 Manas Bhardwaj 4,986
4 Maciej Los 4,820


Advertise | Privacy | Mobile
Web03 | 2.8.1411023.1 | Last Updated 25 Jan 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100