Result other stored procedure in a Select

3 Mar 2006 9:37 AM

Danny M

I need to make reports with record counts over queries you can enter
and save in a tool i made.
Now to make a stored procedure i can use in crystal report i get a
problem.

The stored procedures i have.

To return the saved query:

CREATE PROCEDURE sp_ReturnQuery
@BronPakket     Integer,
@Entiteit    Integer,
@QueryID    Integer,
@Query        Varchar(4000)     Output
AS

DECLARE @@QueryLine    Integer
DECLARE @@RecordCounter     Integer

SET @Query = ''
SET @@RecordCounter = (SELECT COUNT( sdLine) FROM T_QueryLines WHERE
ndBronPakket= @BronPakket AND ndEntiteit = @Entiteit AND ndQuery =
@QueryID)
SET @@QueryLine =1

WHILE (@@QueryLine <= @@RecordCounter)
BEGIN
    SET @Query = (SELECT sdLine FROM T_QueryLines WHERE ndBronPakket=
@BronPakket AND ndEntiteit = @Entiteit AND ndQuery = @QueryID AND
ndLine = @@QueryLine)
    SET @@QueryLine = @@QueryLine + 1
END
GO

To execute the saved query and return the count:
CREATE PROCEDURE sp_ToonQueryResults
@BronPakket    Integer,
@Entiteit    Integer,
@Query        Integer,
@ResultAantal    BigInt    Output
AS

DECLARE @aant_uniek BIGINT
DECLARE @myQUERY    NVARCHAR(4000)
DECLARE @ParmDefinition NVARCHAR(4000)

SET @ParmDefinition = N'@Aantal BigInt OUTPUT'

EXEC sp_ReturnQuery @BronPakket, @Entiteit, @Query, @QUERY = @myQUERY
OUTPUT
EXECUTE sp_executesql
@myQUERY,
@ParmDefinition,
@Aantal = @aant_uniek OUTPUT

SET @ResultAantal = @aant_uniek
GO

The stored procedure i can call from crystal reports:

SELECT sdBronPakketNaam, sdEntiteit, sdQueryNaam, ndSoort,
dbo.sp_ToonQueryResults(B.ndBronPakketID, E.ndBronPakketID, Q.ndQuery)
<--- PROBLEM!!!!
FROM T_BronPakketten B
INNER JOIN T_Entiteiten E ON B.ndBronPakketID = E.ndBronPakketID
INNER JOIN T_Queries Q ON B.ndBronPakketID = Q.ndBronPakket AND
E.ndEntiteit = Q.ndEntiteit

The problem is i cant put an EXEC in the SELECT.
And if i want to use a User Defined Function i will have a problem with
EXECUTE becuase i need to be able to get a result of the saved
query(EXEC sp_ReturnQuery etc)

Is there away to solve this problem?

Btw i am still a bit of a noob with Queries and MSSQL.

3 Mar 2006 10:07 AM

ML

First, a warning: @@ is reserved for global (system) variables, use @ for
local (user) variables.

I don't see a need for a loop at all here. Mind the fact that I haven't seen
your schema. If you post DDL, sample data and expected results
(http://www.aspfaq.com/etiquette.asp?id=5006) we can help you find a more
efficient solution. And definitely one that is more Crystal-friendly.

You could also do some reading regarding data-exchange between procedures in
SQL:
http://www.sommarskog.se/share_data.html

ML

---
http://milambda.blogspot.com/