Stored procedure Errors! ><

[MichaelWClark]

Here are the 2 procedures in question and the code that I use to call it. I will follow it all up with the error that is being produced.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[usp_GetPropertyData](
	@page int,
    @page_len int,
    @sortfield varchar(100),
    @desc bit  
)
as
begin
	set fmtonly off
	set nocount on
	declare @rowcount int;
	declare @innerrows int
	select @rowcount=count(*) from dbo.Investors
	
	if (@page*@page_len) > @rowcount
		begin
			set @page=(@rowcount/@page_len)
		end

	set @innerrows = @rowcount - (@page * @page_len)

	
       declare @sortdesc varchar(100)
       declare @sortasc varchar(100)
       declare @a varchar(6)
       declare @b varchar(6)
       IF @desc=0
               BEGIN
                   set @a = ' DESC '
                   set @b = ' ASC '
               END
       ELSE
               BEGIN
                   set @a = ' ASC '
                   set @b = ' DESC '
               END    	
		


       DECLARE @sql nvarchar(1000)
       SET @sql = 'SELECT TOP ' + STR(@page_len) + ' [PropertyName], dbo.sp_PropertyTotal(PropertyID) as PropertyTotal, PropertyID as PropertyID FROM
               (
                   SELECT TOP ' + STR(@innerrows) + ' [PropertyName], 
						dbo.sp_PropertyTotal(PropertyID) as PropertyTotal, 
						PropertyID as PropertyID
       
                   FROM
                   [dbo].[PropertyGroup] 
                   ORDER BY [dbo].[PropertyGroup].' + @sortfield +  ' 
               ) Alias
               ORDER BY Alias.' + @sortfield 
       EXEC (@sql);
	end

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_PropertyTotal]
	-- Add the parameters for the stored procedure here
@PropertyID as int,
@Total as money output
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 @Total=sum(CapitalInvestmentAmmount) from dbo.Distributions where PropertyID=@PropertyID
END

exec dbo.usp_GetPropertyData 1,15,'',1

Msg 102, Level 15, State 1, Line 10
Incorrect syntax near ')'.

[vuyiswamb]

try this

exec dbo.usp_GetPropertyData(1,15,'',1)

[Brillig]

You need to add your parenthesis to sp_PropertyTotal around the parameters.

(Also,you shouldn't use "sp_" to prefix your stored procs, it makes SQL think it's a system stored procedure and it takes longer to look in two places for it).

[shoestring]

You are passing in '' as a the @sortfield so the sql that is created in the stored proc has wrong order clause : "ORDER BY [dbo].[PropertyGroup]."

The stored proc should check that @sortfield is something sensible before adding the order by.