Convert Existing Stored Procedure To CLR Stored Procedure

See more:

VB

CLR

stored-procedure

Good day all!!!

I have just started learning about CLR Stored Procedures and I wanted to know how to convert my existing stored procedures to CLR Stored Procedures. Could you show me, with code, what this stored procedure should look like. I'm using VS 2010, VB.Net, MSSQL Server 2008.

ALTER PROCEDURE [dbo].[user_login]
    -- Add the parameters for the stored procedure here
    @User_UserName VARCHAR(50),
    @User_Password VARCHAR(50),
    @Station VARCHAR(50),
    @Users_RowID INT OUTPUT,
    @Users_Name VARCHAR(100) OUTPUT,
    @Success BIT OUTPUT,
    @Default_Message VARCHAR(200) OUTPUT
AS

BEGIN TRANSACTION
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT OFF;

    DECLARE @rowsaffected INT
    DECLARE @User_Status_RowID INT
    DECLARE @User_Cursor CURSOR

    -- Insert statements for procedure here
    SET @Success = 0
    SET @Users_RowID = 0
    SET @Users_Name = ''
    SET @User_Status_RowID = 0
    
    IF EXISTS (SELECT * FROM [dbo].[user] WHERE User_UserName = @User_UserName AND CONVERT(varbinary, User_Password) = CONVERT(varbinary, @User_Password))
        BEGIN             
            SELECT @Users_RowID = User_RowID, @User_Status_RowID = User_Status_RowID
                FROM dbo.[user]
                WHERE User_UserName = @User_UserName AND User_Password = @User_Password
                
            SELECT @Users_Name = (User_SName + ', ' + User_FName + '. ' + Title_Descr)
                FROM dbo.[user]
                    INNER JOIN dbo.title ON (dbo.[user].Title_RowID = dbo.title.Title_RowID)
                WHERE User_UserName = @User_UserName AND User_Password = @User_Password

            IF ((LOWER(@User_UserName) <> 'administrator') AND (@User_Status_RowID = 1))
                BEGIN
                    SET @Default_Message = 'User ''' + @Users_Name + ''' is already logged in.'
                    SET @Success = 0
                    RETURN
                END
            ELSE
                BEGIN                    
                    EXEC [dbo].[user_status_update] @User_UserName, @Station, 1, @rowsaffected OUTPUT
                    
                    IF ((@rowsaffected = 2) AND (@@ERROR = 0))
                        BEGIN
                            SET @Default_Message = 'You have been logged in successfully'
                            SET @Success = 1
                        END
                    ELSE
                        BEGIN
                            ROLLBACK TRANSACTION
                            SET @Default_Message = 'An error occured while attempting to log you in. Please try again'
                            SET @Success = 0
                            RETURN
                        END
                END
        END
    ELSE
        BEGIN
            ROLLBACK TRANSACTION
            SET @Default_Message = 'Invalid username and/or password. Try again'
            SET @Success = 0
            RETURN
        END

    SET NOCOUNT ON;

COMMIT TRANSACTION