2 sql statements in SP with data set returned

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

General SQL Server Forums

New to SQL Server Programming

2 sql statements in SP with data set returned

Author

Topic

kpgraci
Yak Posting Veteran

68 Posts

Posted - 2010-12-01 : 10:39:03

Hi all,

I had a working SP that returned a client record. I want to set a LastActivity date whenever a client is loaded so I added an update statment to the SP, but now I don't get a record back.

What is the proper syntax to accomplish this?

Thanks!

Here is the SP:

ALTER PROCEDURE dbo.LoadClient

(
@ID int,
@LastActivityOn datetime,
@LastActivityBy int
)

AS

/* Added this and it broke! */
UPDATE Clients SET
LastActivityOn=@LastActivityOn, LastActivityBy=@LastActivityBy
WHERE ID=@ID

/* The original query that worked */
SELECT c.*, csr.Initials AS xCSR FROM Clients AS c
LEFT OUTER JOIN Clients AS csr ON c.CSR = csr.ID
WHERE c.ID = @ID

RETURN

kpg

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-01 : 10:43:34

try adding the statement

set nocount on

at the top of the sp - first line after as.
Suspect the client doesn't handle the resultsets.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

kpgraci
Yak Posting Veteran

68 Posts

Posted - 2010-12-01 : 11:05:29

quote:
Originally posted by nigelrivett

It works now (with no change needed!) but you did help me find the problem.

Your reply indicated to me that the SP was fine and should work (I added 'nocount on' to no effect) so I looked closer at the caller of the SP and discovered that the ID I was passing in was zero (0)!

This reason the ID parameter was 0 is somewhat bizarre and specific to my circumstances - I'm calling a web service from a silverlight app, and when I modified the LoadClient SP I (for some crazy reason) renamed the ID parameter of the webservice from 'CID' to 'ID', but I didn't refresh the silverlight app's web references (I didn't think it was necessary) and I guess it passes by parameter name (well of course it does, it's xml) so I was getting a zero instead of the actual value. Live and Learn.

Thanks.

kpg

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources