Row Count of Dataset returned from SP

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)

Row Count of Dataset returned from SP

Author

Topic

GregDDDD
Posting Yak Master

120 Posts

Posted - 2012-01-06 : 14:58:47

I wrote a sp that returns a dataset. I call this sp from a Visual Studio .Net application and all is well.

Now I want to call this sp from SQL Server, but I only need to know if it returns any rows. The original sp will only return rows a few times a month, but it is important that these results be dealt with immediately. My concern is that people using the application will forget to use the interface to check for results since it does not return results daily. I want to write a new sp that will call the original sp as a nightly task and if it returns rows send an email to key people who will then look at the results in the .Net application.

Should I create a second SP with the same query that only returns a row count or can I use the original sp to return a dataset for my .Net app and a rowcount for the task sp. Can I just test for rows in the task SP? Seems simple, but I'm stumped.

The sp takes to dates and input values.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-01-06 : 15:12:19

INSERT INTO SomeTable (...)
EXEC SomeSproc ...

IF @@ROWCOUNT > 0
...Send email

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

GregDDDD
Posting Yak Master

120 Posts

Posted - 2012-01-06 : 16:39:00

Example: Original sp called GetInfo and takes 2 dates (e.g. SELECT col1, col2, col2 FROM mytable WHERE datecol BETWEEN @start_date AND @end_date)

If the original SP does not do an INSERT can I call it from another sp to just find out if it returns rows or would I create a second sp that is basically the same query but does a COUNT() on a unique field and then returns that count as a return value.


CREATE PROCEDURE New_SP
AS
BEGIN
	SET NOCOUNT ON;
	exec GetInfo '1/1/2012', '1/3/2012'
	???Row count returned from GetInfo
	
END
GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-01-06 : 16:41:48

You can either wrap my template into a stored procedure or just run my template via a job.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

GregDDDD
Posting Yak Master

120 Posts

Posted - 2012-01-06 : 16:50:39

Ok, I must have been running the sp before I updated it after changes. I'm running that now and I do get the ROWCOUNT.

Thank you and sorry for the confusion.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-01-06 : 17:02:15

No problem, glad to help.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources