September 18, 2008 at 3:18 am
Hi all,
Any help appreciated greatly .....
This is a small simulated scenario to make u understand better......
Create Database MyDB
Go
Use MyDB
Go
Create Table Emp (EmpID Int Identity (1,1) Primary Key, EmpName Varchar(100) Not Null, JoinDate DateTime)
Go
Create Table Dept (DeptID Int Identity(1,1) Primary Key, DeptName Varchar(50) Not Null)
Go
Create Table Desig (DesigID Int Identity(1,1) Primary Key, DesigName Varchar(50) Not Null)
Go
Create Table EmpDept(EmpID Int, DeptID Int Primary Key(EmpID, DeptID))
Go
Create Table EmpDesig (EmpID Int, DesigID Int Primary KEy(EmpID, DesigID))
Go
Insert Emp(EmpName, JoinDate) Select 'Anand',Getdate() - 1
Insert Emp(EmpName, JoinDate) Select 'Ravi',Getdate() - 5
Insert Emp(EmpName, JoinDate) Select 'Arun',Getdate() - 6
Insert Emp(EmpName, JoinDate) Select 'Wael',Getdate() - 8
Insert Emp(EmpName, JoinDate) Select 'Robert',Getdate() - 9
Insert Emp(EmpName, JoinDate) Select 'Ramya',Getdate() - 12
Insert Emp(EmpName, JoinDate) Select 'Shreedhar',Getdate() - 15
Insert Emp(EmpName, JoinDate) Select 'Shrikanth',Getdate() - 13
Go
Insert Dept (DeptName) Select 'Sales'
Insert Dept (DeptName) Select 'Purchase'
Insert Dept (DeptName) Select 'Marketing'
Insert Dept (DeptName) Select 'Admin'
Insert Dept (DeptName) Select 'Security'
Insert Dept (DeptName) Select 'Finance'
Insert Dept (DeptName) Select 'Recruiting'
Insert Dept (DeptName) Select 'EDP'
Go
Insert Desig (DesigName) Select 'Manager'
Insert Desig (DesigName) Select 'GM'
Insert Desig (DesigName) Select 'SecurityOfficer'
Insert Desig (DesigName) Select 'Programmer'
Insert Desig (DesigName) Select 'DatabaseAnalyst'
Insert Desig (DesigName) Select 'Worker'
Insert Desig (DesigName) Select 'Staff'
Insert Desig (DesigName) Select 'Supervisor'
Insert Desig (DesigName) Select 'Accountant'
Go
Insert EmpDept Select 1,8
Insert Empdept Select 2,7
Insert Empdept Select 3,6
Insert Empdept Select 4,5
Insert Empdept Select 5,4
Insert Empdept Select 6,3
Insert Empdept Select 7,2
Insert Empdept Select 8,1
Go
Insert EmpDesig Select 1,8
Insert EmpDesig Select 2,7
Insert EmpDesig Select 3,6
Insert EmpDesig Select 4,5
Insert EmpDesig Select 5,4
Insert EmpDesig Select 6,3
Insert EmpDesig Select 7,2
Insert EmpDesig Select 8,1
Go
I have a SP like....................
IF EXISTS(SELECT NAME FROM NCRMSP..SYSOBJECTS WHERE NAME LIKE '%usp_EmpSearchResult%' AND TYPE = 'P')
BEGIN
DROP PROCEDURE usp_EmpSearchResult
END
go
CREATE PROCEDURE [dbo].[usp_EmpSearchResult]
@EmpID Int = 0,
@EmpName Varchar(100) = Null,
@DesigID Int = 0,
@DeptID Int = 0,
@FromDate DateTime,
@ToDate DateTime
As
Begin
Set NoCount On
;With Cte As
(
Select Emp.EmpID, Emp.EmpName, Dept.DeptName, Desig.DesigName from
Emp Inner Join EmpDept On EmpDept.EmpID = Emp.EmpID
Inner Join Dept On Dept.DeptiD = EmpDept.DeptID
Inner Join EmpDesig On EmpDesig.EmpID = Emp.EmpID
Inner Join Desig On Desig.DesigID = EmpDesig.DesigID
Where
(Emp.JoinDate Between @FromDate And @ToDate)
and (Emp.EmpID = case when @EmpID != 0 Then @EmpID Else EmpID End)
and (Emp.EmpName = case when @EmpName != ' ' Then @EmpName Else EmpName End)
and (EmpDept.DeptID = case when @DeptID != 0 Then @DeptID Else DeptID End)
and (EmpDesig.DesigID = case when @DesigID != 0 Then @DesigID Else DesigID End) Order By Emp.JoinDate
)Select * from Cte
End
--------------------------------------------
I had used CTE becoz, in my real scenario, I am fetching the status of the employees reporting to two different managers and using it in the following CTE. (Multiple CTE)
I am also using a table variable ........ for passing the total number of records (in output parameter) and to display only the last 500 records as a resultset.
--------------------------
My problem is...................
I have 10 input parameters in my original procedure which may or may not be nulls. I had tried using Select Case statement..., which doesn't work properly.
Using an IF clause needs more search criteria that makes the proc complex to understand and also the proc grows too big as there has to more combinations for each and every condition(for 10 parameters).
No idea in Dynamic SQL statements with TABLE VARIABLE AND CTE.....
I dont Know where i had made my mistakes.......
Kindly help me in this code for the dynamic search .....
Regards,
Nithya
Regards
Priya
September 18, 2008 at 4:42 am
I'm not entirely sure what your problem is but...
You say that you are passing parameters (to be used in your WHERE clause by the looks of it) which may or may not be nulls.
Try looking at the COALESCE function in SQL Books Online. This returns the first non-null value in a list.
So as an example query...
DECLARE @Parameter int
SELECT
T1.Column1,
FROM
Table1 AS T1
WHERE
T1.Column2 = COALESCE(@Parameter, T1.Column2)
If the parameter is not NULL, then the COALESCE function returns the value of the parameter in your WHERE clause.
If it is NULL, then the COALESCE function returns the value of the Column2, which in the WHERE clause is equal to itself, thus ignoring the parameter.
This save you having to dynamically build SQL depending on whether a parameter is NULL or not.
Hope this helps,
Ash
September 18, 2008 at 5:26 am
Hi,
Thank u for reply......But the problem persists......
I had replaced the search conditions with coalesce like....
IF EXISTS(SELECT NAME FROM NCRMSP..SYSOBJECTS WHERE NAME LIKE '%usp_EmpSearchResult%' AND TYPE = 'P')
BEGIN
DROP PROCEDURE usp_EmpSearchResult
END
go
CREATE PROCEDURE [dbo].[usp_EmpSearchResult]
@EmpID Int = 0,
@EmpName Varchar(100) = Null,
@DesigID Int = 0,
@DeptID Int = 0,
@FromDate DateTime,
@ToDate DateTime
As
Begin
Set NoCount On
;With Cte As
(
Select Emp.EmpID, Emp.EmpName, Dept.DeptName, Desig.DesigName from
Emp Inner Join EmpDept On EmpDept.EmpID = Emp.EmpID
Inner Join Dept On Dept.DeptiD = EmpDept.DeptID
Inner Join EmpDesig On EmpDesig.EmpID = Emp.EmpID
Inner Join Desig On Desig.DesigID = EmpDesig.DesigID
Where
(Emp.JoinDate Between @FromDate And @ToDate)
and Emp.EmpID = coalesce(@EmpID,Emp.EmpID)
and Emp.EmpName like '%' + Coalesce(@EmpName, Emp.EmpName) + '%'
and EmpDept.DeptID = Coalesce(@DeptID,EmpDept.DeptID)
and EmpDesig.DesigID = Coalesce(@DesigID,EmpDesig.DesigID)
Order By Emp.JoinDate
)Select * from Cte
End
But I dont get the any results for any non null parameters and also for the nullable parameters.
Kindly help me in solving this issue......
Regards,
Nithya
Regards
Priya
September 18, 2008 at 7:15 am
If you run this code below, you will see that if you set the @Parameter variable to NULL, all 3 rows are returned. If you set it to a value e.g. 10, then 2 rows are returned, 2 rows having the value 10.
DECLARE @data TABLE
(
DataIDint,
Parameterint
)
DECLARE @Parameter int
-- Set variable to one or the other to test
SET @Parameter = NULL
-- SET @Parameter = 10
INSERT INTO @data VALUES(1,10)
INSERT INTO @data VALUES(2,10)
INSERT INTO @data VALUES(3,5)
SELECT *
FROM @data
WHERE Parameter = COALESCE(@Parameter, Parameter)
In your procedure, you set some of your variables to have a default = 0.
CREATE PROCEDURE [dbo].[usp_EmpSearchResult]
@EmpID Int = 0,
@EmpName Varchar(100) = Null,
@DesigID Int = 0,
@DeptID Int = 0,
@FromDate DateTime,
@ToDate DateTime
Here @EmpID, @DesigID and @DeptID have a deafult of 0, so even if you think you are passing them as NULL, they are actually being passed as 0.
Ash
September 18, 2008 at 7:56 am
Hi... Thanks again...
The problem is that the value for the parameter is not passed exactly to the query.....
Previously, the dates were substituted and the results were displayed....
But after substituting Coalesce for the conditions, the values for the dates are also not substituted.
hence receiving empty resultset......
Is there any other alternative by which we have to use these methods..... OR Am i wrong in using this?
Kindly help me.....
Regards,
Nithya
Regards
Priya
September 18, 2008 at 9:14 am
I'm sorry but I really don't understand what the problem is. Perhaps you can post some sample data and sample parameter values and explain what result you expect to see. Then I might be able to help you.
Ash
September 18, 2008 at 9:19 am
It looks like the problem lies in the following statement:
and (Emp.EmpName = case when @EmpName != '' Then @EmpName Else EmpName End)
You're input parameter @EmpName, if not populated is defaulted to NULL and you're checking for a zero-length string. Try:
and (Emp.EmpName = case when @EmpName IS NOT NULL Then @EmpName Else EmpName End)
Hth,
September 19, 2008 at 3:24 am
Hi,
My actual problem is......
CREATE PROCEDURE [dbo].[usp_EmpSearchResult]
@EmpID Int = 0,
@EmpName Varchar(100) = Null,
@DesigID Int = 0,
@DeptID Int = 0,
@FromDate DateTime,
@ToDate DateTime
As
Begin
Set NoCount On
;With Cte As
(
Select Emp.EmpID, Emp.EmpName, Dept.DeptName, Desig.DesigName from
Emp Inner Join EmpDept On EmpDept.EmpID = Emp.EmpID
Inner Join Dept On Dept.DeptiD = EmpDept.DeptID
Inner Join EmpDesig On EmpDesig.EmpID = Emp.EmpID
Inner Join Desig On Desig.DesigID = EmpDesig.DesigID
Where
(Emp.JoinDate Between @FromDate And @ToDate)
and Emp.EmpID = coalesce(@EmpID,Emp.EmpID)
and Emp.EmpName like '%' + Coalesce(@EmpName, Emp.EmpName) + '%'
and EmpDept.DeptID = Coalesce(@DeptID,EmpDept.DeptID)
and EmpDesig.DesigID = Coalesce(@DesigID,EmpDesig.DesigID)
Order By Emp.JoinDate
)Select * from Cte
End
This is my procedure....
Let us assume that I had passes values for two parameters..... (say for ex... EmpId and DesigID)
Like... EmpID = 2 and DesigId = 8
The values returnrd were NULLS bcoz.... the conditions goes through all the checking criterias we had given.... like JoinDate, EmpID, EmpName, DeptId, DesigId... So, even if one parameter has no input value, the condition returns NULL.....
So, I tried using OR condition... I get the results but not exact....like
EmpID = 2 and DesigId = 8
The results from either Or tables are returned... (The AND cond does not works with this)
Kindly help me
Nithya
Regards
Priya
September 19, 2008 at 3:33 am
The COLAESCE statements for your numeric parameters with default values of 0 will return 0.
COALESCE(@DeptID,EmpDept.DeptID)
... will always return 0 when the default is used as it's the first non-null in the list. You need to set the INT parameters to NULL instead of 0 for the COALESCE statements to work correctly.
September 19, 2008 at 3:41 am
As I suggested before, change the default value that the parameters have in your procedure.
So instead of:
CREATE PROCEDURE [dbo].[usp_EmpSearchResult]
@EmpID Int = 0,
@EmpName Varchar(100) = Null,
@DesigID Int = 0,
@DeptID Int = 0,
@FromDate DateTime,
@ToDate DateTime
Try this:
CREATE PROCEDURE [dbo].[usp_EmpSearchResult]
@EmpID Int = Null,
@EmpName Varchar(100) = Null,
@DesigID Int = Null,
@DeptID Int = Null,
@FromDate DateTime,
@ToDate DateTime
So, for example, if you don't provide a value for @EmpID, it gets passed as NULL, and the COALESCE function in the WHERE clause will return Emp.EmpID.
Ash
September 19, 2008 at 4:27 am
Hi...
Thank u so much...
I am really very shocked that the code for coalesce works with sample but not with the original sp...
Could u plz say me why the code doesnt work with original sp....
my original sp is as follows...
SET ANSI_NULLS ON
IF EXISTS(SELECT NAME FROM NCRMSP.SYS.OBJECTS WHERE NAME LIKE '%usp_TimeCardSearchResult_Nithya%' AND TYPE = 'P')
BEGIN
DROP PROCEDURE usp_TimeCardSearchResult_Nithya
END
go
CREATE PROCEDURE [dbo].[usp_TimeCardSearchResult_Nithya]
@TCUserID INT = NULL,
@POID INT = NULL,
@OriginatorUserID INT = NULL,
@HMUserID INT = NULL,
@ContractorID INT = NULL,
@StatusID INT = NULL,
@WeekEnding DATETIME,
@ToDate DATETIME,
@LName varchar(50) = NULL,
@FName varchar(50) = NULL,
@NCRPN varchar(50) = NULL,
@VendorLoginID INT = NULL,
@TCCount INT = 0 OUTPUT
AS
BEGIN
SET NOCOUNT ON
DECLARE @VendorID INT
DECLARE @LNCondChk VARCHAR(50)
DECLARE @FNCondChk VARCHAR(50)
IF (@VendorLoginID != 0)
BEGIN
IF (@VendorLoginID > 0 AND NOT ISNULL(@VendorLoginID,0) = 0)
SELECT @VendorID = MAX(vlVendorID) FROM tblvendorLogins WHERE vlLoginID = @VendorLoginID
END
IF (@NCRPN IS NULL)
SET @NCRPN = ''
IF (@LNAME IS NULL)
SET @LNAME = ''
IF (@FNAME IS NULL)
SET @FNAME = ''
IF (@VendorID IS NULL OR @VendorID = '')
SET @VendorID = NULL
IF (ISDATE(@WeekEnding) = 1 AND ISDATE(@Todate) = 1)
BEGIN
DECLARE @RESULT TABLE (timeCardid INT,tWeekEnding DATETIME,
ST FLOAT, OT FLOAT, OtherOT FLOAT, tStatusID INT,tSubmittalDate DATETIME, tApprovalDate DATETIME, POID INT,
OrderLookUpID INT, Description VARCHAR(100), TimeTracking VARCHAR(100), crLastName VARCHAR(50),crFirstName VARCHAR(50),
[name] VARCHAR(100), pJobTitle VARCHAR(100), pTCAUserID INT,pHMUserID INT,pStartDate DATETIME,pEndDate DATETIME,
crSocialSecurity VARCHAR(100),Manager VARCHAR(100), tTotalHoursST FLOAT,tTotalHoursOT FLOAT,
Originator VARCHAR(100), pStateID INT,StateShortName VARCHAR(100),pSubmittalID INT,oOriginatorUserID INT,pContractorID INT);
WITH CTE_POSTATUS AS
(
SELECT tblPO.POID, tblPO.pPOStatusID, tblUser.UserID,tblPO.pContractorID,tblPO.pStateID,tblPO.pHMUserID,tblPO.pTCAUserID,
tblPO.pVendorID,tblPO.pTimeTrackingID,tblPO.pStartDate,tblPO.pEndDate,tblPO.pDisctrict,tblPO.pSubmittalID,tblPO.pNCRProject,tblPO.pJobTitle
FROM tblPO INNER JOIN tblUser ON tblPO.pHMUserID = tblUser.UserID WHERE tblPO.pPOStatusID IN (86,26,97)
AND tblPO.pVendorID = COALESCE(@VendorID, tblPO.pVendorID)
AND tblPO.POID = COALESCE(@POID,tblPO.POID)
AND tblPO.pHMUserID = COALESCE(@HMUserID,tblPO.pHMUserID)
AND tblPO.pContractorID = COALESCE(@ContractorID,tblPO.pContractorID)
UNION
SELECT tblPO.POID, tblPO.pPOStatusID, tblUser.UserID,tblPO.pContractorID,tblPO.pStateID,tblPO.pHMUserID,tblPO.pTCAUserID,
tblPO.pVendorID,tblPO.pTimeTrackingID,tblPO.pStartDate,tblPO.pEndDate,tblPO.pDisctrict,tblPO.pSubmittalID,tblPO.pNCRProject,tblPO.pJobTitle
FROM tblPO INNER JOIN tblUser ON tblPO.pTCAUserID = tblUser.UserID WHERE tblPO.pPOStatusID = 86
AND tblPO.pVendorID = COALESCE(@VendorID, tblPO.pVendorID)
AND tblPO.POID = COALESCE(@POID,tblPO.POID)
AND tblPO.pHMUserID = COALESCE(@HMUserID,tblPO.pHMUserID)
AND tblPO.pContractorID = COALESCE(@ContractorID,tblPO.pContractorID)
),
CTE_RESULTSET AS
(
SELECT tblTimeCard.timeCardid,tblTimeCard.tWeekEnding,
ST=MAX(CASE WHEN paytypeID=1 THEN TotalHours ELSE 0 END),
OT=MAX(CASE WHEN paytypeid IN (2,6) THEN TotalHours ELSE 0 END),
SUM(CASE WHEN paytypeid > 2 AND paytypeid!=6 THEN totalhours ELSE 0 END)AS OtherOT,
tStatusID,tSubmittalDate, tApprovalDate, POID,tblLookUp.OrderLookUpID,
tblLookUp.Description,tblLookUp1.Description AS [TimeTracking],
crLastName,crFirstName, crLastName + ', ' + crFirstName AS name, pJobTitle,
pTCAUserID,pHMUserID,pStartDate,pEndDate,crSocialSecurity,
tblUser1.uLastName + ', ' + tblUser1.uFirstName AS Manager,
tTotalHoursST,tTotalHoursOT,
tblUser2.uLastName + ', ' + tblUser2.uFirstName AS Originator,
pStateID,StateShortName = CASE WHEN pDisctrict='Netherlands' AND (NOT pDisctrict IS NULL) THEN 'HOL'
WHEN StateShortName = 'Intl' THEN RegionName
ELSE ISNULL(tblLocations.StateShortName,'') END ,
pSubmittalID,oOriginatorUserID,pContractorID
FROM CTE_POSTATUS
INNER JOIN tblTimeCard ON POID = tPOID
INNER JOIN tblTimeCardDetail ON tblTimeCard.TimeCardID=tblTimeCardDetail.TimeCardID
INNER JOIN tblContractor ON ContractorID =pContractorID
INNER JOIN tblLookUp ON tblLookUp.OrderLookUpID =tStatusID
INNER JOIN tblLookUp tblLookUp1 ON tblLookUp1.OrderLookUpID =pTimeTrackingID
INNER JOIN tblLocations ON tblLocations.LocationID = pStateID
INNER JOIN tblUser ON tblUser.UserID = pTCAUserID
INNER JOIN tblUser tblUser1 ON tblUser1.UserID = pHMUserID
LEFT JOIN tblSubmittal ON pSubmittalID = SubmittalID
LEFT JOIN tblOrder ON sOrderID = Orderid
LEFT JOIN tblUser tblUser2 ON oOriginatorUserID = tblUser2.UserID
WHERE
tblTimeCard.tStatusID = COALESCE(@StatusID, tblTimeCard.tStatusID)
AND CTE_POSTATUS.pNCRProject = COALESCE(@NCRPN, CTE_POSTATUS.pNCRProject)
AND tblContractor.crLastName LIKE ('%' + COALESCE(@LName, tblContractor.crLastName) + '%')
AND tblContractor.crFirstName LIKE ('%' + COALESCE(@FName, tblContractor.crFirstName) + '%')
ANDtblOrder.oOriginatorUserID = COALESCE(@OriginatorUserID, tblOrder.oOriginatorUserID)
AND ((tblTimeCard.tWeekEnding >= @WeekEnding AND tblTimeCard.tWeekEnding < @Todate) OR
(tblTimeCard.tApprovalDate >= @WeekEnding AND tblTimeCard.tApprovalDate < @Todate))
GROUP BY
tblTimeCard.timeCardid,tWeekEnding,POID,pStartDate,pEndDate,tblLookUp.OrderLookUpID,tblLookUp.Description,tblLookUp1.Description,
crLastName,crFirstName,pJobTitle,tStatusID,tSubmittalDate,tApprovalDate, tblUser2.uLastName,tblUser2.uFirstName,
pTCAUserID,pHMUserID,crSocialSecurity,tblUser.uLastName, tblUser.uFirstName,tTotalHoursST,tTotalHoursOT,tblUser1.uLastName,
tblUser1.uFirstName,pStateID,CASE WHEN pDisctrict='Netherlands' AND (NOT pDisctrict IS NULL) THEN 'HOL'
WHEN StateShortName = 'Intl' THEN RegionName
ELSE ISNULL(tblLocations.StateShortName,'') END,pSubmittalID,oOriginatorUserID,pContractorID
)INSERT @Result SELECT TimeCardID, tWeekEnding, ST, OT, OtherOT, tStatusID, tSubmittalDate, tApprovalDate, POID, OrderLookUpID,
Description, TimeTracking, crLastName, crFirstName, [Name], pJobTitle, pTCAUserid, pHMUserid, pStartDate, pEndDate,
crSocialSecurity, Manager, tTotalHoursST, tTotalHoursOT, Originator, pStateID, StateShortName, pSubmittalID, oOriginatorUserID,
pContractorID FROM Cte_Resultset
SELECT @TCCount = COUNT(*) FROM @Result
PRINT @TCCount
SELECT TOP 300 * FROM @Result ORDER BY tWeekEnding DESC
END
END
Please help me in this.....
Nit
Regards
Priya
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply