Query problem - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Query problem

Author

Topic

immad
Posting Yak Master

230 Posts

Posted - 2013-12-19 : 04:07:26

hello i am working on this query

ALTER FUNCTION [dbo].[employeephoneextensions]
(
@CID int,
@BID int,
@EmpID int,
@DesignationID varchar,
@Did VARCHAR
)
RETURNS TABLE
AS
RETURN
SELECT
T.CID,
T2.CName,
T.BID,
T3.Bname,
T.EID,
T.Ename,
T1.dname Deparmtent,
T.Extension,
CASE WHEN T.DirectNo is null THEN '-' ELSE T.DirectNo END as DirectNo,
CASE WHEN T.CellNo is null THEN '-' ELSE T.CellNo END as CellNo,
T4.name [Designation],
T.Grade
from employee T
left outer join department T1 on T.CID = T1.CID AND T.BID = T1.BID AND T.did = T1.did
left outer join Company T2 on T.CID = T2.CID
left outer join Branch T3 on T.CID = T3.CID AND T.BID = T3.BID
left outer join Designation T4 on T.CID = T4.CID AND T.BID = T4.BID AND T.designationid = T4.designationid
where
T.leavingdate is null
AND t.extension is not null
AND (ISNULL(@CID,'')='' OR T.CID = @CID)
AND (ISNULL(@BID,'')='' OR T.BID = @BID)
AND (ISNULL(@EmpID,'')='' OR T.EID = @EmpID)
AND (ISNULL(@DesignationID,'')='' OR ',' + @DesignationID + ',' LIKE '%,' + CAST(T.designationid AS varchar) + ',%')
AND (ISNULL(@Did,'')='' OR ',' + @Did + ',' LIKE '%,' + CAST(T.DID AS varchar) + ',%')

i have problem .the problem is that when i write below statment

select * from [employeephoneextensions]
('1','2','26589','','214')

this give me null data.but In database data is available
may be there is a problem after where clause

please help me out
thanks in advance

immad uddin ahmed

Kristen
Test

22859 Posts

Posted - 2013-12-19 : 04:32:11

You have:


ISNULL(@CID,'')=''

but @CID is an INT ??

and


CAST(T.DID AS varchar)
CAST(T.designationid AS varchar)

you should include a size to the VARCHAR. Presumably you are just converting numbers, so the default size is probably OK, but its a had habit IMHO.

That means of making a wild-card match is very inefficient, so will not scale well as the table sizes grow.

(On that basis personally I wouldn't be writing this as a Function either, inline code, or a Stored Procedure, is likely to be more efficient, but there may be other considerations that you face)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-19 : 04:32:36

The first obvious issue i find with code is you've not specified length for varchar fields. make it like below


ALTER FUNCTION [dbo].[employeephoneextensions]
(
@CID int,
@BID int,
@EmpID int,
@DesignationID varchar(20),
@Did VARCHAR(20)
)
RETURNS TABLE
AS
RETURN 
SELECT
T.CID,
T2.CName,
T.BID,
T3.Bname,
T.EID,
T.Ename,
T1.dname Deparmtent,
T.Extension,
CASE WHEN T.DirectNo is null THEN '-' ELSE T.DirectNo END as DirectNo,
CASE WHEN T.CellNo is null THEN '-' ELSE T.CellNo END as CellNo,
T4.name [Designation],
T.Grade
from employee T
left outer join department T1 on T.CID = T1.CID AND T.BID = T1.BID AND T.did = T1.did
left outer join Company T2 on T.CID = T2.CID
left outer join Branch T3 on T.CID = T3.CID AND T.BID = T3.BID
left outer join Designation T4 on T.CID = T4.CID AND T.BID = T4.BID AND T.designationid = T4.designationid
where 
T.leavingdate is null 
AND t.extension is not null
AND (ISNULL(@CID,'')='' OR T.CID = @CID)
AND (ISNULL(@BID,'')='' OR T.BID = @BID)
AND (ISNULL(@EmpID,'')='' OR T.EID = @EmpID)
AND (ISNULL(@DesignationID,'')='' OR ',' + @DesignationID + ',' LIKE '%,' + CAST(T.designationid AS varchar) + ',%')
AND (ISNULL(@Did,'')='' OR ',' + @Did + ',' LIKE '%,' + CAST(T.DID AS varchar(20)) + ',%')

Not sure whether thats cause for issue you're facing though
For that we might need some details on rows which you feel to be returned like their values for columns which were used in filter

http://visakhm.blogspot.in/2010/02/importance-of-specifying-length-in.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

immad
Posting Yak Master

230 Posts

Posted - 2013-12-20 : 02:43:38

this is not working i did it varchar but still no result

immad uddin ahmed

Kristen
Test

22859 Posts

Posted - 2013-12-20 : 04:16:16

Sample data, and expected results, please

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-21 : 00:50:00

quote:
Originally posted by immad

this is not working i did it varchar but still no result

immad uddin ahmed

Unless you give us some sample data showing values you're passing and then explain us the expected output vs your current output we wont be able to help you further.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources