Post reply

Weird Problem

  • January 5, 2015 at 11:42 pm

    #313480

    Hi all,

    I have constructed a t-sql it is returning the expected results. but the same statements in a stored procedures is not returning any row. what can be wrong. any help is highly appreciated

    <code>

    ALTER procedure [dbo].[Get_CDR]

    @ExtnNo varchar,

    @EmpName varchar,

    @FromDate varchar,

    @ToDate varchar

    As

    select call_date,call_time,call_duration,substring(dialed_no,4,len(dialed_no))dialed_no,c.extn_no,

    emp_name,emp_dept

    from cd_records c inner join employees e on c.extn_no=e.extn_no

    where c.extn_no not in ('302','660','690','711','755','763','771','797','799','890','911','932','988','A001')

    and c.call_date>=@FromDate and c.call_Date<=@ToDate

    </code>

  • January 6, 2015 at 12:52 am

    #1767981

    Are you saying that if have your procedure as this:-

    Alter PROCEDURE [dbo].[Get_CDR]

    @ExtnNo VARCHAR ,

    @EmpName VARCHAR ,

    @FromDate VARCHAR ,

    @ToDate VARCHAR

    AS

    SELECT call_date ,

    call_time ,

    call_duration ,

    SUBSTRING(dialed_no, 4, LEN(dialed_no)) dialed_no ,

    c.extn_no ,

    emp_name ,

    emp_dept

    FROM cd_records c

    INNER JOIN employees e ON c.extn_no = e.extn_no

    WHERE c.extn_no NOT IN ( '302', '660', '690', '711', '755', '763', '771',

    '797', '799', '890', '911', '932', '988',

    'A001' )

    AND c.call_date >= @FromDate

    AND c.call_Date <= @ToDate

    and you set @fromDate to '1 Dec 2013' and @ToDate to '1 dec 2014' you don't get any results.

    But if you run

    SELECT call_date ,

    call_time ,

    call_duration ,

    SUBSTRING(dialed_no, 4, LEN(dialed_no)) dialed_no ,

    c.extn_no ,

    emp_name ,

    emp_dept

    FROM cd_records c

    INNER JOIN employees e ON c.extn_no = e.extn_no

    WHERE c.extn_no NOT IN ( '302', '660', '690', '711', '755', '763', '771',

    '797', '799', '890', '911', '932', '988',

    'A001' )

    AND c.call_date >= '1 Dec 2013'

    AND c.call_Date <= '1 dec 2014'

    you do get results?

    -------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
    There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
    I would never join a club that would allow me as a member - Groucho Marx

  • January 6, 2015 at 12:59 am

    #1767983

    yes i dont get the result

  • January 6, 2015 at 1:34 am

    #1767987

    i solved it by adding size to varchar(20)

  • January 6, 2015 at 1:40 am

    #1767989

    VARCHAR declared without a length is VARCHAR(1), so any values assigned to that get truncated silently to 1 character.

    p.s. @FromDate and @ToDate should be DATETIME, not strings.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 6, 2015 at 1:45 am

    #1767990

    Match the datatype of your parameters

    @ExtnNo varchar,

    @EmpName varchar,

    @FromDate varchar,

    @ToDate varchar

    to the datatype of the column they are to be compared with. Implicit datatype conversions can yield unexpected results.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply