March 11, 2013 at 10:48 pm
Hi,
I have below table:
ID URL
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.
March 12, 2013 at 2:56 am
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)
...
March 12, 2013 at 11:41 am
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)
March 12, 2013 at 2:33 pm
dsrapid (3/11/2013)
Hi,I have below table:
ID URL
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
-- 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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy