Help with stored procedure 1

[pyttviper]

i am new to SPs and i need some basic answers
i wrote the below querie which returns a number but i want to store that number in a variable.

CREATE PROCEDURE sp_ac6000_metric_load 
AS
SELECT 	count(diffdays)as counts
FROM	(SELECT ID, 
		Initial_Date, 
		getdate() AS Today,
		 DateDiff(day,Initial_Date,getdate()) AS diffDays, 
		left(Active,1) as active, 
		left(Railroad,4)as rail , 
		left(REMOTE_MON_REC,10) as REC 
	FROM 	ac6000_loco_data 
	WHERE Initial_Date<>'' AND 
		ac6000_loco_data.Active='Y'  AND 
		ac6000_loco_data.remote_mon_rec<>'' AND 
		ac6000_loco_data.Railroad='up') as DTA

 

[pmanivas]

if ur question is how to return the count value as a variable then
SELECT VariableName = count(diffdays)as counts
FROM (SELECT ID,
Initial_Date,
getdate() AS Today,
DateDiff(day,Initial_Date,getdate()) AS diffDays,
left(Active,1) as active,
left(Railroad,4)as rail ,
left(REMOTE_MON_REC,10) as REC
FROM ac6000_loco_data
WHERE Initial_Date<>'' AND
ac6000_loco_data.Active='Y' AND
ac6000_loco_data.remote_mon_rec<>'' AND
ac6000_loco_data.Railroad='up')

Let me know its helpful or not

 

[pyttviper]

i tried this and i can't get it to work

CREATE PROCEDURE sp_ac6000_metric_load
AS
@uptotal int
SELECT @uptotal = count(diffdays)as counts
FROM (SELECT ID,
Initial_Date,
getdate() AS Today,
DateDiff(day,Initial_Date,getdate()) AS diffDays,
left(Active,1) as active,
left(Railroad,4)as rail ,
left(REMOTE_MON_REC,10) as REC
FROM ac6000_loco_data
WHERE Initial_Date<>'' AND
ac6000_loco_data.Active='Y' AND
ac6000_loco_data.remote_mon_rec<>'' AND
ac6000_loco_data.Railroad='up') as DTA

 

[jhall156]

you need a 'DECLARE' in front of that @uptotal.

 

[pyttviper]

thanks got it