Creating Stored Procedure

  • Hi,

    I have below table:

    ID URL

    1 https://google.com

    2 https://facebook.com

    3 https://yahoo.com

    4 https://gmail.com

    I am trying to create a procedure where I take the input for URL column.

    I am defining 5 paramters for procedure, url1, url2, url3, url4, proc_msg

    I need to do below checks:

    If value is null, existing value should not change. (means table is not updated)- I was using NVL2 function for this.

    If value is provided, check if value starts with 'https://'. If it is valid update the table. Else display msg- invalid url.

    User may not i/p all values at one time. he may update only 1 url or may be more at a particular time.

  • Hi)

    if there are 4 rows in the table and 4 parameters to update this table, you need to check parameters individually and make separate updates:

    declare @error_message varchar(30)

    set @error_message = 'invalid url'

    if (@url1 is not null)

    if ( @url1 like 'https://%' )

    --update row

    else

    RAISERROR ( @error_message, 10, 1)

    if (@url2 is not null)

    if ( @url2 like 'https://%' )

    --update row

    else

    RAISERROR ( @error_message, 10, 1)

    ...

  • Something like this maybe?

    declare @error_message varchar(30)

    set @error_message = 'invalid url'

    update yourtable set url =

    (case

    when ID = 1 then

    case when @url1 is not null

    case when left(@url1,8) = 'https://' then @url1

    else raiserror(@error_message,10,1)

    end

    else url

    end

    when ID = 2 then

    case when @url2 is not null

    case when left(@url2,8) = 'https://' then @url2

    else raiserror(@error_message,10,1)

    end

    else url

    end

    when ID = 3 then

    case when @url3 is not null

    case when left(@url1,8) = 'https://' then @url3

    else raiserror(@error_message,10,1)

    end

    else url

    end

    when ID = 4 then

    case when @url4 is not null

    case when left(@url1,8) = 'https://' then @url4

    else raiserror(@error_message,10,1)

    end

    else url

    end

    end)

  • dsrapid (3/11/2013)


    Hi,

    I have below table:

    ID URL

    1 https://google.com

    2 https://facebook.com

    3 https://yahoo.com

    4 https://gmail.com

    I am trying to create a procedure where I take the input for URL column.

    I am defining 5 paramters for procedure, url1, url2, url3, url4, proc_msg

    I need to do below checks:

    If value is null, existing value should not change. (means table is not updated)- I was using NVL2 function for this.

    If value is provided, check if value starts with 'https://'. If it is valid update the table. Else display msg- invalid url.

    User may not i/p all values at one time. he may update only 1 url or may be more at a particular time.

    I think this should do the trick:

    --test data

    IF OBJECT_ID('tempdb..#urls') IS NOT NULL

    DROP TABLE #urls;

    CREATE TABLE #urls (id int primary key, url varchar(100) NOT NULL);

    INSERT INTO #urls VALUES (1,'https://google.com'), (2,'https://facebook.com'), (3,'https://yahoo.com'), (4,'https://gmail.com');

    GO

    USE {YOURDB}

    GO

    --1st, no need for @proc_msg

    CREATE PROC dbo.urlupdate

    (@url1 varchar(100), @url2 varchar(100), @url3 varchar(100), @url4 varchar(100))

    AS

    SET NOCOUNT ON;

    DECLARE @input TABLE(id int primary key, url varchar(100), bad_url_flag varchar(100));

    ;WITH input(id,url) AS

    (SELECT 1, @url1 UNION ALL SELECT 2, @url2 UNION ALL

    SELECT 3, @url3 UNION ALL SELECT 4, @url4)

    INSERT INTO @input

    SELECT id, url, CASE WHEN LEFT(LTRIM(url),8)='https://' THEN 0 ELSE 1 END

    FROM input

    WHERE url IS NOT NULL;

    IF EXISTS(SELECT * FROM @input WHERE bad_url_flag=1)

    BEGIN

    DECLARE @bad_url varchar(5), @bad_url_val varchar(100);

    SELECT TOP 1

    @bad_url='@url'+CAST(id AS varchar(2)),

    @bad_url_val=url

    FROM @input WHERE bad_url_flag=1;

    PRINT 'We need URLs that are NULL or begin with "https://"'

    PRINT 'For '+CAST(@bad_url AS varchar(5))+', you entered "'+@bad_url_val+'"'

    RETURN;

    END;

    ELSE

    SELECTu.id,

    u.url AS old_val,

    ISNULL(i.url,'<unchanged>') AS new_val

    INTO #changelog

    FROM @input AS i

    FULL JOIN #urls AS u ON u.id = i.id;

    UPDATE u

    SET u.url = i.url

    FROM @input AS i

    JOIN #urls AS u ON u.id = i.id;

    BEGIN

    SELECT * FROM #changelog;

    DROP TABLE #changelog;

    RETURN;

    END

    GO

    EXEC dbo.urlupdate @url1='https://www.sqlservercentral.com', @url2='https://www.mulletsgalore.com', @url3=NULL, @url4=NULL

    --cleanup

    DROP TABLE #urls;

    GO

    One thing to note: you don't need @proc_msg as a parameter.

    Edit: added note about @proc_msg

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply