Function within a query not working. One more ques

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Function within a query not working. One more ques

Author

Topic

mrm23
Posting Yak Master

198 Posts

Posted - 2010-04-24 : 03:32:29

Hi All,
I have a SP which searches for a record. This was discussed earlier and i was given solution to it. Now, i need to display one of the columns as COMMA SEPERATED.

I have written a function for that. Now prob is i am not able to get desired o/p in the sp.

I think the WHERE clause is causing prob. please help.

SP:

ALTER PROCEDURE [dbo].[sp_TLCOMSSearchCand] (@CandID varchar(50),@CandName varchar(50),@EmailID varchar(50),
@MobileNo varchar(15),@CentreID bigint,@TotalExperience decimal(18,2))
AS
BEGIN
DECLARE @erid BIGINT
SELECT @erid = resumeid
FROM hc_resume_employer
JOIN hc_resume_bank ON hc_resume_bank.rid = hc_resume_employer.resumeid AND hc_resume_bank.uniqueno = @candid
-- PRINT @erid
--select dbo.fn_GetSeparatedText(@erid)

SELECT @CandName = NullIf(RTrim(@CandName), ''),
@EmailID = NullIf(RTrim(@EmailID), ''),
@MobileNo = NullIf(RTrim(@MobileNo), ''),-- Assumed that these are numeric fields and 0 value means "any value"
@CandID = NullIf(@CandID, ''),
@CentreID = NullIf(@CentreID, 0),
@TotalExperience = NullIf(@TotalExperience, 0)

SELECT hrb.uniqueno,hrb.FirstName,hrb.MiddleName,hrb.LastName,hrb.Father_Occupation,hrb.Monthly_FamilyIncome
,hrb.EmailID,hrb.Mobile,hrb.PhoneH,hrb.DoB,hrb.Gender,hrb.PresentAddress,hrb.Landmark,hrb.CountryID,hrb.StateID,hrb.LocationID
,hrb.ResumeConvertedText,hrb.TotalExp,hrb.IndustryText,hrb.FunctionText,hrb.Working,hrb.WorkingFrom,hrb.PresentEmployer
,hrb.PresentCTC,hrb.ExpectedCTC
,dbo.fn_GetSeparatedText(@erid),hrb.resumesourceID
FROM hc_resume_bank hrb
JOIN hc_resume_employer hre
ON hrb.rid = hre.resumeid
WHERE (hrb.uniqueno = @CandID OR @CandID is null)
AND (hrb.FirstName like @CandName OR @CandName is null)
AND (hrb.LastName like @CandName OR @CandName is null)
AND (hrb.EmailId like @EmailID OR @EmailID is null)
AND (hrb.Mobile = @MobileNo OR @MobileNo is null)
AND (hrb.ResumeSourceID = @CentreID OR @CentreID is null)
AND (hrb.TotalExp = @TotalExperience OR @TotalExperience is null)
END

mrm23
Posting Yak Master

198 Posts

Posted - 2010-04-24 : 03:36:33

The user makes search in the front end so he may enter ANY OR ALL of the parameters. SO my SP Should work for any cond.
Now i have given AND in the WHERE clause as it was not working with OR. But now it is showing entirely diff record multiple times.
Please help.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-04-24 : 03:42:58

fn_GetSeparatedText is doing what as you are passing a bigint parameter

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

mrm23
Posting Yak Master

198 Posts

Posted - 2010-04-24 : 03:45:44

Here is the function. It generates COMMA SEPERATED VALUES of a particular column (Employer) where corresponding ResumeIDs match.

Function:
CREATE FUNCTION fn_GetSeparatedText
(
@ResumeID as bigint
)
RETURNS nvarchar(4000)
AS
BEGIN
Declare @ReturnData as nvarchar(4000)
set @ReturnData=''
Declare @Employer as nvarchar(1000)
DECLARE cur CURSOR STATIC LOCAL FOR
select Employer from HC_RESUME_EMPLOYER where ResumeID =@ResumeID
OPEN cur
FETCH cur INTO @Employer
WHILE @@fetch_status = 0
BEGIN
if @Employer<>''
BEGIN
if @ReturnData=''
BEGIN
SET @ReturnData=@Employer
END
ELSE
BEGIN
SET @ReturnData =@ReturnData +','+ @Employer
END
END
FETCH cur INTO @Employer
END
CLOSE cur
DEALLOCATE cur
return @ReturnData
END

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-04-24 : 05:02:03

What is your desired output and coming output ?
If distinct can remove duplicate then you can use it with the select statement...

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

mrm23
Posting Yak Master

198 Posts

Posted - 2010-04-24 : 06:14:50

Thanks for your time. The prob was with JOIN. I can get that column from the main table itself.
The JOIN was causing repetitive records.
Thanks

mrm23
Posting Yak Master

198 Posts

Posted - 2010-04-24 : 08:10:18

Can i make this work for the case when only one of the search fields are entered? Say, one user enters only name and other may enter name and emailid. In both cases it must work.
when everything is null it must be a select * from table.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-04-24 : 08:22:44

You may want to read this - http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

--
Gail Shaw
SQL Server MVP

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-24 : 08:28:19

quote:
Originally posted by mrm23

