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?

Answer 1

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

Answer 2

DECLARE @Count INT

EXEC logtable_count @Count OUTPUT

SELECT @Count

Answer 3

You want to use

CREATE PROCEDURE dbo.LogTable_Count

AS

SELECT Count(*) FROM LogTable

GO

Answer 4

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

Answer 5

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/

Answer 6

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.

Ron