SQL Server - store count into a variable

How would I store a count into a variable>  I tried

intCount = Select Count(*) from sometable

but this does not work.

How can I do this?

Thanks.

  Roy Harvey replied...

@variables can be set using SET or SELECT

declare @count int

SET @count = (select count(*) from sysobjects)

select @count

SELECT @count = count(*)
FROM sysobjects

select @count

Roy Harvey
Beacon Falls, CT

  SAC replied...

Thanks!!

is the sproc: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].ESIMonthly') AND OBJECTPROPERTY(id, N'IsProcedure') = 1) BEGIN DROP Procedure CREATE Procedure dbo.ESIMonthly ( @ProcessDate DateTime = Null, @SubsidyPerChild SmallMoney = 100.00, @Rerun bit = 0, @Payments int OUTPUT, @TotalPaid Money OUTPUT ) AS SET NOCOUNT ON; / * Misc work areas * / Declare @RunDate DateTime Declare @YYYYMMDD Char(8) Declare @YYDDD Char(5) Declare @DDD Char(3) Declare @FiscalYear Char(4 Declare @RunDay Int Declare @MonthStart DateTime Declare @MonthEnd DateTime Declare @LastRun DateTime Declare @VendorName Varchar(30

NOCOUNT ON statement SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].GetParentsChildren') AND OBJECTPROPERTY(id, N'IsFunction') = 1) BEGIN DROP Function dbo.GetParentsChildren END GO CREATE FUNCTION dbo.GetParentsChildren ( @CycleId int, @ParentId int, @RunDate DateTime = Null ) RETURNS Int AS BEGIN / * SET NOCOUNT ON; * / DECLARE @RunDay TinyInt DECLARE @MonthStart DateTime DECLARE @MonthEnd DateTime DECLARE @ChildCount Int / * For error logging * / Declare @Error int Declare @ErrorMessage nvarchar(4000) Declare @ErrorProcedure nvarchar

again, insert or update it). My stored procedure: - -- -- -- -- -- -- -- -- -- -- -- -- - CREATE PROCEDURE myProcedure @MyXML xml AS BEGIN DECLARE @DocHandle int CREATE TABLE #Tmp ( [ComponentID] int, [ComponentName] varchar(50) ) EXEC sp_xml_preparedocument @DocHandle OUTPUT, @MyXML INSERT INTO #Tmp (ComponentID, ComponentName) SELECT id name FROM OPENXML (@DocHandle, ' / products / product', 2) WITH (id int, name varchar(50)) EXEC sp_xml_removedocument @DocHandle END GO - -- -- -- -- -- -- -- -- -- -- -- -- - 1) Is there any way to avoid TheParentProductID (1) Stored procedure (1) CREATE PROCEDURE (1) CREATE TABLE (1) Smallint (1) Varchar (1) Declare (1) Inner (1) You need to think "set based" and not "loop over each record Highlight the text within this comment range and run it manually to test your code. declare @rowcount int EXEC dbo.uspProductUpdate ' ' , @rowcount output * / if exists (select * from sysobjects where id = object_id('uspProductUpdate') and sysstat & 0xf = 4) drop procedure uspProductUpdate GO CREATE PROCEDURE dbo

an sp to do something like this. Here is it. if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DB_BACKUP_SETTINGS_T]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[DB_BACKUP_SETTINGS_T] GO CREATE TABLE [dbo].[DB_BACKUP_SETTINGS_T] ( [DBBACKUPSCHEDULEID] [int] NOT NULL , [DBID] [int] NOT NULL , [SCHEDULE int] NOT NULL , [DBNAME] [varchar] (100) COLLATE Latin1_General_CI_AS NOT NULL , [PRIMARYLOCATION] [varchar] (500) COLLATE Latin1_General_CI_AS NULL , [SECONDARYLOCATION] [varchar] (500) COLLATE Latin1_General_CI_AS NULL , [DIFFERENTIA18PM] [int] NULL , [DIFFERENTIA12PM] [int] NULL , [TRANSACTIONLOG] [int] NULL , [REINDEXTHISDB] [bit] NULL , [INDEXSCRIPT] [varchar] (50) COLLATE Latin1_General_CI_AS NULL , [IGNORE] [bit] NULL ) ON [PRIMARY THIS SP IS RESPONSABLE FOR DOING THE FULL BACKUPS OF THE * / / * DATABASE ON THIS SERVER * / / * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * / DECLARE @FILEEXISTS INT DECLARE @DB_TO_BACKUP_CNT INT DECLARE @LOOP_COUNT_I INT DECLARE @BKUP_STRING VARCHAR(500) DECLARE @VCHCOMMAND VARCHAR

disk space and failed. Any ideas / suggestions is appreciated. Thanks. USE XXXXX SET NOCOUNT ON DECLARE @sdbname varchar(25) set @sdbname = 'XXXXX' - - set dboption to trunc on checkpoint exec sp_dboption @dbname = @sdbname, @optname = 'trunc. log on chkpt.', @optvalue = 'true' DECLARE @tablename VARCHAR (128) DECLARE @owner VARCHAR (128 DECLARE @execstr VARCHAR (255) DECLARE @objectid INT DECLARE @indexid INT DECLARE @frag DECIMAL DECLARE @maxfrag DECIMAL set @execstr = 'use '+ @sdbname EXEC (@execstr) - - Decide on the