error while trying to fill a variable with information from a query 1

[ericpsu32]

Hi all,

I am perplexed, I have the following SQL code and it runs fine on it's own, and returns only one result, which should make it OK for filling a variable that I have. Instead, I get the following error...

"Server: Msg 8624, Level 16, State 16, Line 7
Internal SQL Server error."

The variable I am trying to populate is the @AssistID
Here is the query that I am running...

Declare	@USERID as Char(15)
set @UserID = 'MHammel'
Declare @Assist as INT
Set @Assist = (select SUM(Case when AssistID <> KDL_User_ID then 1 ELSE 0 END) from vw_3PLActivity WHERE EnterDate= Convert(Char(10),GetDate(),101) and AssistID = @UserID)

 

[AlexCuse]

So what is being populated in your @assist variable, and what were you expecting?

Alex

Ignorance of certain subjects is a great part of wisdom

 

[ericpsu32]

I am expecting a number of assists from my select query to be populated in the @assist variable. The problem is I get the error I listed above. If I run the select statment on it's own, I get a result with a number, yet when I combine it with the "set @assist =" it bombs out.

 

[gmmastros]

try this...

Declare @USERID as Char(15)
set @UserID = 'MHammel'
Declare @Assist as INT

Select @Assist = SUM(Case when AssistID <> KDL_User_ID then 1 ELSE 0 END) 
from   vw_3PLActivity 
WHERE  EnterDate= Convert(Char(10),GetDate(),101) 
       and AssistID = @UserID

-George

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

 

[AlexCuse]

Hm, I wonder if your where clause is throwing it off? It seems to me like it should work, and it has worked for me in a simplified example.

Can you try this out?

select @Assist = SUM(Case when AssistID <> KDL_User_ID then 1 ELSE 0 END) from vw_3PLActivity WHERE EnterDate= Convert(Char(10),GetDate(),101) and AssistID = @UserID

I normally use select @variable = when I need to populate a variable with a query result, and I have never had a problem with this.

Hope it helps,

Alex


Ignorance of certain subjects is a great part of wisdom

 

[ericpsu32]

Wow... that worked. Why did it require the "select" instead of "set?" Should this be something I use moving forward when filling a variable with an agregated query?

 

[gmmastros]

Here's an interesting article.

http://vyaskn.tripod.com/differences_between_set_and_select.htm

For me, I use set when assigning a scalar value to a variable. If the data is coming from a table, then I use select.

-George

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