June 25, 2015 at 12:55 am
I need a help to fix the below SP which is having cursor output with an alternate logic which can make the SP work.(may be using temp table or any other option)
CREATE PROCEDURE Get_IDS
/*
* SSMA warning messages:
* O2SS0356: Conversion from NUMBER datatype can cause data loss.
*/
@p_uid float(53),
@return_value_argument varchar(8000) OUTPUT
AS
/*Generated by SQL Server Migration Assistant for Oracle version 6.0.0.*/
BEGIN
DECLARE
@v_rc CURSOR,
@v_query varchar(100),
/*
* SSMA warning messages:
* O2SS0356: Conversion from NUMBER datatype can cause data loss.
*/
@v_login_id float(53)
SELECT @v_login_id = ID
FROM dbo.LOGINS
WHERE REL_ID = @p_uid
SET @v_query = 'SELECT id FROM Test_'+ ISNULL(CAST(@v_login_id AS nvarchar(max)), '')
DECLARE
@auxiliary_cursor_definition_sql nvarchar(max)
DECLARE
@auxiliary_exec_param nvarchar(max)
IF (cursor_status('variable', N'@v_rc') > -2)
DEALLOCATE @v_rc
/*
* SSMA error messages:
* O2SS0157: The OPEN...FOR statement will be converted, but the dynamic string must be converted manually. */
SET @auxiliary_cursor_definition_sql = 'SET @auxiliary_tmp_cursor = CURSOR LOCAL FOR ' + @v_query + '; OPEN @auxiliary_tmp_cursor'
SET @auxiliary_exec_param = '@auxiliary_tmp_cursor cursor OUTPUT'
EXECUTE sp_executesql @auxiliary_cursor_definition_sql, @auxiliary_exec_param, @v_rc OUTPUT
/*
* SSMA error messages:
* O2SS0245: The conversion of cursors in return statements is not supported.*/
RETURN @v_rc
END
GO
June 25, 2015 at 2:56 am
This procedure has been migrated from Oracle. Returning a cursor from a stored procedure is a common pattern in Oracle, but much less common in SQL Server.
This is because Oracle stored procedures cannot return a result set, while SQL Server stored procedures can. Change the code to return the data instead of the cursor.
-- Gianluca Sartori
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply