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?
Edhy Rijo