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 SouthI'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)|||The task is only as difficult as you make it, I guess.

Thanks!|||I can personally assure you that you are at least the second person to make this mistake...|||I've never made such a misteak :eek: 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|||I'm supposed to quit? Nobody ever told me that part.

OUCH!