Can i make this work for the case when only one of the search fields are entered? Say, one user enters only name and other may enter name and emailid. In both cases it must work.
when everything is null it must be a select * from table.

currently your proc is written that way only. why is that not working for you?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mrm23
Posting Yak Master

198 Posts

Posted - 2010-04-25 : 11:24:42

I freshly entered a record and tested. Its CandID = 'RES/1332/09'.
But when i run the above proc, it returns record with candid = 'RES/1331/09' and this is retrieved 62 times! There are many other records but they are not retrieved. This happened when all the fields were passed null.
In that case it should have returned all rows. Suggest me something...

mrm23
Posting Yak Master

198 Posts

Posted - 2010-04-26 : 00:10:31

Hi Gail Shaw,
Thanks for the reply. I tried this but it is working partially. When every field is null it retrieves all the records and when @CandID is supplied it returns that particular record.
But when @CandID is not given and only name and email are given it is doing a select * from tab.
what could be wrong?

here is my query:

ALTER PROCEDURE [dbo].[sp_TEMP_TLCOMSSearchCand] (@CandID varchar(50) = NULL,@CandName varchar(50) = NULL
,@EmailID varchar(50) = NULL,@MobileNo varchar(15) = NULL
,@CentreID bigint = NULL ,@TotalExperience decimal(18,2) = NULL)
AS
BEGIN

SELECT hrb.uniqueno,hrb.FirstName,hrb.MiddleName,hrb.LastName,hrb.Father_Occupation,hrb.Monthly_FamilyIncome
,hrb.EmailID,hrb.Mobile,hrb.PhoneH,hrb.DoB,hrb.Gender,hrb.PresentAddress,hrb.Landmark,hrb.CountryID,hrb.StateID,hrb.LocationID
,hrb.ResumeConvertedText,hrb.TotalExp,hrb.IndustryText,hrb.FunctionText,hrb.Working,hrb.WorkingFrom,hrb.PresentEmployer
,hrb.PresentCTC,hrb.ExpectedCTC,dbo.fn_GetSeparatedText(hrb.rid) as PrevEmployer,hrb.resumesourceID
FROM hc_resume_bank hrb
WHERE (hrb.uniqueno = @CandID OR @CandID is null)
OR (hrb.FirstName like @CandName OR @CandName is null)
AND (hrb.LastName like @CandName OR @CandName is null)
AND (hrb.EmailId like @EmailID OR @EmailID is null)
AND (hrb.Mobile = @MobileNo OR @MobileNo is null)
AND (hrb.ResumeSourceID = @CentreID OR @CentreID is null)
AND (hrb.TotalExp = @TotalExperience OR @TotalExperience is null)

END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-26 : 00:25:48

quote:
Originally posted by mrm23

Hi Gail Shaw,
Thanks for the reply. I tried this but it is working partially. When every field is null it retrieves all the records and when @CandID is supplied it returns that particular record.
But when @CandID is not given and only name and email are given it is doing a select * from tab.
what could be wrong?

here is my query:

ALTER PROCEDURE [dbo].[sp_TEMP_TLCOMSSearchCand] (@CandID varchar(50) = NULL,@CandName varchar(50) = NULL
,@EmailID varchar(50) = NULL,@MobileNo varchar(15) = NULL
,@CentreID bigint = NULL ,@TotalExperience decimal(18,2) = NULL)
AS
BEGIN

SELECT hrb.uniqueno,hrb.FirstName,hrb.MiddleName,hrb.LastName,hrb.Father_Occupation,hrb.Monthly_FamilyIncome
,hrb.EmailID,hrb.Mobile,hrb.PhoneH,hrb.DoB,hrb.Gender,hrb.PresentAddress,hrb.Landmark,hrb.CountryID,hrb.StateID,hrb.LocationID
,hrb.ResumeConvertedText,hrb.TotalExp,hrb.IndustryText,hrb.FunctionText,hrb.Working,hrb.WorkingFrom,hrb.PresentEmployer
,hrb.PresentCTC,hrb.ExpectedCTC,dbo.fn_GetSeparatedText(hrb.rid) as PrevEmployer,hrb.resumesourceID
FROM hc_resume_bank hrb
WHERE (hrb.uniqueno = @CandID OR @CandID is null)
ORAND (hrb.FirstName like @CandName OR @CandName is null)
AND (hrb.LastName like @CandName OR @CandName is null)
AND (hrb.EmailId like @EmailID OR @EmailID is null)
AND (hrb.Mobile = @MobileNo OR @MobileNo is null)
AND (hrb.ResumeSourceID = @CentreID OR @CentreID is null)
AND (hrb.TotalExp = @TotalExperience OR @TotalExperience is null)

END

shouldnt it be like above?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mrm23
Posting Yak Master

198 Posts

Posted - 2010-04-26 : 00:29:06

no this still gives me all the records.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-04-26 : 07:28:24

quote:
Originally posted by mrm23

Hi Gail Shaw,
Thanks for the reply. I tried this but it is working partially.

I do hope that 'fast' is not a requirement for your query. My link was nothing about the accuracy and all about the performance implications of the method that you're using.

--
Gail Shaw
SQL Server MVP

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-26 : 07:40:02

quote:
Originally posted by mrm23

no this still gives me all the records.

How? I dont think it will unless you pass NULL for all parameters

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources