SQL Server Performance Forum – Threads Archive

How to put the result of a select in a variable

Hi everybody! I would like to know how to put the result of a querry in a variable. By example i have this variable:
DECLARE @NbUser int I would like to put the result of this querry on this variable:
SELECT COUNT(UserID) FROM tblUser Suppose the querry return 23, i want to put this 23 on the variable. I need it to do some other operation after. I’m using stored procedure. Anyone can help? Thank you! Pascal

---

select @nbUser = count(*) from tblUser

---

select count(userID) from tblUser is equivalent to:
select count(*) from tblUser where userID is not null In case there is no null UserID value in the table they will return the same result.

---

…and just in case, you can also assign multiple values to variables with one SELECT
select @nbUser = count(*), @max = MAX(whatever_column) from tblUser —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

---

A little bit more complex now. I want to retrieve it from a querry like that: select @vSQL = ‘SELECT COUNT(tp.[ID]) FROM tblPhoto tp ‘
select @vSQL = @vSQL + ‘LEFT JOIN tblPhotoImages AS tpi ON tpi.PhotoID = tp.[ID] ‘
select @vSQL = @vSQL + ‘LEFT JOIN tblImages AS ti ON ti.[ID] = tpi.ImageID ‘
IF @CategoryID > 0 BEGIN
select @vSQL = @vSQL + ‘ WHERE tp.CategoryID = ‘ + convert(varchar, @CategoryID)
END
Execute(@vSQL) How can i put it on the variable with an Execute behind it. Pascal

---

Also note than if you dont use Aggregate functions, the variable will hold the last value if any Madhivanan Failing to plan is Planning to fail

---

Refer this
http://www.nigelrivett.net/SQLTsql/sp_executesql.html Madhivanan Failing to plan is Planning to fail

---

… or read sp_executeSQL in BOL.

---

In this case i dont see any reason to build dynamic query.
Try this: declare @count int
IF @CategoryID > 0
BEGIN
SELECT @count=COUNT(tp.[ID]) FROM tblPhoto tp
LEFT JOIN tblPhotoImages AS tpi ON tpi.PhotoID = tp.[ID]
LEFT JOIN tblImages AS ti ON ti.[ID] = tpi.ImageID
WHERE tp.CategoryID = @CategoryID
end
else
begin
SELECT @count=COUNT(tp.[ID]) FROM tblPhoto tp
LEFT JOIN tblPhotoImages AS tpi ON tpi.PhotoID = tp.[ID]
LEFT JOIN tblImages AS ti ON ti.[ID] = tpi.ImageID
end
select @count edited: in else loop you can even ommit joining clause as you are doing left join
so the result will be same if you fire simple select count([id]) from tblphoto

---

Even in if case the join is not required as categoryid column refers to same table only where filter would be applicable.

---

Check outhttp://www.sommarskog.se/dynamic_sql.html —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

---

If it is one to many relationship between tblPhoto and either tblPhotoImages and tblImages removing joins can change the count. E.g. if there is 1 photo but 3 related images (not sure I understand the difference between photos and images but anyway) count without joins would be 1 but with joins at least 3 (depending on number of matching rows from the third table). If count(distinct tp.[id]) had been used then the count would be the same.<br /><br />Anyway, good catch that dynamic sql is not needed at all. [<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]

---

Yes Mirko,
Don’t know how could i miss that simple logic.
Thanks for the updates. -pharvey
left join is pretty needed if you have one to many relation otherwise can affect the result.
but for one to one its not required and dynamic query for this case is not relevent

---

]]>