Post reply

How to set the records count of table in one variable using sql query

  • October 5, 2009 at 4:31 am

    #74103

    Dear all,

    See the below query, i want to get the results of this query into one variable. u can find it in "select statement". Plz help me out to get the count into one variable. If there is another way then plz suggest.

    Declare @cnt int

    Declare @sid varchar(20), @qry varchar(1000)

    Declare @dtTo varchar(30), @dtFrom varchar(30)

    set @sid = '31'

    set @dtTo = '2009-09-01'

    set @dtTo = '2009-09-30'

    Select @qry = 'Select @cnt = Count(Distinct dbo.CustomerMaster.CustId) FROM dbo.CustomerMaster Where

    (Cast(floor(Cast(dbo.CustomerMaster.Transdate as float)) as datetime) >= ''' + @dtFrom + ''' and

    Cast(floor(Cast(dbo.CustomerMaster.Transdate as float)) as datetime) <= ''' + @dtTo + ''')'

    Exec(@qry)

    Thanks in advance...

  • October 5, 2009 at 4:49 am

    #1061635

    From the posted query it looks like the dynamic query may not be required. However if it is unavoidable to use a dynamic sql query, you can:

    1. Declare a table variable:

    Declare @tblVar TABLE (

    ResultingCount int

    )

    2. Insert the results of the dynamic sql into table variable using

    Insert Into @tblVar Exec (@qry)

    3. Query the table variable

    Select @cnt = ResultingCount From @tblVar

  • October 5, 2009 at 7:31 am

    #1061706

    Hi Girish,

    First-of-all thanks for reply..

    Actually i use SQL Server 2000. so i can not use Table variable.

    any ways, i use temporary table using yr solution. 🙂

    Again thank u....

  • October 5, 2009 at 10:37 am

    #1061873

    krish-529631 (10/5/2009)

    Hi Girish,

    First-of-all thanks for reply..

    Actually i use SQL Server 2000. so i can not use Table variable.

    any ways, i use temporary table using yr solution. 🙂

    Again thank u....

    SQL 2000 supports table variables just fine.

    Seth Phelabaum

    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • October 5, 2009 at 4:49 pm

    #1062034

    As Girish said, there's no need for a table variable, but you don't need a temp table either or any dynamic SQL at all.

    Declare @sid varchar(20), @qry varchar(1000)

    Declare @dtTo varchar(30), @dtFrom varchar(30)

    set @sid = '31'

    set @dtFrom = '2009-09-01'

    set @dtTo = '2009-09-30'

    Select @cnt = Count(Distinct dbo.CustomerMaster.CustId) FROM dbo.CustomerMaster Where

    dbo.CustomerMaster.Transdate >= @dtFrom and

    dbo.CustomerMaster.Transdate < DATEADD ( dd, 1, @dtTo ) -- Or maybe DateAdd ( mm, 1, @dtFrom ) if you are looking for monthly reports

    SELECT @cnt -- To be returned by your statement/storedproc

    Note this is untested, since you didn't supply any DDL or test data, and I am therefore assuming your TransDate column is typed as datetime, but I hope you get the idea. And of course assuming you meant to set @dtFrom to 1 September 2009 instead of assigning it to @dtTo. Also note that I changed the "<=" @dtTo to "< DATEADD ( dd, 1, @dtTo )" coz datetimes without the time portion are subject to the 3ms resolution of the datetime data type's resolution, which means that you may end up with 1 October at midnight and two milliseconds before that being counted twice once you run the same kind of query for October. May not be a big deal... Oh, by the way, where is the @sid variable being used?

    --------------------------------------------------------------------------
    A little knowledge is a dangerous thing (Alexander Pope)
    In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

  • October 6, 2009 at 12:11 am

    #1062107

    Hello Jan Van der Eecken,

    Thanks for reply...

    Actually already tried your suggested query when i begin with one new module. Actually the flow is like that - the end user select 2 dates : DateFrom and DateTo as u also find it in my SQL Query.

    so my query should be like this

    Select @cnt = Count(Distinct dbo.CustomerMaster.CustId) FROM dbo.CustomerMaster Where

    dbo.CustomerMaster.Transdate >= @dtFrom and

    dbo.CustomerMaster.Transdate <= @dtTo

    SELECT @cnt

    Instead of below you suggest.

    Select @cnt = Count(Distinct dbo.CustomerMaster.CustId) FROM dbo.CustomerMaster Where

    dbo.CustomerMaster.Transdate >= @dtFrom and

    dbo.CustomerMaster.Transdate <= DateAdd ( mm, 1, @dtFrom )

    SELECT @cnt

    I mean, it does not mean that your suggested query is wrong, but according to my requirement i can't use that.

    OK...Now the problem is when i run the below query i get the 2988 Records

    Declare @cnt varchar(20), @qry varchar(1000)

    Declare @dtTo varchar(30), @dtFrom varchar(30)

    set @dtFrom = '2009-09-01'

    set @dtTo = '2009-09-30'

    Select @cnt = Count(Distinct dbo.CustomerMaster.CustId) FROM dbo.CustomerMaster Where

    dbo.CustomerMaster.Transdate >= @dtFrom and

    dbo.CustomerMaster.Transdate <= @dtTo

    SELECT @cnt

    And after that when i run the below query by Casting my table column, I get the 3113 Records.

    Select Count(Distinct dbo.CustomerMaster.CustId) FROM dbo.CustomerMaster Where

    Cast(floor(Cast(dbo.CustomerMaster.Transdate as float)) as datetime) >= '2009-09-01' and

    Cast(floor(Cast(dbo.CustomerMaster.Transdate as float)) as datetime) <= '2009-09-30'

    As u told me in your reply that datetimes without the time portion are subject to the 3ms resolution of the datetime data type's resolution..

    and may be that is why i get this Count difference..:w00t:

    And bcs of this I write my sql query like this -

    Select @qry = "Select ........"

    Exec(@qry)

    If there is another way to overcome this problem then plz suggest...

    Thanks again for suggesting...and yes also thanks to share your knowledge with me...

    Oh Yes, Actually @sid variable is a part of another table which is Inner join with my Customermaster table but then i remove that table from query but forgot to remove this variable...:-)

  • October 6, 2009 at 10:57 pm

    #1062578

    krish-529631 (10/6/2009)

    Instead of below you suggest.

    Select @cnt = Count(Distinct dbo.CustomerMaster.CustId) FROM dbo.CustomerMaster Where

    dbo.CustomerMaster.Transdate >= @dtFrom and

    dbo.CustomerMaster.Transdate <= DateAdd ( mm, 1, @dtFrom )

    SELECT @cnt

    You did not read the suggestion carefully enough.

    It should be:

    Select @cnt = Count(Distinct dbo.CustomerMaster.CustId)

    FROM dbo.CustomerMaster

    Where

    dbo.CustomerMaster.Transdate >= @dtFrom and

    dbo.CustomerMaster.Transdate < DateAdd ( mm, 1, @dtFrom )

    SELECT @cnt

    _____________
    Code for TallyGenerator

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

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy