ADO can get a recordset from a Stored Procedure, Can TSQL?

Question

Post reply

ADO can get a recordset from a Stored Procedure, Can TSQL?

John F

SSCommitted

Points: 1597
More actions
December 27, 2004 at 11:56 am

#163403

I have a stored proc that I usually call from VB using ADO. Here is a simplified version of the stored procedure:
ALTER PROCEDURE [usp_CopyJob] (@idJobSrc AS INT) AS
SET NOCOUNT ON
DECLARE @idJobNew AS INT
-- Copy Job and get new id
INSERT INTO Jobs (JobName)
SELECT 'Copy of ' + JobName FROM Jobs WHERE idJob = @idJobSrc

SELECT @idJobNew = @@IDENTITY
--
-- I removed a bunch of code here for simplicity
--
SELECT @idJobNew
SET NOCOUNT OFF
RETURN
GO
From VB, I get a recordset with one record. The record has one field. How do I get this record and use it within TSQL? Here is what I want to be able to do, but can't because I don't know the proper technique.
SELECT @idJobNew = usp_CopyJob(10)
- john

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

Several things to point out here:

1. if you don't want to modify your sp then use this syntax:

create table #T(ident int)

insert into #T(isent) exec usp_CopyJob(10)

select int from #T

2.The proper way to write such procedure is:

ALTER PROCEDURE [usp_CopyJob] (@idJobSrc AS INT) AS

SET NOCOUNT ON

DECLARE @idJobNew AS INT

-- Copy Job and get new id

INSERT INTO Jobs (JobName)

SELECT 'Copy of ' + JobName FROM Jobs WHERE idJob = @idJobSrc

RETURN SCOPE_IDENTITY()

GO

then you would use the return parameter from vb without creating a recordset and from TSQL like:

Declare @ident int

exec @ident = usp_CopyJob(10)

select @ident

3. There is the option of returning the value on an output variable and you could use that is you want to use the return parameter to check for success or failure. It is up to you

HTH

* Noel

John F SSCommitted Points: 1597 More actions · Answer 2

John F

SSCommitted

Points: 1597

December 27, 2004 at 1:20 pm

#535086

Thanks a lot! All is well now.

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 3

The 'proper' way of returning stuff from procedures, is to return errorcodes with return, and data with output parameters.

ALTER PROCEDURE [usp_CopyJob] (@idJobSrc AS INT), @idJobNew int OUTPUT

AS

SET NOCOUNT ON

-- Copy Job and get new id

INSERT INTO Jobs (JobName)

SELECT 'Copy of ' + JobName FROM Jobs WHERE idJob = @idJobSrc

SET @idJobNew = SCOPE_IDENTITY()

GO

And then the proc should be called like this:

(in a pure Transact SQL environemnt - translate to ADO is left as an exercide for the reader )

DECLARE @err int, @idJobSrc int, @idJobNew int

SET @idJobSrc = 1

EXEC @err = usp_CopyJob @idJobSrc, @idJobNew OUTPUT

IF ( @err <> 0 ) goto errhandler

SELECT @idJobNew AS 'returnedId'

The point here is to keep errorcodes and data separate.

/Kenneth

noeld SSC Guru Points: 96590 More actions · Answer 4

Are you just trying to implement the third ooyion I just mentioned ?

* Noel

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 5

Absolutely correct.

However, not as an option, but as a requirement. The third option you mentioned is the only 'proper' one.

/Kenneth