How to define an SQL Server function withing DDT?

StrataFrame Forum

How to define an SQL Server function withing DDT?

http://forum.strataframe.net/Topic25095.aspx

By Edhy Rijo - 11/1/2009

I have a SQL Server function which I have not problem creating it in SQL Server Manager Studio, but when try to create it in the DDT I always get an error when try to deploy the UDF.

Here the code I have in the DDT, also look at the udfSample.png image:

(@FK_Customers INT)

RETURNS varchar(20)

AS

BEGIN

DECLARE @ParkingNumbers varchar(20)

DECLARE @IsInactive BIT

SET @ParkingNumbers = ''

SET @IsInactive = 0

SELECT @ParkingNumbers = @ParkingNumbers + LTRIM(cast(v.ParkingNumber as varchar(3))) + ','

FROM

  dbo.Vehicles V

WHERE

  (V.FK_Customers = @FK_Customers AND

   V.IsInactive = @IsInactive)

ORDER BY

  v.ParkingNumber

RETURN @ParkingNumbers

END

Here is the code I use in Manager Studio with not problem:

CREATE FUNCTION dbo.udf_GetParkingNumbers (@FK_Customers INT)

RETURNS varchar(20)

AS

BEGIN

DECLARE @ParkingNumbers varchar(20)

DECLARE @IsInactive BIT

SET @ParkingNumbers = ''

SET @IsInactive = 0

SELECT @ParkingNumbers = @ParkingNumbers + LTRIM(cast(v.ParkingNumber as varchar(3))) + ','

FROM

  dbo.Vehicles V

WHERE

  (V.FK_Customers = @FK_Customers AND

   V.IsInactive = @IsInactive)

ORDER BY

  v.ParkingNumber

-- SELECT @ParkingNumbers

RETURN @ParkingNumbers

END

GO

The error I get from DDT when try to deploy the UDF is:

X 11/1/2009 11:09:09 PM-> An error occurred while creating the stored procedure [dbo].[spGetParkingNumbers].

X 11/1/2009 11:09:09 PM-> SqlException        

X 11/1/2009 11:09:09 PM->   Incorrect syntax near the keyword 'DECLARE'.        

X 11/1/2009 11:09:09 PM-> Incorrect syntax near the keyword 'AS'.        

X 11/1/2009 11:09:09 PM-> Must declare the scalar variable "@ParkingNumbers".        

X 11/1/2009 11:09:09 PM-> Must declare the scalar variable "@ParkingNumbers".        

X 11/1/2009 11:09:09 PM->         

X 11/1/2009 11:09:09 PM-> Source     : .Net SqlClient Data Provider        

X 11/1/2009 11:09:09 PM->         

X 11/1/2009 11:09:09 PM-> Stack Trace:         

X 11/1/2009 11:09:09 PM->    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)        

X 11/1/2009 11:09:09 PM->    at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)        

X 11/1/2009 11:09:09 PM->    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)        

X 11/1/2009 11:09:09 PM->    at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)        

X 11/1/2009 11:09:09 PM->    at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)        

X 11/1/2009 11:09:09 PM->    at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)        

X 11/1/2009 11:09:09 PM->    at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()        

X 11/1/2009 11:09:09 PM->    at MicroFour.StrataFrame.DBEngine.SQL.DatabaseMigrator.DeploySprocsOfPriority(Database parent, DatabaseInfo dbInfo, Int32 priority)

What am I doing wrong in DDT to get this error, while the same code works just fine in Manager Studio?

By Dustin Taylor - 11/3/2009

This is probably a silly question, but you are creating this as a User Defined Function rather than a Stored Procedure in the DDT, correct?

I just gave this a shot on a test database, and the following worked fine as a UDF deployed to a new database:

(@FK_Customers INT)
RETURNS varchar(20)
AS
BEGIN
DECLARE @ParkingNumbers varchar(20)
DECLARE @IsInactive BIT
SET @ParkingNumbers = ''
SET @IsInactive = 0
SELECT @ParkingNumbers = @ParkingNumbers + LTRIM(cast(v.ParkingNumber as varchar(3))) + ','
FROM
dbo.Vehicles V
WHERE
(V.FK_Customers = @FK_Customers AND
V.IsInactive = @IsInactive)
ORDER BY
v.ParkingNumber
-- SELECT @ParkingNumbers
RETURN @ParkingNumbers
END

That above was in the "Procedure Code" section of hte User-Defined Function Properties, while the "Pre-Create/Environment Code" was left as the default:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

By Edhy Rijo - 11/3/2009

Hi Dustin,

Thanks for the confirmation. I deleted my code from DDT and copied again and now it works. I guess some invisible character may have been there before.