将现有存储过程转换为CLR存储过程 [英] Convert Existing Stored Procedure To CLR Stored Procedure

问题描述

大家好！!!!

我刚刚开始学习CLR存储过程，我想知道如何将现有的存储过程转换为CLR存储过程.您能否用代码向我展示此存储过程的外观.我正在使用VS 2010，VB.Net，MSSQL Server2008.

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

推荐答案

查看 CLR存储过程和创建步骤按步骤 [ ^ ]，在CodeProject上.

Take a look at CLR Stored Procedure and Creating It Step by Step[^], here on CodeProject.

这篇关于将现有存储过程转换为CLR存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！