Params for table value functions causing errors.

[jonmor]

Keep getting the message detailed below - this error prevents the entire database from updating to subversion and also incremental changes to the code being made.

Errors occured whilst parsing file C:\Documents and Settings\********\Local Settings\Application Data\Red Gate\SQL Source Control 0\Transients\piohmrkf.sjq\Stored Procedures\dbo.usp_PriceEditor_InjectPermissionChange_PriceEditor.sql

'line 23:47: unexpected token: ["DEFAULT",<656>,line=23,col=47] [char=768]'

'line 23:47: unexpected token: ["DEFAULT",<656>,line=23,col=47] [char=768]'

The code causing the problem is below

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [usp_PriceEditor_InjectPermissionChange_PriceEditor]
(
@Username AS VARCHAR(50),
@Allowed AS BIT
)
AS
/*#################################################################################################
Description: Injects a new User if not presnt and assigns PriceEditorPermissions
Author: *********
Date: 2007-05-08
###################################################################################################*/

IF NOT EXISTS ( SELECT * FROM dbo.udf_UserDetails(@Username))
BEGIN
INSERT INTO dbo.udf_UserDetails(DEFAULT) /*TODO trigger on this as will only update the Users table
Person table would also need update or rows will not be
returned.*/
([sUserName])
VALUES
(@Username)
END

UPDATE dbo.udf_UserDetails(@Username)
SET
[bPermPriceEditor] = @Allowed

[sherr]

Hello,

In order to replicate this issue, could you please post or email support@red-gate.com the script for dbo.udf_UserDetails?

If by email, please reference our internal support number SOC-1197.

Thank you!
Stephanie M. Herr
Project Manager

[jonmor]

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ====================================================
-- Author: ***********
-- Create date: 10/10/2007
-- Description: Returns user details from the Config DB
-- for the passed in UserName or all
-- users if UserName not passed in
-- ====================================================
ALTER FUNCTION [udf_UserDetails]
(
@sUserName varchar(50) = NULL
)
RETURNS TABLE
AS
RETURN
(
SELECT U.sUserName as sUserName,
P.sFirstName as sFName,
P.sLastName as sLName,
P.sEmail as sEMail,
P.sFullName as sFullName,
U.bPermPriceEditor,
U.bAdmin,
U.sFavouritePCode,
U.iUID
FROM dbo.Users U
INNER JOIN dbo.CONFIG_DB_Person P
ON U.sUserName = P.sUserName
WHERE UPPER(U.sUserName) = UPPER(@sUserName)
OR @sUserName IS NULL
)