Initialize variables from stored procedure results

Question

Hello Group, I am still fairly green in SQL, and I have a question.

I am creating a lookup using reporting services that finds an account based on lat/long. My question is how can I get results from a stored procedure to initialize variables in the following sp?

Example: --This sp will go out and get the minlat, maxlat, minlong, maxlong based on the zip code. EXEC SERVER.DATABASE.dbo.GetLatLongApprox @Zip

--Then I am declaring variables which I will use in the reporting services DECLARE @MaxLat AS float DECLARE @MinLat AS float DECLARE @MaxLong AS float DECLARE @MinLong AS float

--Now I want to dynamicaly set the values base on the results of the first stored procedure. SET @MaxLat = ? SET @MinLat = ? SET @MaxLong = ? SET @MinLong = ?

This will all reside in one stored procedure that I will use in reporting services. Hopefully this makes sense. Again, I am green and don't quite have the sql speak down yet.

Any help is greatly appreciated.

Answer 1

I think you can use OUTPUT parameters in that.

EXEC SERVER.DATABASE.dbo.GetLatLongApprox @Zip, @MaxLong OUTPUT, ... , @MinLong OUTPUT

SELECT @MaxLong as N'@MaxLong' ... SELECT @MinLong as N'@MinLong'

Is that what are you looking for?

Answer 2

lookup OUTPUT parameters in Books On Line

here is an example

CREATE PROCEDURE TestProc
       @employeeID INT,
       @managerID INT OUTPUT
    AS
    BEGIN
       SELECT @managerID =2

    END
    Go


    declare @employeeID int, @managerID int
    select @employeeID = 1


    exec TestProc @employeeID,@managerID output

    select  @employeeID,@managerID

Answer 3

Hi.....

To do this job you must use output parameters. Like this:

CREATE PROCEDURE XPTO_Procedure

@intInput int,
@intOutput int OUTPUT

AS
set @intOutput = @intInput + 1 

go

Call it like this:

declare @intResult int
exec _4P_test 3 ,@intResult OUT
select @intResult

It's nice and easy :D

Answer 4

In your stored procedure

EXEC dbo.GetLatLongApprox @Zip

you just need to set the variables against the returned resultset i.e.

SELECT
@MinLat = min_lat,
@MaxLat = max_lat,
@MinLong = min_long,
@MaxLong = max_long
FROM
my_resultset

EXEC dbo.MyReportingServiceStoredProcedure @MinLat, @MaxLat, @MinLong, @MaxLong

Then pass these as parameters into the reporting services stored procedure, if I have understood you correctly and you are doing this all inside one stored procedure.