Cursor based CLR Stored procedure

Question

Hi Friends, How to write Cursor used CLR Stored procedures in C#.

jagadish patil · Answer

Hi Friends, I am new to CLR SP's. Its very simple Just write sp in sql server and test then just copy and paste like. strQuery = @"DECLARE @SPName VARCHAR(255) DECLARE SPNameCursor CURSOR FOR SELECT STOREDPROCEDURENAME FROM table OPEN SPNameCursor FETCH NEXT FROM SPNameCursor INTO @SPName WHILE @@FETCH_STATUS = 0 BEGIN insert query FETCH NEXT FROM SPNameCursor INTO @SPName END CLOSE SPNameCursor DEALLOCATE SPNameCursor" ; then List Parameter > objParams = new List Parameter >(); objParams.Add( new Parameter ( "@param1" , val)); objParams.Add( new Parameter ( "@parame2" , valDate)); int output = objSQLHelper.ExecuteNonQuery(strQuery, CommandType .Text, objParams); return output;

Mohit Jain · Answer

Hi Jagdish, The way you have created the CLR Stored Procedure is correct but Cursor are not recommended to be used. Instead of cursor I would suggest you to use Temporary Table or Table variable which provide good performance over cursor.

Cursor based CLR Stored procedure

Answers (2)