excute stored procedure with output parameters

Question

Post reply

excute stored procedure with output parameters

sqlfriend

SSC Guru

Points: 52781
More actions
October 5, 2011 at 12:03 pm

#390550

I tried to excute a stored procedure which has output parameters, but I keep get errors:
Here is the excute statement:
DECLARE @rc int
DECLARE @ProcessID int
DECLARE @SchoolYear int
DECLARE @StudentIDs int
DECLARE @createdby varchar(128)
DECLARE @Rowcount int
EXECUTE @rc = [Assignment].[dbo].[spStudent_Insert_ByStudentIDs]
,@ProcessID=483
,@SchoolYear=2011
,@StudentIDs=456985
,@CreatedBy='testuser'
,@rowCount output
SELECT'Return Value' = @rc
GO
Then I got an error:
Must pass parameter number 6 and subsequent parameters as '@name = value'. After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'.
Then I tried to move the last parameter @rowCount output to the beginning of the parameter,
then I got the following error:
The formal parameter "@ProcessID" was not declared as an OUTPUT parameter, but the actual parameter passed in requested output.
How can I fix this?
Thanks

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

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

mimran18 SSC-Addicted Points: 451 More actions · Answer 1

Hi,

Here is the articale for your reference

http://msdn.microsoft.com/en-us/library/ms188001.aspx

Here is the sample.

Declare @X as nvarchar(100)

Declare @Item int

Set @X='Select top 1 @Item=[Item] from [Table Name]'

Exec sp_executesql @X,N'@Item int output',@Item output

Select @Item

MI
http://raresql.com

sqlfriend SSC Guru Points: 52781 More actions · Answer 2

Do I have to use sp_executesql?

How can I fix my above sql?

Thanks

mimran18 SSC-Addicted Points: 451 More actions · Answer 3

try this

EXEC [Assignment].[dbo].[spStudent_Insert_ByStudentIDs] @ProcessID=483 ,@SchoolYear=2011 ,@StudentIDs=456985 ,@CreatedBy='testuser',@Rowcount output

Select @Rowcount

MI
http://raresql.com

sqlfriend SSC Guru Points: 52781 More actions · Answer 4

mimran18 (10/5/2011)
try this
EXEC [Assignment].[dbo].[spStudent_Insert_ByStudentIDs] @ProcessID=483 ,@SchoolYear=2011 ,@StudentIDs=456985 ,@CreatedBy='testuser',@Rowcount output
Select @Rowcount

I got the error:

Must pass parameter number 6 and subsequent parameters as '@name = value'. After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'.

mimran18 SSC-Addicted Points: 451 More actions · Answer 5

Can you plz put your stored procedure source here.

I think you did not define the output parameter there like this

CREATE PROCEDURE dbo.GetCountByLastName (

@LastName NVARCHAR(50),

@LastNameCount INT OUTPUT )

AS

SELECT @LastNameCount = COUNT(*)

FROM Person.Contact

WHERE LastName = @LastName

GO

DECLARE @TheCount INT

EXEC dbo.GetCountByLastName

@LastName = 'Alexander',

@LastNameCount = @TheCount OUTPUT

SELECT TheCount = @TheCount

GO

MI
http://raresql.com