Ignore Param in Stored Procedure

[JaedenRuiner]

Well,

i have a Stored Procedure that Updates a Table, but several of the Parameters are Optional with default values of NULL.

this is the base of the procedure:

ALTER PROCEDURE [dbo].[UpdateEmail] 
	-- Add the parameters for the stored procedure here
	@emid nvarchar(10) = '', 
	@custid nvarchar(6) = '',
	@num	int = 0, 
	@qty	int = NULL,
	@mail   int = NULL
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	IF NOT ((@custid IS NULL) or (@custid = '') or (@num is null) or (@num < 1))
		UPDATE	[dbo].[tbl_Email]
		SET		[ExpQty]=@qty, [MailID]=@mail
		WHERE	[Customer]=@custid AND [Num]=@num
END

now that isn't the whole thing,but looking at the SET line of the UPDATE Statement. is there a way to have the SET portion of the statement "ignore" a parameter value if it is null, basically keeping the original value.

the programmatic effect would be multiple IF blocks:

	IF NOT ((@custid IS NULL) or (@custid = '') or (@num is null) or (@num < 1))
	BEGIN
		IF @qty IS NULL THEN
			UPDATE	[dbo].[tbl_Email]
			SET		[MailID]=@mail
			WHERE	[Customer]=@custid AND [Num]=@num
		ELSE 
			IF @MAILID IS NULL THEN
				UPDATE	[dbo].[tbl_Email]
				SET		[ExpQty]=@qty
				WHERE	[Customer]=@custid AND [Num]=@num
			ELSE	
				UPDATE	[dbo].[tbl_Email]
				SET		[ExpQty]=@qty, [MailID]=@mail
				WHERE	[Customer]=@custid AND [Num]=@num
	END

As you can see that's very involved for a simple idea. I'm just wondering if it is possible to code this with Stored Procedure syntax in a singular "UPDATE" statement that would achieve the same end.

Thanks

 

[Wild Bill]

"is there a way to have the SET portion of the statement "ignore" a parameter value if it is null, basically keeping the original value."

try this

UPDATE [dbo].[tbl_Email]
SET [MailID]=isnull(@mail,[MailID]),
...
WHERE...

 

[Wild Bill]

Wow... Sorry guys, i only now see, that question was asked3 years ago