SEO

I have a stored procedure as follows
ALTER PROCEDURE [dbo].
[spLoaderCoverageObjectTypeActionTypeWithOptional]
-- Add the parameters for the stored procedure here
@ObjectClass sysname,
@ObjectType sysname,
@RecordAction sysname,
@FieldName sysname
AS

BEGIN
SELECT DISTINCT @ObjectClass as ObjectClass,@ObjectType as
ObjectType,@RecordAction as RecordAction,@FieldName as FieldName,
COUNT(*) AS VALIDCount--,
/*(select Count(*) from TestData6061.dbo.accounting WHERE
(AutomationType LIKE 'loader') AND ([Negative Testcase] = 0) AND
(@ObjectType = @ObjectType) AND (@RecordAction = 'New') AND
('['+@FieldName+'] ' IS NULL)
GROUP BY [01-RecordClass],[02-RecordAction]) as NULLCount*/

FROM TestData6061.dbo.accounting
WHERE (AutomationType LIKE 'loader') AND ([Negative Testcase] =
0) AND (@ObjectType = @ObjectType) AND (@RecordAction = 'New') AND
('['+@FieldName+'] ' IS NOT NULL)
GROUP BY [01-RecordClass],[02-RecordAction],@FieldName
END

I call the stored procedure as
exec spLoaderCoverageObjectTypeActionTypeWithOptional
'accounting','AccountingParameters','New','13-
OverdueOpeningTargetAccount'

and I am seeing the above mentioned error. If I remove the
'@fieldname' in the GROUPBY clause the query works but the result is
not really what I want.
Are there any solutions or workaround for this?

@fieldname is treated like a constant value,
so no another group is returned

On Mar 4, 11:32 am, czytacz <redak...@dupa.gazeta.pl> wrote:
> @fieldname is treated like a constant value,
> so no another group is returned

So I tired to modify the stored procedure a little bit as below
Declare @TableNullCount int
Declare @TableValidCount int

select @TableNullCount = (SELECT Count(*)
from TestData6061.dbo.accounting
WHERE (AutomationType = 'loader') AND ([Negative Testcase] = 0)
AND ([01-RecordClass] = @ObjectType) AND
([02-RecordAction]= 'New') AND ('['+@FieldName+']+ IS NULL') )
print @ObjectType
print '['+@FieldName+'] IS NULL'
print @TableNullCount

/*select @TableValidCount = (SELECT Count(*)
from TestData6061.dbo.accounting
WHERE (AutomationType = 'loader') AND ([Negative Testcase] = 0)
AND ([01-RecordClass] = @ObjectType) AND
([02-RecordAction]= 'New') AND ('['+@FieldName+'] IS NOT NULL') )

print @TableValidCount

SELECT DISTINCT @ObjectClass as ObjectClass,@ObjectType as
ObjectType,@RecordAction as RecordAction,@FieldName as FieldName,
@TableValidCount as VALIDCount, @TableNullCount as NULLCount

FROM TestData6061.dbo.accounting
WHERE (AutomationType LIKE 'loader') AND ([Negative Testcase] =
0) AND (@ObjectType = @ObjectType) AND (@RecordAction = 'New')

GROUP BY [01-RecordClass],[02-RecordAction] */

some commented code but basically I am trying to get the total number
of rows (ValidCount) and (NullCount) seperately and use that in the
3rd select statement but it gives me an error saying 'An expression of
non-boolean type specified in a context where a condition is expected,
near ')'. Which is @FieldName in the select statement. Am I doing
anything wrong here?

Vic (vikrantp@gmail.com) writes:
> ([02-RecordAction]= 'New') AND ('['+@FieldName+'] IS NOT NULL') )
>...
> some commented code but basically I am trying to get the total number
> of rows (ValidCount) and (NullCount) seperately and use that in the
> 3rd select statement but it gives me an error saying 'An expression of
> non-boolean type specified in a context where a condition is expected,
> near ')'. Which is @FieldName in the select statement. Am I doing
> anything wrong here?

You have

AND (string-expression)

where the string expression is

'[' + @FieldName + '] IS NOT NULL'

and that does not make much sense at all.

Maybe you like to believe that SQL Server will guess that it should
substitute the value in @FieldName with a column name, but that did
not happen last week, and it is not going to happen this week either.

Generally: if you want to use dynaimc table and column names, you will
need to use dynamic SQL, and it is going to be very painful.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Mar 4, 2:47 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> Vic (vikra...@gmail.com) writes:
> > ([02-RecordAction]= 'New') AND ('['+@FieldName+'] IS NOT NULL') )
> >...
> > some commented code but basically I am trying to get the total number
> > of rows (ValidCount) and (NullCount) seperately and use that in the
> > 3rd select statement but it gives me an error saying 'An expression of
> > non-boolean type specified in a context where a condition is expected,
> > near ')'. Which is @FieldName in the select statement. Am I doing
> > anything wrong here?
>
> You have
>
> AND (string-expression)
>
> where the string expression is
>
> '[' + @FieldName + '] IS NOT NULL'
>
> and that does not make much sense at all.
>
> Maybe you like to believe that SQL Server will guess that it should
> substitute the value in @FieldName with a column name, but that did
> not happen last week, and it is not going to happen this week either.
>
> Generally: if you want to use dynaimc table and column names, you will
> need to use dynamic SQL, and it is going to be very painful.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Hi Erland,
I actually posted and it and right after that I realized that. I got
it working actually with dynamic sql using sp_executesql. Thanks
anyways and sorry for the spam