Store Count of Records in a Variable 3

[Pigster14]

Working in a stored procedure for the first time...

I have a variable....
Declare @CountNew as varchar(3)

I don't think this is right, but I have next....

Set @CountNew = "Select Count(*) as NewAccounts
from server.db.dbo.table
where SGMTNUMB = 3 and SGMNTID Not In (Select SGMNTID from PrevCostCenter)"

I want the @CountNew to take on the number of records the sql statement produces. I do not want the @CountNew to equal the whole string of the statement.

I then want to use @CountNew to determine if I have results and continue on with producing a file and sending an e-mail.

Any help would be appreciated.

 

[DotNetGnat]

first of all you need to have...

Declare @CountNew as INT
Select Count(*)=@CountNew
from server.db.dbo.table
where SGMTNUMB = 3 and SGMNTID Not In (Select SGMNTID from PrevCostCenter)

-DNG

 

[amrita418]

Try this:

Set @CountNew = (Select Count(*) 
                 from    server.db.dbo.table
                 where   SGMTNUMB = 3 
                         and SGMNTID Not In (Select SGMNTID from PrevCostCenter))

But if all you need to know is that there is atleast one record (i.e. the count is not used anywhere else) then
Try something like:

IF Exists (select 'x' 
           from   server.db.dbo.table
           where  SGMTNUMB = 3 
                  and SGMNTID Not In (Select SGMNTID from PrevCostCenter)) 
Begin
blah blah
End

Regards,
AA

 

[DotNetGnat]

oops i guess that should be

Declare @CountNew as integer

Select Count(*)=@CountNew
from server.db.dbo.table
where SGMTNUMB = 3 and SGMNTID Not In (Select SGMNTID from PrevCostCenter)

-DNG

 

[gmmastros]

Count will be an integer, so declare it as such

Declare @CountNew Integer

Then, to set it...

Select @CountNew = IsNull(Count(*), 0)
From Table
Where SGMTNUMB = 3 and SGMNTID Not In (Select SGMNTID from PrevCostCenter)

You can then use the @CountNew for logic.

If @CountNew = 0
Begin
-- sql statements
End
Else
Begin
-- other sql statements
End

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[amrita418]

I did not realize you had declared the variable as varchar.

As DNG, George suggested you need to change the declaration to an integer.

 

[Pigster14]

I appreciate the quick responses.

I do have one question. I notice that some replies have

Select Count(*) = @CountNew

and some have

Select @CountNew = Count(*)

Can you do this either way?

I have tried both and get errors, but I think because of my own mistake.

If I do the following:

Select Count(*) = @CountNew
from server.db.dbo.gl40200
where SGMTNUMB = 3 and SGMNTID Not In (Select SGMNTID from PrevCostCenter)

I get an error about the incorrect syntax near '=' and I keep getting an error on the line after the statement an error that just says incorrect syntax near ','. The line after the statment is blank.

If I do
Select @CountNew = (Select Count(*)
from server.db.dbo.gl40200
where SGMTNUMB = 3 and SGMNTID Not In (Select SGMNTID from PrevCostCenter))

Then I just get an error on the line after the statement that says Incorrect syntax near ','. The line that it references is blank.

On the last sql I show you, I have also tried without the () around the Select statement. Still the same thing.

Any ideas? Thank you for your help.

 

[Pigster14]

Nevermind, I figured the error out....but if you can answer about the various answers that would be great.

Thanks for all of your help.

 

[gmmastros]

Your last query shows 2 nested subqueries (which should be fine). I suggest you work your way out.

Try..
Select SGMNTID from PrevCostCenter

Does it give you results? If yes, try...
Select Count(*)
from server.db.dbo.gl40200
where SGMTNUMB = 3 and SGMNTID Not In (Select SGMNTID from PrevCostCenter))

Does this query work?

I also notice that you are referencing a server, database, owner, and table in one query, but not in the other. Could this be causing your problem?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom