I have written the following Stored Procedure:
USE [BHPropertyInfo] GO /****** Object: StoredProcedure [dbo].[usp_UnitAvgByRM] Script Date: 08/26/2010 16:47:21 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- ============================================= ALTER PROCEDURE [dbo].[usp_UnitAvgByRM] -- Add the parameters for the stored procedure here (@RM VARCHAR(255)) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here SELECT LEFT(PROPERTY_UNITS,4) FROM dbo.PROPERTY_TABLE WHERE PROPERTY_RM like '%' + @RM + '%' END
I wanted to originally get the adverage number of units for a particular RM. Note also that my PROPERTY_UNIT is formated as such "#### MM/DD/YYYY" <##= units,date = aquisition. This format has been around since before me and I am working on removing it completely but for now I work around it. I had the idea of
SELECT AVG(cast(left(PROPERTY_TABLE,4) as int)) from PROPERTY_TABLE where PROPERTY_RM like '%' + @RM + '%'
this threw errors though. I then thought I would just get the avg once the Units were returned so I tried.
Select avg(exec usp_UnitAvgByRM 'name')
SELECT Agv from exec usp_......
Where am I going wrong here? This is my first time playing with stored procedures.