Click here to Skip to main content
14,739,187 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I would like to insert an image to SQL Server table contains other columns and imagepath columns ( varbinary(MAX) ) using stored procedure , how can I do that?

Thanks

What I have tried:

Create procedure insert_test
AS
BEGIN
INSERT test(
   Id,
logo

)
     VALUES (
         @Id,
(SELECT  BulkColumn
    FROM Openrowset(Bulk '''+ @imagepath +''', Single_Blob) as img),
END
Posted
Updated 4-Nov-20 22:51pm
v2

   
Comments
abdou_31 5-Nov-20 4:18am
   
I see this , but I need to use stored procedure on table contains other columns not a table for the image
That's a bad idea.
If we ignore that you don't define either of the stored procedure parameters you are using (and that means it won't work at all anyway), the problem is that that code relies on your SQL Server instance having both the actual image files stored on it, and that the SQL Server process has access to the folders containing them.
In production, neither of those is likely to be true: the SQL Server instance is likely to be a separate machine (or farm of machines) to which users have no direct access for security reasons, and for similar reasons, the server process will be restricted to only those folders it needs to do it's job, such as the DB storage directory.

A much better idea is to pass the image data as a parameter instead of the path from your presentation software when you call the stored procedure. That's easy to do in most languages, this shows how to do it in C#: Why do I get a "Parameter is not valid." exception when I read an image from my database?[^]
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900