Set variable based on result of procedure OR update columns fromsproc result

[Greg]

I need to send the result of a procedure to an update statement.
Basically updating the column of one table with the result of a
query in a stored procedure. It only returns one value, if it didnt I
could see why it would not work, but it only returns a count.

Lets say I have a sproc like so:

create proc sp_countclients
@datecreated datetime
as
set nocount on
select count(clientid) as count
from clientstable
where datecreated > @datecreated
Then, I want to update another table with that value:

Declare @dc datetime
set @dc = '2003-09-30'
update anothertable
set ClientCount = (exec sp_countclients @dc) -- this line errors
where id_ = @@identity
OR, I could try this, but still gives me error:

declare @c int
set @c = exec sp_countclients @dc
What should I do?

Thanks in advance!
Greg

[Simon Hayes]

"Greg" <sy****@techgroupinc.com> wrote in message
news:3f***********************@news.twtelecom.net. ..

I need to send the result of a procedure to an update statement.
Basically updating the column of one table with the result of a
query in a stored procedure. It only returns one value, if it didnt I
could see why it would not work, but it only returns a count.

Lets say I have a sproc like so:

create proc sp_countclients
@datecreated datetime
as
set nocount on
select count(clientid) as count
from clientstable
where datecreated > @datecreated

An output parameter is usually the best way to get a scalar value back from
a proc:

create proc dbo.CountClients
@datecreated datetime,
@NumClients int OUTPUT
as
set nocount on
begin
select @NumClient = count(clientid)
from dbo.clientstable
where datecreated > @datecreated
end


Then, I want to update another table with that value:

Declare @dc datetime
set @dc = '2003-09-30'
update anothertable
set ClientCount = (exec sp_countclients @dc) -- this line errors
where id_ = @@identity

declare @dc datetime, @i int
set @dc = '20030930'
exec dbo.CountClients @datecreated = @dc, @NumClients = @i OUTPUT

update dbo.anothertable
set ClientCount = @i
where id = @@identity

OR, I could try this, but still gives me error:

declare @c int
set @c = exec sp_countclients @dc

You may be thinking of this:

exec @c = sp_countclients @dc

But that assigns the return value of the procedure to the variable, which is
not what you want. This is more useful:

exec @rc = dbo.CountClients @datecreated = @dc, @NumClients = @i OUTPUT

That assigns the return code to @rc and the count to @i, so you can check
for successful execution.

You should avoid using sp_ for stored procedure names - that's used for
system stored procedures only. And you may find that using @@identity as you
do above is unreliable (if you have triggers on your tables for example) -
if you need the identity value, it's usually best to assign it to a variable
immediately after it's generated, so you know you'll always be referring to
the same value throughout your code. Check out scope_identity() in Books
Online as well - it's often a better choice than @@identity.

Simon

[Simon Hayes]

"Greg" <sy****@techgroupinc.com> wrote in message
news:3f***********************@news.twtelecom.net. ..

I need to send the result of a procedure to an update statement.
Basically updating the column of one table with the result of a
query in a stored procedure. It only returns one value, if it didnt I
could see why it would not work, but it only returns a count.

Lets say I have a sproc like so:

create proc sp_countclients
@datecreated datetime
as
set nocount on
select count(clientid) as count
from clientstable
where datecreated > @datecreated

An output parameter is usually the best way to get a scalar value back from
a proc:

create proc dbo.CountClients
@datecreated datetime,
@NumClients int OUTPUT
as
set nocount on
begin
select @NumClient = count(clientid)
from dbo.clientstable
where datecreated > @datecreated
end


Then, I want to update another table with that value:

Declare @dc datetime
set @dc = '2003-09-30'
update anothertable
set ClientCount = (exec sp_countclients @dc) -- this line errors
where id_ = @@identity

declare @dc datetime, @i int
set @dc = '20030930'
exec dbo.CountClients @datecreated = @dc, @NumClients = @i OUTPUT

update dbo.anothertable
set ClientCount = @i
where id = @@identity

OR, I could try this, but still gives me error:

declare @c int
set @c = exec sp_countclients @dc

You may be thinking of this:

exec @c = sp_countclients @dc

But that assigns the return value of the procedure to the variable, which is
not what you want. This is more useful:

exec @rc = dbo.CountClients @datecreated = @dc, @NumClients = @i OUTPUT

That assigns the return code to @rc and the count to @i, so you can check
for successful execution.

You should avoid using sp_ for stored procedure names - that's used for
system stored procedures only. And you may find that using @@identity as you
do above is unreliable (if you have triggers on your tables for example) -
if you need the identity value, it's usually best to assign it to a variable
immediately after it's generated, so you know you'll always be referring to
the same value throughout your code. Check out scope_identity() in Books
Online as well - it's often a better choice than @@identity.

Simon

[--CELKO--]

>> What should I do? <<

You can use a scalar subquery expression anywhere you would use any
other expression.

UPDATE Foobar
SET clientcount
= (SELECT COUNT(client_id)
FROM Clients
WHERE date_created < @search_date)
WHERE ..;

If the subquery is empty, you get a NULL; if the subquery is not
scalar, you get a cardinality violation.

Your real problem is that you are still thinking in terms of
procedures and not in SQL yet. Do not use process steps; if you need
to do an insert, write it all in one statement; if you need to do an
update, write it all in one statement; if you need to do a delete,
write it all in one statement.

Also, please tell that you know better than to use IDENTITY for a key.
That is a way to destroy data integrity, performance and all the
advantages of an RDBMS.

[--CELKO--]

>> What should I do? <<

You can use a scalar subquery expression anywhere you would use any
other expression.

UPDATE Foobar
SET clientcount
= (SELECT COUNT(client_id)
FROM Clients
WHERE date_created < @search_date)
WHERE ..;

If the subquery is empty, you get a NULL; if the subquery is not
scalar, you get a cardinality violation.

Your real problem is that you are still thinking in terms of
procedures and not in SQL yet. Do not use process steps; if you need
to do an insert, write it all in one statement; if you need to do an
update, write it all in one statement; if you need to do a delete,
write it all in one statement.

Also, please tell that you know better than to use IDENTITY for a key.
That is a way to destroy data integrity, performance and all the
advantages of an RDBMS.