Problems with SP

[G12Consult]

Hi,

I am trying to run the following SP.

USE [property]
GO
/****** Object:  StoredProcedure [dbo].[uspAddUser]    Script Date: 05/13/2016 00:47:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[uspAddUser]
    @pLogin NVARCHAR(50), 
    @pPassword NVARCHAR(50), 
    @pFirstName NVARCHAR(40) = NULL, 
    @pLastName NVARCHAR(40) = NULL,
    @responseMessage NVARCHAR(250) OUTPUT
AS
BEGIN
    SET NOCOUNT ON

    BEGIN TRY

        INSERT INTO dbo.[User] (LoginName, PasswordHash, FirstName, LastName)
        VALUES(@pLogin, HASHBYTES('SHA2_512', @pPassword), @pFirstName, @pLastName)

        SET @responseMessage='Success'

    END TRY
    BEGIN CATCH
        SET @responseMessage=ERROR_MESSAGE() 
    END CATCH

END

The SP is not adding the new user. It is giving me the error:

Cannot insert the value NULL into column 'PasswordHash', table 'property.dbo.User'; column does not allow nulls. INSERT fails.

I have specified a password value so why is it parsing NULL?

Running the code like:

USE [property]
GO

DECLARE	@return_value int,
		@responseMessage nvarchar(250)

EXEC	@return_value = [dbo].[uspAddUser]
		@pLogin = N'Admin',
		@pPassword = N'Admin',
		@pFirstName = N'Andrew',
		@pLastName = N'Prior',
		@responseMessage = @responseMessage OUTPUT

SELECT	@responseMessage as N'@responseMessage'

SELECT	'Return Value' = @return_value

GO

 

[gmmastros]

The problem is with the HASHBYTES function. It is returning null.

I suspect that you are using an older version of SQL Server. When I ran the following code on SQL2005, I got a null return. Running the same thing on sql server 2012, I get a real value.

Select HASHBYTES('SHA2_512', 'Admin')

If you're not sure what version your SQL Server database engine is, you can run this:

select @@VERSION

You may need to use a different hashing algorithm.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[G12Consult]

Sql2008r2 I am using

 

[fredericofonseca]

https://msdn.microsoft.com/en-ie/library/ms174415.aspx

256 bits (32 bytes) for SHA2_256, and 512 bits (64 bytes) for SHA2_512 applies to SQL Server 2012 through SQL Server 2016.


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[G12Consult]

Ok thank you.

I will upgrade sql server I reckon.

What version of visual studio can I connect with sql server 2012 or 2014.

At the moment I am running VS 2010 and awl server 2008 R2.

Is it worth getting sql server 2014

 

[Olaf Doschke]

About the value of versions:
Generally considering an upgrade why go with anything lower than the most recent version?
2016 is soon to come, you might consider waiting for it.

2012 had the major change of very useful new windowing function (windows not meaning winforms, but windows of data rows additional to groups and partitions) allowing to compute things like running sums, comparison or inclusion of next/previous values in some order and partitioning etc etc.

What's new?

https://technet.microsoft.com/en-us/library/bb500435(v=sql.110).aspx

https://msdn.microsoft.com/library/bb500435(v=sql.120).aspx

https://msdn.microsoft.com/en-us/library/bb500435.aspx

Bye, Olaf.