Really wierd variable results with values of variables 1

[UnsolvedCoding]

I am working on getting the values of variables from a select statment that is in a stored procedure. The problem I am getting is that the select statment has two specific variables that use a count to return values.

The first one - @Upload_Count will not function with or without a select statment. It doesn't matter if it reads (Select count(*) or a straight from C_Comment because I keep getting an error message that says "Incorrect syntax near the keyword 'From'."

The second one - @Blank_Count, runs just fine with a select statment but I can't get it to accept an alias regardless of its location.

Two questions -

1) Why is there a difference?
2) Why can't I asign a column name to the variable?

Here is the select portion of the code

Select @Multi_Counter as Multi_Count,
@Multi_Fixed_Counter as Multi_Fixed,
@Posted_By_Counter as Posted,
@Blank_CB_Count as Blank,
@Upload_Count =(From C_Comment
Where Switch = 0), -- as Uploaded
@Blank_Count = (Select count(*) from C_Comment
where Nbr ='') -- as Blank

 

[gmmastros]

I think the problem you are having is with trying to do too much in one statement.

Basically... select can be used in various ways, but you cannot mix them up. You can use select to assign values to variables and you can use select to return data. You cannot use select to assign values to variables AND return data at the same time.

It appears as though you intend this Select statement to return data. As such...

Select  @Multi_Counter as Multi_Count,  
    @Multi_Fixed_Counter as Multi_Fixed, 
    @Posted_By_Counter as Posted,
    @Blank_CB_Count as Blank,
    (Select Count(*) From C_Comment
            Where Switch = 0) as Uploaded,
    (Select count(*) from C_Comment 
                where Nbr ='') as Blank

Note that I removed the variable assignment from the last 2 and also added 'Select Count(*)' to the uploaded columns.

Looking at the code, there is another way you can construct the query that should perform a little better.

Select  @Multi_Counter as Multi_Count,  
        @Multi_Fixed_Counter as Multi_Fixed, 
        @Posted_By_Counter as Posted,
        @Blank_CB_Count as Blank,
        Count(Case When Switch = 0 Then 1 End) As Uploaded,
        Count(Case When nbr='' Then 1 End) As Blank
From    C_Comment

Under certain circumstances, this code could run a little slower. Under most (normal) situations, it will be faster. You'll need to run it against your database to see if it is any faster.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[UnsolvedCoding]

Thanks! Worked like a charm.