Hi Friends,
How to write Cursor used CLR Stored procedures in C#.
Loading
Know the answer? Post it — somebody with the same question will find it here.
Sign in to answer this question
It is the same account you read, post and publish with — and you will come straight back to this page.
jagadish patilPosted May 29, 2009, 8:39 AM
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 JainPosted May 29, 2009, 10:05 AM
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.