Error While passing long query

I have a query.

DECLARE  @fromDate AS DATETIME = '01/28/2009';
DECLARE  @ToDate AS DATETIME = '01/28/2011';
DECLARE  @IsMonthWise AS BIT = 1
DECLARE  @temp AS TABLE(   userId BIGINT
                         , Visits INT
                         , DownLoads INT )
DECLARE  @tempmonth AS TABLE(   userId BIGINT
                              , Visits INT
                              , DownLoads INT
                              , MonthYear NVARCHAR( 50 ) )

IF @IsMonthWise = 0
BEGIN
    INSERT INTO @temp(   UserId
                       , Visits
                       , DownLoads )
    SELECT  Main.user_id             AS UserId
          , COUNT( Main.user_id )    AS Visits
          , ( SELECT  COUNT( user_id )
              FROM  content_hits_tbl AS Sub
              WHERE  content_id <> 0 AND
                     Main.user_id = Sub.user_id AND
                     visit_type = 2 AND
                     CAST( CONVERT( VARCHAR, hit_date, 101 ) AS DATETIME ) BETWEEN CAST( CONVERT( VARCHAR, @fromDate, 101 ) AS DATETIME ) AND CAST( CONVERT( VARCHAR, @ToDate, 101 ) AS DATETIME )
              GROUP BY  USER_ID )
    FROM  content_hits_tbl AS Main
    INNER JOIN users
    ON  Main.user_id = users.user_id
    WHERE  content_id = 0 AND
           visit_type = 1 AND
           users.user_deleted = 0 AND
           CAST( CONVERT( VARCHAR, hit_date, 101 ) AS DATETIME ) BETWEEN CAST( CONVERT( VARCHAR, @fromDate, 101 ) AS DATETIME ) AND CAST( CONVERT( VARCHAR, @ToDate, 101 ) AS DATETIME )
    GROUP BY  Main.user_id

    SELECT  Users.last_name + ',' + Users.first_name AS User_Name
          , REPLACE( REPLACE( REPLACE( REPLACE( region.value_string, '1', 'APR' ), '2', 'LAR' ), '3', 'EAMER' ), '4', 'US&C' ) AS User_Region
          , CASE
                WHEN Users.membership_user = 0 THEN 'Internal'
                ELSE 'External'
            END AS User_Type
          , Visits                   AS No_of_Visits
          , ISNULL( DownLoads, 0 )   AS No_of_DownLoads
          , Users.user_id            AS UserId
          , ( SELECT  userData.value_string
              FROM  dynamic_data_tbl AS userData
              WHERE  userData.object_id = user_id AND
                     definition_id = 2147483649 ) AS [Organizational Group]
    FROM  users AS Users
    INNER JOIN @temp AS temp
    ON  temp.UserId = Users.user_id
    INNER JOIN dynamic_data_tbl AS region
    ON  region.object_id = Users.user_id
    WHERE  region.definition_id = 43
    开发者_如何学运维ORDER BY  No_of_Visits DESC
END
ELSE
BEGIN
    INSERT INTO @tempmonth(   UserId
                            , Visits
                            , DownLoads
                            , MonthYear )
    SELECT  Main.user_id             AS UserId
          , COUNT( Main.user_id )    AS Visits
          , ( SELECT  COUNT( user_id )
              FROM  content_hits_tbl AS Sub
              WHERE  content_id <> 0 AND
                     Main.user_id = Sub.user_id AND
                     DATENAME( mm, Main.hit_date ) + '-' + DATENAME( yyyy, Main.hit_date ) = DATENAME( mm, Sub.hit_date ) + '-' + DATENAME( yyyy, Sub.hit_date ) AND
                     visit_type = 2 AND
                     CAST( CONVERT( VARCHAR, hit_date, 101 ) AS DATETIME ) BETWEEN CAST( CONVERT( VARCHAR, @fromDate, 101 ) AS DATETIME ) AND CAST( CONVERT( VARCHAR, @ToDate, 101 ) AS DATETIME )
              GROUP BY  USER_ID
                      , DATENAME( mm, hit_date ) + '-' + DATENAME( yyyy, hit_date ) )
          , DATENAME( mm, hit_date ) + '-' + DATENAME( yyyy, hit_date ) AS MonthYear
    FROM  content_hits_tbl AS Main
    INNER JOIN users
    ON  Main.user_id = users.user_id
    WHERE  content_id = 0 AND
           visit_type = 1 AND
           users.user_deleted = 0 AND
           CAST( CONVERT( VARCHAR, hit_date, 101 ) AS DATETIME ) BETWEEN CAST( CONVERT( VARCHAR, @fromDate, 101 ) AS DATETIME ) AND CAST( CONVERT( VARCHAR, @ToDate, 101 ) AS DATETIME )
    GROUP BY  Main.user_id
            , DATENAME( mm, hit_date ) + '-' + DATENAME( yyyy, hit_date )

    SELECT  Users.last_name + ',' + Users.first_name AS User_Name
          , REPLACE( REPLACE( REPLACE( REPLACE( region.value_string, '1', 'APR' ), '2', 'LAR' ), '3', 'EAMER' ), '4', 'US&C' ) AS User_Region
          , CASE
                WHEN Users.membership_user = 0 THEN 'Internal'
                ELSE 'External'
            END AS User_Type
          , Visits                   AS No_of_Visits
          , ISNULL( DownLoads, 0 )   AS No_of_DownLoads
          , Users.user_id            AS UserId
          , temp.MonthYear           AS MonthYear
          , ( SELECT  userData.value_string
              FROM  dynamic_data_tbl AS userData
              WHERE  userData.object_id = user_id AND
                     definition_id = 2147483649 ) AS [Organizational Group]
    FROM  users AS Users
    INNER JOIN @tempmonth AS temp
    ON  temp.UserId = Users.user_id
    INNER JOIN dynamic_data_tbl AS region
    ON  region.object_id = Users.user_id
    WHERE  region.definition_id = 43
    ORDER BY  User_Name ASC
            , MonthYear DESC
END

DELETE FROM @temp

DELETE FROM @tempmonth

IT Works fine if in run in SQL server. But if i pass the same as string from C# code(ExecuteReader).It is Not executing.

If i copy the query from code behind and paste in sql server it is coming in single row.I think it is the reason why my query is not executing.

Please let me know the way how to pass execute long query from my Application (C#)

Thanks

Try Declaring your variables first:

DECLARE  @fromDate AS DATETIME;
DECLARE  @ToDate AS DATETIME;

etc...

and then setting them

SET @fromDate = '01/28/2009';
SET @ToDate = '01/28/2011';

Not sure if this will solve your problem or not hard to tell without seeing the code that sets up your SqlCommand object and executes this query but this is the preferred method of assigning a value to a variable.