Setting variable depending on record count

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Setting variable depending on record count

Author

Topic

adiel
Starting Member

16 Posts

Posted - 2009-06-18 : 10:57:30

I want to set a variable depending on the record count returned from a query. If there are no records returned I want to set the variable to a value. If there is more than one record returned from the query, the variable does not get set to anything.

Here is what I have so far (not working yet):

-- Local Variables
declare @incompleteForms varchar(1024)

-- Initialize Variables
set @incompleteForms = ''

-- Check Table
select
case
when count(*) = 0 then @incompleteForms = @incompleteForms + ',' + 'TblCustomers'
end
from TblCustomers(nolock)
where customerId = 3

An additional note on the problem
The exact error message I am getting is:

Msg 102, Level 15, State 1, Line 16
Incorrect syntax near '='.

It is pointing to the line "when count(*) = 0...."

Thanks Before Hand,
Adiel

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-18 : 11:42:19

[code]
if not exists (select * from TblCustomers where customerId = 3)
begin
select @incompleteForms = @incompleteForms + ',' + 'TblCustomers'
end
[/code]

KH
[spoiler]Time is always against us[/spoiler]

adiel
Starting Member

16 Posts

Posted - 2009-06-18 : 11:52:35

Thanks for your help. I am avoiding the use of the exists statement however. There must be a way to do it with the "when count(*)..." statement.

Thanks Again,
Adiel

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-18 : 11:59:31

what's wrong with the exists() ?

KH
[spoiler]Time is always against us[/spoiler]

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-06-18 : 12:00:31

Using EXISTS is probably more effcient, but you might be able to make use of a derived table:

SELECT
	CASE
		WHEN MyCount = 0 THEN @incompleteForms = @incompleteForms + ',' + 'TblCustomers'
		ELSE NULL
	END
FROM
	(
		SELECT COUNT(*) AS MyCount
		from TblCustomers(nolock)
		where customerId = 3
	) AS T

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-18 : 12:14:38

[code]
SELECT @incompleteForms =
CASE
WHEN MyCount = 0 THEN ~~@incompleteForms =~~ @incompleteForms + ',' + 'TblCustomers'
ELSE ~~NULL~~ @incompleteForms
END
FROM
(
SELECT COUNT(*) AS MyCount
from TblCustomers(nolock)
where customerId = 3
) AS T
[/code]

KH
[spoiler]Time is always against us[/spoiler]

adiel
Starting Member

16 Posts

Posted - 2009-06-18 : 13:07:39

Thanks Guys I appreciate it.

Adiel

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources