CODEWITHSUNDEEP
sql-servert-sqlstored-proceduresexecutescalar
oldbrazil
oldbrazil

Reputation: 367

How to prevent stored proc from returning intermediate recordsets?

Problem context : I use VisualStudio 2008 and typed datasets, which offer an "easy" access to executescalar() Execute scalar returns the value of the first field of the first row of the first recorset (this last part being often omitted)

The problem with this behaviour is that most often you want to make some other requests before you compute the value to be returned with a final select @returnValue statement.

So my question is, in general, How do you prevent a SP to return intermediate recordsets? (I tried SET FMTONLY ON/OFF but is has unwanted side behaviour)

Another related question is : how do you prevent a T-SQL UPDATE statement from returning the updated rows ? Sometimes you use UPDATE to simply UPDATE ...

thanks!

sample:

BEGIN

SET NOCOUNT ON;

declare @c int
select  @c=(select count(*) from work where ...)
select @c   -- so ExecuteScalar() works 
update sousblocs set myfield = @c
       where ...

select @c --after the update, won't be seen by ExecuteScalar()
return @c --useless for ExecuteScalar

END

this was my problem, and it can be solved with the first "select @c"; but I nevertheless wonder if you can disable recorset output....

Upvotes: 1

Views: 3655

Answers (1)

JNK
JNK

Reputation: 65197

1 - Don't have multiple select statements in your stored proc without a target. I.e. if you run SELECT 1,2,3 and then SELECT 4,5,6 it will return 2 recordsets.

2 - I'm not sure why you are getting records back when you run an update. Try setting SET NOCOUNT ON to turn off the "xx rows affected" messages.

Upvotes: 1

Related Questions