Alter Stored Procedure on MS SQL Server 2012 fails; error #178

[expired user #7777]'s profile image [expired user #7777] posted 10 years ago in General Permalink
I am trying to alter a stored procedure (a function actually) on a MS SQL Server 2012 database. I love Heidi (8.3.0.4694)

When I click 'save' I get an error: SQL Error (178): Incorrect syntax near '@WaveUserId'
A RETURN statement with a return value cannot be used in this context.

Trace follows:
----------------------------------------------------

SELECT ROUTINE_NAME FROM "information_schema"."ROUTINES" WHERE ROUTINE_SCHEMA = 'Vertical_2_0_12_QE' AND ROUTINE_TYPE = 'FUNCTION';
CREATE FUNCTION "HeidiSQL_temproutine_1"("@WaveUserId" nvarchar(250))
RETURNS INT
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
DECLARE @count as int
SET @count= 0
/* SELECT @count = count(CRMLinkServiceLogID) FROM CRMLinkServiceLog WHERE WaveUserID =@WaveUserId and LogType = 'Exception' */
RETURN @count
END;
/* SQL Error (178): Incorrect syntax near '@WaveUserId'
A RETURN statement with a return value cannot be used in this context. */

----------------------------------------------------

I also tried to ALTER FUNCTION and got same error code, different location.

----------------------------------------------------
USE [Vertical_2_0_12_QE]
GO
/****** Object: UserDefinedFunction [dbo].[GetCRMLinkUserErrorCount] Script Date: 02/14/2014 13:04:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[GetCRMLinkUserErrorCount](@WaveUserId nvarchar(250))
RETURNS INT
BEGIN
DECLARE @count as int
SET @count= 0
-- SELECT @count = count(CRMLinkServiceLogID) FROM CRMLinkServiceLog WHERE WaveUserID =@WaveUserId and LogType = 'Exception'
RETURN @count
END;
/* SQL Error (178): Incorrect syntax near 'GO'
A RETURN statement with a return value cannot be used in this context. */
----------------------------------------------------

I realize Heidi may not support this for SQL Server 2012 - but I wanted to try.
Thanks, Scott
[expired user #7777]'s profile image [expired user #7777] posted 10 years ago Permalink
I ended up downloading Microsoft SQL Server 2012: SQL Server Management Studio and that worked for me. I would rather use Heidi for my SQL work. Oh well.

Please login to leave a reply, or register at first.