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