Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2004
    Posts
    2

    Exclamation Unanswered: complex query in sp

    I'm struggling with a syntax error in my sp. I'm hoping someone can give it a fresh look and straighten me out. Thanks.
    Background:
    1. The innermost query (using the dynamic statements) will run fine on its own.
    2. I can wrap that in another query (with the innermost as a DERIVEDTBL) and it will run fine UNLESS I apply the GROUP BY. Then I get syntax errors.
    3. Also, the entire query runs fine as a view (with hard coded vals for column and value).
    4. I don't know what kind of formatting this message will apply, so just know that I've checked my line truncations and they all include a trailing '+. Also, I know that Occured is misspelled. That is the correct object name. Not all our DB admins are lit majors.

    Here's the code:

    CREATE PROCEDURE [dbo].[sp_ninetydayavgtotals]
    @column nvarchar(100),
    @value smallint
    AS
    DECLARE @SelectString nvarchar(500)

    SET @SelectString = 'SELECT AVG(total_attacks) AS avg_attacks ' +
    'FROM (SELECT TOP 100 PERCENT thedate AS [day], COUNT(total) AS total_attacks, SUM(enemyKIA) AS EKIA, SUM(enemyWIA) AS EWIA, SUM(coalitionKIA) AS CKIA, SUM(coalitionWIA) AS CWIA ' +
    'FROM SELECT(TOP 100 PERCENT CONVERT(nvarchar, dateOccured, 11) AS thedate, txtCategory AS total, enemyKIA, enemyWIA, coalitionKIA, coalitionWIA ' +
    'FROM dbo.v_maindata ' +
    'WHERE (CONVERT(nvarchar, dateOccured, 11) > CONVERT(nvarchar, (getdate()-90), 11)) AND (CONVERT(nvarchar, dateOccured, 11) != CONVERT(nvarchar, getdate(), 11)) AND ' + @column + ' = @value ' +
    'ORDER BY dateOccured DESC) DERIVEDTBL ' +
    'GROUP BY thedate ' +
    'ORDER BY thedate DESC) DERIVEDTBL'

    EXEC sp_executesql @SelectString, N'@column nvarchar(100), @value smallint', @column, @value
    GO

    -------------------------------------------------------------------------
    Owen Eustice
    MNC-I Webmaster
    Victory Base South

  2. #2
    Join Date
    Jun 2004
    Posts
    1
    Quote Originally Posted by oeustice
    I'm struggling with a syntax error in my sp. I'm hoping someone can give it a fresh look and straighten me out. Thanks.
    Background:
    1. The innermost query (using the dynamic statements) will run fine on its own.
    2. I can wrap that in another query (with the innermost as a DERIVEDTBL) and it will run fine UNLESS I apply the GROUP BY. Then I get syntax errors.
    3. Also, the entire query runs fine as a view (with hard coded vals for column and value).
    4. I don't know what kind of formatting this message will apply, so just know that I've checked my line truncations and they all include a trailing '+. Also, I know that Occured is misspelled. That is the correct object name. Not all our DB admins are lit majors.

    Here's the code:

    CREATE PROCEDURE [dbo].[sp_ninetydayavgtotals]
    @column nvarchar(100),
    @value smallint
    AS
    DECLARE @SelectString nvarchar(500)

    SET @SelectString = 'SELECT AVG(total_attacks) AS avg_attacks ' +
    'FROM (SELECT TOP 100 PERCENT thedate AS [day], COUNT(total) AS total_attacks, SUM(enemyKIA) AS EKIA, SUM(enemyWIA) AS EWIA, SUM(coalitionKIA) AS CKIA, SUM(coalitionWIA) AS CWIA ' +
    'FROM SELECT(TOP 100 PERCENT CONVERT(nvarchar, dateOccured, 11) AS thedate, txtCategory AS total, enemyKIA, enemyWIA, coalitionKIA, coalitionWIA ' +
    'FROM dbo.v_maindata ' +
    'WHERE (CONVERT(nvarchar, dateOccured, 11) > CONVERT(nvarchar, (getdate()-90), 11)) AND (CONVERT(nvarchar, dateOccured, 11) != CONVERT(nvarchar, getdate(), 11)) AND ' + @column + ' = @value ' +
    'ORDER BY dateOccured DESC) DERIVEDTBL ' +
    'GROUP BY thedate ' +
    'ORDER BY thedate DESC) DERIVEDTBL'

    EXEC sp_executesql @SelectString, N'@column nvarchar(100), @value smallint', @column, @value
    GO

    -------------------------------------------------------------------------
    Owen Eustice
    MNC-I Webmaster
    Victory Base South
    The problem is that the variable @SelectString is too short for the SELECT statement you assigning to it so your SQL is being truncated. Use this declaration instead:

    DECLARE @SelectString nvarchar(1000)

  3. #3
    Join Date
    Jun 2004
    Posts
    2

    Lightbulb I'm an idiot

    The task is only as difficult as you make it, I guess.

    Thanks!

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I can personally assure you that you are at least the second person to make this mistake...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I've never made such a misteak in my life, but I've read about them in this book once

    On a slightly more serious note, if you don't run into a wall at full speed every now and then, you forget how good it feels when you quit!

    -PatP

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'm supposed to quit? Nobody ever told me that part.


    OUCH!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •