George2: Created page with " USE [test] GO / Object: StoredProcedure [dbo].[sptest2] Script Date: 09/03/2017 09:31:48 / SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON G..."

2017-03-09T10:04:54Z

Created page with "<source lang="sql"> USE [test] GO ‎***** Object: StoredProcedure [dbo].[sptest2] Script Date: 09/03/2017 09:31:48 *****: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON G..."

New page

<source lang="sql">

USE [test]
GO
/****** Object: StoredProcedure [dbo].[sptest2] Script Date: 09/03/2017 09:31:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sptest2] as

select Id
into #ControlTable
from dbo.ImageTable

declare @TableID varchar (30);
declare @photoid varchar(10);
declare @photostr varchar(40);
declare @result int;

-- set @photoid=(select Name from ImageTable where id='1');
-- set @photostr= CONCAT('C:\IMSpics14_15\',@photoid,'.jpg');
-- print @photostr

while exists (select * from #ControlTable)
begin

select @TableID = (select top 1 Id
from #ControlTable
order by Id asc
)

-- Do something with your TableID
--- print @TableID;
set @photostr= CONCAT('C:\IMSpics14_15\',@Tableid,'.jpg');
print @photostr;
DECLARE @PATH varchar(20);

EXEC master.dbo.xp_fileexist @photostr, @result OUTPUT

if(@result=1)
BEGIN
BEGIN TRY
update ImageTable set photo=
(SELECT BulkColumn
FROM Openrowset( Bulk 'C:\IMSpics14_15\33105810.jpg', Single_Blob) as image)
where id=@TableID;
END TRY
BEGIN CATCH
END CATCH
END
else
print 'notfound';
delete #ControlTable
where Id = @TableID

end

drop Table #ControlTable

</source>

Importing photographs - Revision history

George2: Created page with " USE [test] GO /****** Object: StoredProcedure [dbo].[sptest2] Script Date: 09/03/2017 09:31:48 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON G..."

George2: Created page with " USE [test] GO / Object: StoredProcedure [dbo].[sptest2] Script Date: 09/03/2017 09:31:48 / SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON G..."