Home Get value from store or store and get if not exists
Reply: 0

Get value from store or store and get if not exists

user2509
1#
user2509 Published in July 22, 2018, 8:36 am

what I want to achieve is a way to get previously generated or generate, store and return data, in a way, which could be used as function - needed to join or APPLY in UPDATE scripts.

So, having data generate function:

ALTER FUNCTION generateData()
    RETURNS NVARCHAR(20)
AS
BEGIN
    RETURN 'asdyukyuk' --some rng related algorithm
END

And "get or generate, save and get" procedure:

    ALTER PROCEDURE getOrGenerateAndGet (@orig NVARCHAR(20), @result NVARCHAR(20) OUTPUT)
    AS
        BEGIN
            IF @orig IS NULL
                BEGIN
                    SET @result = NULL
                END
            ELSE
                BEGIN
                    DECLARE @hash VARBINARY(64) = dbo.getHash(@orig)
                    SELECT @result = GENERATED_DATA FROM STORED_DATA WHERE HASH = @hash
                    IF @result IS NULL
                        BEGIN
                            SET @result = dbo.generateData()
                            INSERT INTO STORED_DATA (HASH, GENERATED_DATA) VALUES (@hash, @result)
                        END
                END
        RETURN 1
        END;
    GO

And UPDATE script:

UPDATE FRAGILE_DATA SET FRAGILE_COLUMN = getOrGenerateAndGet(FRAGILE_COLUMN)

it's obviously not working, because getOrGenerateAndGet is not function.

However, with function way:

    CREATE FUNCTION getOrGenerateAndGet (@orig NVARCHAR(20))
        RETURNS NVARCHAR(20)
    AS
        BEGIN
            DECLARE @result NVARCHAR(20)
            IF @orig IS NULL
                BEGIN
                    SET @result = NULL
                END
            ELSE
                BEGIN
                    DECLARE @hash VARBINARY(64) = dbo.getHash(@orig)
                    SELECT @result = GENERATED_DATA FROM STORED_DATA WHERE HASH = @hash
                    IF @result IS NULL
                        BEGIN
                            SELECT @result = dbo.generateData()
                            EXEC sp_executesql N'INSERT INTO STORED_DATA (HASH, GENERATED_DATA) VALUES (@hash, @result)'
                        END
                END
            RETURN @result
        END;
    GO

it's still not working, because "Only functions and some extended stored procedures can be executed from within a function."

Is there any way to achieve this, without enabling sql command shell?

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.309058 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO