August 8, 2008 at 6:19 am
DECLARE @Col1 VARCHAR(10),@Col2 VARCHAR(10),@Col3 VARCHAR(10)
SET @Col1 = (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tbComm_3Months_Ago'
AND ORDINAL_POSITION =(6))
SET @Col2 = (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tbComm_3Months_Ago'
AND ORDINAL_POSITION =(7))
SET @Col3 = (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tbComm_3Months_Ago'
AND ORDINAL_POSITION =(8))
DECLARE @Records VARCHAR(MAX);
SET @Records = 'SELECT COUNT(*) FROM tbComm_3Months_Ago WHERE
(ISNULL(CONVERT(DECIMAL(18,3),' + @Col1 + '),0) + ' +
'ISNULL(CONVERT(DECIMAL(18,3),' + @Col2 + '),0) + ' +
'ISNULL(CONVERT(DECIMAL(18,3),' + @Col3 + '),0))/ ' +
'(3 * ISNULL(CONVERT(DECIMAL(18,3),EvenInstalment),0))
< 0.3333;'
EXEC @Records
---From clipboard after selecting & pasting query generated
SELECT COUNT(*) FROM tbComm_3Months_Ago
WHERE (ISNULL(CONVERT(DECIMAL(18,3),Jul08AMT),0) +
ISNULL(CONVERT(DECIMAL(18,3),Jun08AMT),0) +
ISNULL(CONVERT(DECIMAL(18,3),May08AMT),0))/
(3 * ISNULL(CONVERT(DECIMAL(18,3),EvenInstalment),0)) < 0.3333;
If select @records and copy the code into query analyser it works. But when I
exec @records I get the following error
Msg 203, Level 16, State 2, Line 25
The name 'SELECT COUNT(*) FROM tbComm_3Months_Ago WHERE
(ISNULL(CONVERT(DECIMAL(18,3),Jul08AMT),0) +
ISNULL(CONVERT(DECIMAL(18,3),Jun08AMT),0) +
ISNULL(CONVERT(DECIMAL(18,3),May08AMT),0))/
(3 * ISNULL(CONVERT(DECIMAL(18,3),EvenInstalment),0)) < 0.3333;' is not a valid identifier.
August 8, 2008 at 7:46 am
Use
EXEC(@Records)
Regards,
Andras
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply