How to fill a varbinary(MAX) column with a default image for all the records in SQL Server 2005 Express

Question

I want to fill the varbinary(MAX) column of a SQL Server database table when ever a new record is created with a default picture. How do I do this using a trigger or in that case by any other means?

This is the T-SQL code I have tried:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[Trg_DoctorDemographic_DefaultImage]
ON [ECGManagementSystem].[dbo].[DoctorDemographic]
 AFTER INSERT  
NOT FOR REPLICATION
AS
BEGIN
  INSERT INTO[ECGManagementSystem].[dbo].[DoctorDemographic]
    (DPhoto)
    SELECT  * FROM OPENROWSET(Bulk N'D:\Programs\MSVC\Exercises\BEMS\BEMS\res\Doctor.bmp',SINGLE_BLOB)AS BLOB
END;

Answer 1

I would be inclined to use a separate table to store the picture, and wrap this in a scalar UDF, which is defined as the default constraint.

If only to avoid unnecessary OPENROWSET calls in triggers...

Answer 2

No, don't do it this way.

If you have 1,000 rows with this default you will have 1,000 copies of this image? The size of your database will grow quickly. If you want to change the default you have to update 1,000 images. Not good.

Store 1 copy in 1 place - maybe a table called DefaultDPhoto. Keep the image column null in your DoctorDemographic table when it is the default, then when you go to retrieve the image have the logic that if this column is null, go pull the single copy.

EDIT:

Ok, first I would make a stored proc like:

create proc getDPhoto(@ID int)
as
begin
set nocount on

if exists (select 1 from DoctorDemographic where id = @ID and DPhoto is not null)
     select DPhoto from DoctorDemographic where id = @ID
else 
    select DPhoto from DefaultDPhoto 

end

Then from this example here as a starting point I would change step 1 under "Retrieving Image" to the following:

SqlCommand cmdSelect = new SqlCommand("getDPhoto");

cmdSelect.CommandType = CommandType.StoredProcedure;

Answer 3

Could use something like this?

SELECT [DColumnName1]
     , [DColumnName2]
     , CASE WHEN [DPhoto] IS NULL THEN (SELECT  * FROM OPENROWSET(Bulk N'D:\Programs\MSVC\Exercises\BEMS\BEMS\res\Doctor.bmp',SINGLE_BLOB)AS BLOB) ELSE [DPhoto] END [DPhoto]
  FROM [ECGManagementSystem].[dbo].[DoctorDemographic]
 WHERE Something = 'Matching';

Edit: Well if you really want copies of the image in the table to fix [OMG Ponies] insert below try this then.

SET @Image = (SELECT BulkColumn FROM OPENROWSET(Bulk N'D:\Programs\MSVC\Exercises\BEMS\BEMS\res\Doctor.bmp',SINGLE_BLOB)AS BLOB);

Instead of:

SELECT @image = column FROM OPENROWSET(Bulk N'D:\Programs\MSVC\Exercises\BEMS\BEMS\res\Doctor.bmp', SINGLE_BLOB) AS BLOB

Doing this makes it harder to change the default picture later on though but it's your DB :)