How do I run a TSQL script that returns a value?

Question

Am I correct in saying it is TSQL? I am a novice at database scripting. Is this called scripting? I am a novice at this, really I am.

I just wrote a stored procedure in SQL Server Management Studio:

CREATE PROCEDURE dbo.LogTable_Count

@Count INT OUT

AS

SELECT @Count = Count(ExperimentId) FROM LogTable

GO

I would like to test the script by calling something like:

exec logtable_count

but management studio says it expects a parameter @Count, which was not supplied. How do I write a couple of ad hoc lines to test this?

Irina C · Accepted Answer · 2009-05-01 03:18:28Z

1

Also you can use RETURN statement to return an integer value (only one and only integer) from a stored procedure.

Your stored procedure will look a little different:

CREATE PROCEDURE dbo.LogTable_Count  
AS
DECLARE @Count int
SELECT @Count = Count(ExperimentId) 
FROM LogTable
RETURN @Count
GO

You have to call your stored procedure like this:

DECLARE @RC int
EXEC @RC=dbo.LogTable_Count
PRINT @RC --or SELECT @RC

answered May 1, 2009 at 3:18

Irina C

3655 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

GilaMonster Over a year ago

Typically the return value is used (by convention mostly) for success/failure/status indicators

GilaMonster · Accepted Answer · 2009-05-01 17:20:50Z

1

Because COUNT(*) means count the number of rows in the result set. Count(ColumnName) means count the number of rows in the result set where that column is not null.

See - http://sqlinthewild.co.za/index.php/2009/04/14/on-counts/

answered May 1, 2009 at 17:20

GilaMonster

1,76812 silver badges13 bronze badges

Comments

Ron Savage · Accepted Answer · 2011-09-27 16:08:23Z

For single values like this, I prefer to use functions because you can use them in select statements:

print 'Creating countRows Stored Proc ...'
go
if exists (select * from dbo.sysobjects where  name = 'countRows') drop function countRows;
go
create function dbo.countRows()
   -- **************************************************************************
   --  Procedure: countRows()
   --     Author: Ron Savage
   --       Date: 05/01/2009
   --
   --  Description:
   --  This function counts the rows in a table and returns it.
   -- **************************************************************************
   returns integer
begin
   return(select count(*) from mytable);
end
go

-- Now you can use it in a select ...
select dbo.countRows()
go

-- Or store it in a variable ...
declare @mycount integer

select @mycount = dbo.countRows()

select @mycount
go

like that.

Chris Shaffer · Accepted Answer · 2009-04-30 23:07:42Z

0

DECLARE @Count INT

EXEC logtable_count @Count OUTPUT

SELECT @Count

answered Apr 30, 2009 at 23:07

Chris Shaffer

32.6k5 gold badges52 silver badges61 bronze badges

Comments

Gary · Accepted Answer · 2009-04-30 23:08:58Z

0

You want to use

CREATE PROCEDURE dbo.LogTable_Count

AS

SELECT Count(*) FROM LogTable

GO

answered Apr 30, 2009 at 23:08

Gary

1,8672 gold badges16 silver badges22 bronze badges

1 Comment

MedicineMan Over a year ago

great suggestion, although not exactly what I asked. I'll upvote this answer if you can tell me why * instead of naming one of the columns.

Yishai · Accepted Answer · 2009-05-13 02:25:52Z

0

declare @myout int
exec logtable_count @myout
print @myout

Edit: As the correct answer states, that middle line should be:

exec logtable_count @myout OUTPUT

edited May 13, 2009 at 2:25

answered Apr 30, 2009 at 23:07

Yishai

92.4k31 gold badges195 silver badges266 bronze badges

1 Comment

MedicineMan Over a year ago

print @myout ended up not printing anything to the "Messages" window

Collectives™ on Stack Overflow

How do I run a TSQL script that returns a value?

6 Answers 6

1 Comment

Comments

Comments

Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

Comments

Comments

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related