Update Query

Question

Post reply

Update Query

p.beeke

SSC Enthusiast

Points: 130
More actions
April 18, 2008 at 1:48 am

#189289

Hi everyone
I am new to sqlserver so please be kind
I am trying to use output parameters for the first time. I have a table that has one field which I call form_count of data type int. I update the table and make form_count = form_count+1
I then need to assign the new form_count to an output parameter to return. Could anyone please give an example

Viewing 9 posts - 1 through 8 (of 8 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Not sure I fully understood what you want...

Here's a simple example of output parameters

CREATE PROCEDURE TestingOutput

@OutVar int OUTPUT

AS

SET @OutVar = 42

GO

DECLARE @a int

EXEC TestingOutput @OutVar = @a OUTPUT

PRINT @a

Does that help?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

p.beeke SSC Enthusiast Points: 130 More actions · Answer 2

CREATE PROCEDURE [DBO].[get_form_count] @return int output

AS

UPDATE form_count

SET form_count = form_count +1

I need to get the new form_count into the @return output param

I am a newbie and have not used output params before

GO

Anipaul SSC-Insane Points: 24681 More actions · Answer 3

CREATE PROCEDURE _4P_test

@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

Read this example also:

http://www.tek-tips.com/viewthread.cfm?qid=1461110&page=9

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

p.beeke (4/18/2008)
CREATE PROCEDURE [DBO].[get_form_count] @return int output
AS
UPDATE form_count
SET form_count = form_count +1
I need to get the new form_count into the @return output param
I am a newbie and have not used output params before
GO

how many rows are in form_count?

If there's more than 1, which value do you want returned?

Try this for starters

CREATE PROCEDURE [DBO].[get_form_count] @return int output

AS

UPDATE form_count

SET @return = form_count = form_count +1

GO

DECLARE @a int

EXEC [get_form_count] @return = @a OUTPUT

PRINT @a

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

p.beeke SSC Enthusiast Points: 130 More actions · Answer 5

Thanks for the response

When I run this in query analyser I get this message

Procedure 'get_form_count' expects parameter '@return', which was not supplied.

But when running it there is no prompt for any paramaters. Any ideas please

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

The code I posted ran fine for me, no errors. Please post the way you're calling the procedure.

QA won't prompt you for parameters like MSAccess does.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

p.beeke SSC Enthusiast Points: 130 More actions · Answer 7

in query analyser I right click on the sp and select open to run it

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Open a new query, paste the following then run it.

DECLARE @a int

EXEC [get_form_count] @return = @a OUTPUT

PRINT @a

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass