How to Select a Record and use the result to Update

Dear Code Masters,

This your humble disciple again, humbling saying thank you for all the suggestions and tips that had made my coding improving by the day.

My masters i have a challenge with Stored Procedure, I want to write within my stored procedure an SQL statement that will do the following :

Create Procedure InsertDocument
(
@Category nvarchar(1000),
@FileName nvarchar(1500),
@ContentType Nvarchar(1500),
@Data varbinary(MAX),
@Description nvarchar(2000)
)
AS
BEGIN
Declare @mTerm nvarchar(2000)
SET NOCOUNT ON;
set @mTerm ="SELECT Description FROM tbl_ElementalCostingRate WHERE Description = "+@Description
exec @mTerm
IF (@mTrerm IS NOT Null)
UPDATE tbl_ElementalCostingRate SET Category=@Category, [fileName]=@FileName,ContentType=@ContentType,mBtye =@Data ,[Description] =@Description
WHERE [Description] =@Description
ELSE
INSERT INTO tbl_ElementalCostingRate (Category,[fileName],ContentType,mBtye,[Description])
VALUES(@Category,@FileName,@ContentType,@data,@Description)
END
END

I am finding it difficult as i keep getting error any time i rewrite it and try so that is my challenge .

I will appreciate any help to resolve this issue and i will also want any like where i can get tutorial to write such Batch SQL statement like this.

Thank you

Hey AB ,

Try this

Create Procedure InsertDocument
(
@Category nvarchar(1000),
@FileName nvarchar(1500),
@ContentType Nvarchar(1500),
@Data varbinary(MAX),
@Description nvarchar(2000)
)
AS
BEGIN
Declare @mTrerm nvarchar(2000)
SET NOCOUNT ON;

Select @mTrerm = Description FROM tbl_ElementalCostingRate WHERE Description = @Description

-- set @mTerm ="SELECT Description FROM tbl_ElementalCostingRate WHERE Description = "+@Description
--exec @mTerm
IF (@mTrerm IS NOT Null)
Begin
UPDATE tbl_ElementalCostingRate
SET Category=@Category,
[fileName]=@FileName,
ContentType=@ContentType,
mBtye =@Data ,
[Description] =@Description
WHERE [Description] =@Description
End
ELSE
Begin
INSERT INTO tbl_ElementalCostingRate (Category,[fileName],ContentType,mBtye,[Description]) VALUES(@Category,@FileName,@ContentType,@data,@Description)
ENd
END

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Mangesh G9y ago
Accepted answer
Hey AB ,

Try this

Create Procedure InsertDocument
(
@Category nvarchar(1000),
@FileName nvarchar(1500),
@ContentType Nvarchar(1500),
@Data varbinary(MAX),
@Description nvarchar(2000)
)
AS
BEGIN
Declare @mTrerm nvarchar(2000)
SET NOCOUNT ON;

Select @mTrerm = Description FROM tbl_ElementalCostingRate WHERE Description = @Description

-- set @mTerm ="SELECT Description FROM tbl_ElementalCostingRate WHERE Description = "+@Description
--exec @mTerm
IF (@mTrerm IS NOT Null)
Begin
UPDATE tbl_ElementalCostingRate
SET Category=@Category,
[fileName]=@FileName,
ContentType=@ContentType,
mBtye =@Data ,
[Description] =@Description
WHERE [Description] =@Description
End
ELSE
Begin
INSERT INTO tbl_ElementalCostingRate (Category,[fileName],ContentType,mBtye,[Description]) VALUES(@Category,@FileName,@ContentType,@data,@Description)
ENd
END