CODEWITHSUNDEEP
sql-serverstored-procedures
Bill
Bill

Reputation: 1477

Need help making my stored procedure more efficient

I would like some help making my SQL Server 2016 stored procedure more efficient. I got it to work and that is 50% of my battle but I know that many (if not most) of you folks have much more experience with SQL Server stored procedures than I do.

My code so far:

DECLARE @U1A nvarchar(50), @U2A nvarchar(50),
        @U3A nvarchar(50), @U4A nvarchar(50),
        @U5A nvarchar(50), @U6A nvarchar(50),
        @U7A nvarchar(50), @U8A nvarchar(50),
        @U9A nvarchar(50)

DECLARE @Jsonstring  nvarchar(max)
DECLARE @recCount int

SELECT 
    @recCount = COUNT(*) 
FROM 
    [dbo].[Staging_PersonalInformation] 
WHERE 
    jsondata IS NULL

WHILE @recCount > 0
BEGIN
    SELECT TOP 1 
        @U1A = [FirstName], @U2A = [MiddleName], 
        @U3A = [LastName], @U4A = [EmailAddress],
        @U5A = eraCommons, @U6A = [PositionTitle],
        @U7A = [MyNCBILink], @U8A = [UniqueID],
        @U9A = [ReferenceID]
    FROM 
        [dbo].[Staging_PersonalInformation]
    WHERE 
        jsondata IS NULL

    SET @Jsonstring = '[{"name":"FirstName","value":"'+isnull(@U1A, '')+'"},{"name":"Middlename","value":"'+ISNULL(@U2A, '')+'"},{"name":"LastName","value":"'+isnull(@U3A, '')+'"},{"name":"emailaddress","value":"'+isnull(@U4A, '')+'"},{"name":"eRACommons","value":"'+ISNULL(@U5A, '')+'"},{"name":"positionTitle","value":"'+ISNULL(@U6A, '')+'"},{"name":"MyNCBILink","value":"'+ISNULL(@U7A, '')+'"},{"name":"  uniqueid","value":"'+ISNULL(@U8A, '')+'"},{"name":"ReferenceID","value":"'+ISNULL(@U9A, '')+'"}]'

    UPDATE Staging_PersonalInformation
    SET JsonData = @Jsonstring
    WHERE (EmailAddress = @U4A);

    SET @recCount = @recCount - 1
END

The purpose of this is to take the individual column values and make a string that my sterilized JavaScript form can repopulate. I would rather store the string than to make it on the fly each time.

Thanks for your help

Upvotes: 0

Views: 58

Answers (2)

SqlZim
SqlZim

Reputation: 38023

The answer by Sean Lange is a great answer, but I am curious as to why you are not taking advantage of SQL Server 2016 support of for json.

I realize that the format is not the same as you specified, so I suppose that could be the reason. Perhaps this format would also work?:

select * 
from Staging_PersonalInformation 
for json auto, include_null_values

dbfiddle.uk demo: http://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=d533c6d3b82fdd7865b3817fba94037d

returns: 

[{"Id":1,"FirstName":"Sean","MiddleName":null,"LastName":"Lange","EmailAddress":null,"PositionTitle":null,"MyNCBILink":null,"UniqueID":"6E6732A9-9FC9-4B6E-8695-AF6BB2DA2152","ReferenceID":0}
,{"Id":2,"FirstName":"Sql","MiddleName":null,"LastName":"Zim","EmailAddress":null,"PositionTitle":null,"MyNCBILink":null,"UniqueID":"FA33808B-E8BE-41B5-AA89-DA8A37503F8F","ReferenceID":0}]

Reference:

Upvotes: 0

Sean Lange
Sean Lange

Reputation: 33571

Well the biggest issue is that looping is horribly inefficient. And since you are always going to update this column based on values already in the table you could use a computed column and avoid all this work entirely.

I would suggest that in the future you give your variable names something meaningful instead of just numbering them.

Here is how you could make this a computed column. You can read more about computed columns here. https://technet.microsoft.com/en-us/library/ms191250.aspx

alter table [dbo].[Staging_PersonalInformation]
add jsondata as '[{"name":"FirstName","value":"' + isnull(FirstName, '')
    + '"},{"name":"Middlename","value":"' + ISNULL(MiddleName, '')
    + '"},{"name":"LastName","value":"' + isnull(LastName, '')
    + '"},{"name":"emailaddress","value":"'+isnull(EmailAddress, '')
    + '"},{"name":"eRACommons","value":"'+ISNULL(eraCommons, '')
    + '"},{"name":"positionTitle","value":"'+ISNULL(PositionTitle, '')
    + '"},{"name":"MyNCBILink","value":"'+ISNULL(MyNCBILink, '')
    + '"},{"name":"  uniqueid","value":"'+ISNULL(UniqueID, '')
    + '"},{"name":"ReferenceID","value":"'+ISNULL(ReferenceID, '')
    + '"}]'

Upvotes: 4

Related Questions