Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
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
 
Posted 4-Nov-12 23:05pm
azinyama1.2K

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 459
1 Maciej Los 325
2 Sergey Alexandrovich Kryukov 234
3 DamithSL 233
4 BillWoodruff 200
0 OriginalGriff 7,168
1 Sergey Alexandrovich Kryukov 6,377
2 DamithSL 5,461
3 Manas Bhardwaj 4,876
4 Maciej Los 4,450


Advertise | Privacy | Mobile
Web04 | 2.8.1411023.1 | Last Updated 5 Nov 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100