Nested execution of strored procedures

How i can capture one nested stored procedure's ouput parameter and using the out param in calling stored procedure...Help me..declare @.i int
exec sptest @.i output

create procedure sptest
@.i int output
as
...

For a dynamic sp call see
www.nigelrivett.com
setting variables from dynamic sql

Need to UPDATE with PARAMETER / PARTITION / RANK. Please help.

Hi,

I wrote this stored procedure that works, and returns what I want, but now I want to mark the "Active" field to 1 for each of the records returned by this. I have had no luck so far.

ALTER PROCEDURE [dbo].[SelectCurrent_acmdtn]

@.extractNum char(10)

AS

BEGIN

SET NOCOUNT ON;

SELECT id, efctv_from_dt, efctv_to_dt, modify_ts, extractno, Active, acmtdn_RECID

FROM (SELECT dbo.acmdtn.*, row_number() OVER (partition BY id

ORDER BY extractno, efctv_to_dt DESC, efctv_from_dt DESC, modify_ts DESC, acmdtn_RECID DESC) rn

FROM dbo.acmdtn

WHERE extractno > @.extractNum) Rank

WHERE rn = 1

END

I have tried inserting Update between the 2 "WHERE" statements, but it returns an error

"Invalid column name 'rn'."

I have also tried opening the recordset in Access VB , but I am restricted to read-only.

I would prefer to have a stored procedure do this.

I can get it to work if I take out the parameter, but I need that part.

The purpose of this (if you care..) is I have a large amount of historical data (this is one of 42 tables) that I need to run reports on, but I need to have the data "as of a certain date (or extractno)". This is data exported from another application that I only get flat files for, that I have imported into SQL Server tables. So, by running this procedure, I get the latest "id" record as of the extractno (I get a new extract every day, with changes that were made the previous day). I want to mark these latest fields in the "Active" field so when I create reports, I can have them filter on this field.

Any help would be greatly appreciated.

Assuming ID is the primary key, you can just do something like this:

update dbo.acmdtn
set Active = 1
where id in (
SELECT id
FROM (SELECT dbo.acmdtn.*,
row_number() OVER (partition BY id ORDER BY extractno, efctv_to_dt DESC, efctv_from_dt DESC, modify_ts DESC, acmdtn_RECID DESC) rn
FROM dbo.acmdtn
WHERE extractno > @.extractNum) Rank
WHERE rn = 1) )
and active <> 1

Need to suppress repeating values in parameter drop-down list

I'm using a Report Parameter within a SQL Reporting Services Report, and the appropriate values are populating the parameter drop-down list. However, one of my tables has several records with the Service Provider ID. My question is how can repeating values be suppressed in the parameter drop-down list. See below for example. Any insight/advice is appreciated!
Service Provider ID 21
50
60
60
60

you can do a SELECT DISTINCT in your query to get unique records.