June 20, 2003 at 1:14 pm
It's not working, but seems simple. I just want it to take whatever parameters I give and do the update.
Here's the paste out of query Analyzer
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE dbo.DNN_photo_categories_U
(@id int,
@module_id int = NULL,
@category_name varchar(50) = NULL,
@notes varchar(4000) = NULL,
@date datetime = NULL,
@approved bit = NULL,
@archived bit = NULL)
AS
DECLARE @UPDATE_STRING NVARCHAR(1000)
DECLARE @ITEM_STRING NVARCHAR(1000)
IF @module_id IS NOT NULL
Begin
SET @ITEM_STRING = 'module_id = @module_id,'
END
IF @category_name IS NOT NULL
Begin
SET @ITEM_STRING = @ITEM_STRING + ' category_name = @category_name,'
END
IF @notes IS NOT NULL
Begin
SET @ITEM_STRING = @ITEM_STRING + ' notes = @notes,'
END
IF @date IS NOT NULL
Begin
SET @ITEM_STRING = @ITEM_STRING + ' date = @date,'
END
IF @approved IS NOT NULL
Begin
SET @ITEM_STRING = @ITEM_STRING + ' approved = @approved,'
END
IF @archived IS NOT NULL
Begin
SET @ITEM_STRING = @ITEM_STRING + ' archived = @archived,'
END
SET @ITEM_STRING = LEFT(@ITEM_STRING,(LEN(@ITEM_STRING)-1))
SET @UPDATE_STRING = 'UPDATE DNN_photo_categories SET ' + @ITEM_STRING + ' WHERE id = @id'
PRINT @UPDATE_STRING
EXEC sp_executesql @UPDATE_STRING,
N'@id int, @module_id int, @category_name varchar(50), @notes varchar(4000), @date datetime, @approved bit, @archived bit',
@id, @module_id, @category_name, @notes, @date, @approved, @archived
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
June 21, 2003 at 12:35 am
wpballar i tried this its working perfectly fine.
can u post what problem are you facing with this.
June 23, 2003 at 6:26 am
try this instead:
ALTER PROCEDURE dbo.DNN_photo_categories_U
(
@id int
, @module_id int = NULL
, @category_name varchar(50) = NULL
, @notes varchar(4000) = NULL
, @date datetime = NULL
, @approved bit = NULL
, @archived bit = NULL
)
AS
--
UPDATE DNN_photo_categories
SET
module_id = ISNULL(@module_id, module_id)
, category_name = ISNULL(@category_name, category_name)
, notes = ISNULL(@notes, notes)
, date = ISNULL(@date, date)
, approved = ISNULL(@approved, approved)
, archived = ISNULL(@archived, archived)
WHERE id = @id
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply