传递长查询时出错

Question

我有一个疑问。

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

如果在 SQL Server 中运行，它可以正常工作。但是，如果我传递与 C# 代码（ExecuteReader）中的字符串相同的字符串，则它不会执行。

如果我从代码后面复制查询并粘贴到 SQL Server 中，它将出现在单行中。我认为这就是我的查询未执行的原因。

请让我知道如何从我的应用程序（C#）传递执行长查询的方法

谢谢

Answer 1

尝试先声明您的变量：

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

等等...

然后设置它们

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

不确定这是否会解决您的问题，或者在没有看到设置 SqlCommand 对象并执行此查询的代码的情况下不难判断，但这是分配一个的首选方法变量的值。