Wondering if it is better practice to use OUTPUT variables (and am I using them right) to return some values to the calling code
or if I should just use a select statement
Thanks,
Willie
Code:
CREATE PROCEDURE [dbo].[InsertOrUpdateFormPageData]
@FormPageDataId int null,
@FormPageId int,
@CreatedBy int null,
@PostedJSONData nvarchar(max),
@UpdatedBy int null,
@PageToReturn int,
@PrevPageId INT OUTPUT,
@NextPageId INT OUTPUT,
@HTML varchar(max) OUTPUT,
@FPId int OUTPUT
AS
BEGIN
Create table #tmp
(
FormPageDataId int,
FormPageId int,
CreatedBy int,
PostedJSONData nvarchar(max),
UpdatedBy int null
)
INSERT INTO #tmp(FormPageDataId,FormPageId,CreatedBy,PostedJSONData,UpdatedBy)
VALUES(@FormPageDataId,@FormPageId,@CreatedBy,@PostedJSONData,@UpdatedBy)
MERGE INTO FormPageData As TGT
USING #tmp As SRC
ON TGT.Id = SRC.FormPageDataId
WHEN MATCHED THEN
UPDATE Set
TGT.PostedJSONData = SRC.PostedJSONData,
TGT.UpdatedBy = SRC.UpdatedBy,
TGT.UpdatedDate = SYSDATETIME()
WHEN NOT MATCHED THEN
INSERT (FormPageId,PostedJSONData,CreatedBy)
VALUES(SRC.FormPageId,SRC.PostedJSONData,SRC.CreatedBy);
SELECT @PrevPageId=fp.PrevPageId, @NextPageId=fp.NextPageId, @HTML=pg.HTML, @FPId = fp.Id
FROM dbo.FormPage fp
JOIN dbo.[Page] pg
ON fp.PageId = pg.Id
WHERE fp.Id = @PageToReturn;
END
or if I should just use a select statement
Code:
ALTER PROCEDURE [dbo].[InsertOrUpdateFormPageData]
@FormPageDataId int null,
@FormPageId int,
@CreatedBy int null,
@PostedJSONData nvarchar(max),
@UpdatedBy int null,
@PageToReturn int
AS
BEGIN
Create table #tmp
(
FormPageDataId int,
FormPageId int,
CreatedBy int,
PostedJSONData nvarchar(max),
UpdatedBy int null
)
INSERT INTO #tmp(FormPageDataId,FormPageId,CreatedBy,PostedJSONData,UpdatedBy)
VALUES(@FormPageDataId,@FormPageId,@CreatedBy,@PostedJSONData,@UpdatedBy)
MERGE INTO FormPageData As TGT
USING #tmp As SRC
ON TGT.Id = SRC.FormPageDataId
WHEN MATCHED THEN
UPDATE Set
TGT.PostedJSONData = SRC.PostedJSONData,
TGT.UpdatedBy = SRC.UpdatedBy,
TGT.UpdatedDate = SYSDATETIME()
WHEN NOT MATCHED THEN
INSERT (FormPageId,PostedJSONData,CreatedBy)
VALUES(SRC.FormPageId,SRC.PostedJSONData,SRC.CreatedBy);
SELECT fp.PrevPageId, fp.NextPageId, pg.HTML, fp.Id
FROM dbo.FormPage fp
JOIN dbo.[Page] pg
ON fp.PageId = pg.Id
WHERE fp.Id = @PageToReturn;
END
Thanks,
Willie