Is there a way to get rid of immediate resultsets? 1

[bborissov]

Here's the problem. I have two SP. I use both of them in my frontend, In my second SP I execute first one, but only want its return value, w/o created recorddset to be returned to the fronend. I know that I can redirect output to TEMP TABLE, send parameter to first SP just to calculate, but I don't want to. Is there a way to accomplish this. Here some example:

CREATE DATABASE TestMe
GO
USE TestMe
GO
CREATE TABLE Test1 (Fld1 int)
CREATE TABLE Test2 (Fld1 int)
DECLARE @i int
SET @i = 0
WHILE @i < 50
     BEGIN
          IF @i <  25
             INSERT INTO Test1 VALUES (@i+1)

          INSERT INTO Test2 VALUES (@i+1)
          SET @i = @i + 1
     END
GO
CREATE PROCEDURE Test_1
AS
BEGIN
    SELECT * FROM Test2
    RETURN @@ROWCOUNT
END
GO
CREATE PROCEDURE Test_2
AS
BEGIN
    DECLARE @i int
    EXEC @i = Test_1
    SELECT @i, * FROM Test1
END

So I want when I execute Test_2 to get only one recordset w/o using something like:

DECLARE @Test TABLE (Fld1 int)
INSERT INTO @Test EXEC Test_1

or passing parameter to Test_1 and change select in Test_1, just to COUNT(*) anbd store variable.

Thank you in advance.


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[SQLDenis]

For a count you can do this

SELECT  count(*)
FROM    OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','set fmtonly off exec TestMe.dbo.Test_1)
AS tbl

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[bborissov]

Thank you Denis.
I didn't thought about OPENROWSET()

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[SQLDenis]

No problem, thanks for the purple thingy

But be careful using it. you don't want to use it with massive stored procs

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/