how to insert guid from one table to another?

Question

Hi, I have three tables:
CREATE TABLE [dbo].[tx_main] (
[id] INT IDENTITY (1, 1) NOT NULL,
[uni] UNIQUEIDENTIFIER DEFAULT (newid()) NOT NULL,
[calendar] DATETIME NULL,
[inout] CHAR (1) NULL,
[cust] VARCHAR (8) NULL
);
CREATE TABLE [dbo].[tx_transport] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[MainID] INT NOT NULL,
[tr_name] NVARCHAR (50) NOT NULL,
[tt_weight] INT NULL,
[tr_type] INT NOT NULL,
[tt_vin] NVARCHAR (50) NOT NULL,
[calendar] DATETIME NOT NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)
);
CREATE TABLE [dbo].[fotoblock] (
[guid] UNIQUEIDENTIFIER NOT NULL,
[img_name] NVARCHAR (50) NULL,
[photo] VARBINARY (MAX) NULL,
[id] INT IDENTITY (1, 1) NOT NULL,
[calendar] DATETIME NULL
);
I have Blazor server app in which it is possible to insert new records to these tables and retreive them. So to insert new record I wrote two stored procedures:
CREATE PROCEDURE [dbo].[insmain]
@cake ndar datetime,
@inout char,
@cust varchar(10),
@tr_name nvarchar(50),
@tt_weight int,
@tr_type int,
@dsadassad int,
@tt_vin nvarchar(50),

    @guid uniqueidentifier output  
	  
	  
	  
AS  
begin  
set nocount on  
declare @table table(ItemID uniqueidentifier)  
insert tx_main (calendar,inout,cust)  
output inserted.uni into @table(ItemID)  
  
values(@calendar,@inout,@cust)  
  
  
set @MainID=SCOPE_IDENTITY()  
  
insert tx_transport(MainID,calendar,tr_name,tt_vin,tt_weight,tr_type)values(@MainID,  
@calendar,@tr_name,@tt_vin,@tt_weight,@tr_type)  
select @guid=ItemID from @table  
  
end  
CREATE PROCEDURE [dbo].[insphoto]  
	@img_name nvarchar(50),  
	@photo varbinary(max),  
	@calendar datetime  
	  
AS  
begin  
declare @guid uniqueidentifier  
exec insmain @guid output  
insert fotoblock (img_name,photo,calendar,[guid])values(@img_name,@photo,@calendar,@guid)  
end  

So tx_main table has column 'id' related with tx_transport table by 'MainID' column and it works normally. I want to relate tx_main table and fotoblock table by uniqueidentifier, which is generated in tx_main 'uni' column and must be inserted by stored procedures to fotoblock 'guid' column, but this is not work.When I make a new record in my app, I see " Error: System.Data.SqlClient.SqlException (0x80131904): Procedure or function 'insmain' expects parameter '@George Wang ', which was not supplied. " So, I think it's a problem with stored procedures. How to correctly write stored procedures to bind tx_main and fotoblock by guid?

Answer 1

Hi @Decompressor ,

Per my knowledge, you could modify below part since the procedure [dbo].[insmain] has 8 paramters which did not provided in below part.

 exec insmain @guid output  

I also tried from my side with some modifications and it was working.

You could refer below and check whether it is helpful.

CREATE OR ALTER PROCEDURE [dbo].[insmain]  
@calendar datetime,  
@inout char,  
@cust varchar(10),  
@tr_name nvarchar(50),  
@tt_weight int,  
@tr_type int,  
@MainID int,  
@tt_vin nvarchar(50),  
@guid uniqueidentifier output         
 AS  
 begin  
 set nocount on  
 declare @table table(ItemID uniqueidentifier)  
 insert tx_main (calendar,inout,cust)  
 output inserted.uni into @table(ItemID)   
 values(@calendar,@inout,@cust)  
      
 set @MainID=SCOPE_IDENTITY()  
      
 insert tx_transport(MainID,calendar,tr_name,tt_vin,tt_weight,tr_type)values(@MainID,  
 @calendar,@tr_name,@tt_vin,@tt_weight,@tr_type)  
 select @guid=ItemID from @table  
    
 end  

CREATE OR ALTER PROCEDURE [dbo].[insphoto]  
     @img_name nvarchar(50),  
     @photo varbinary(max),  
     @calendar datetime,  
  @calendar2 datetime,  
 @inout char,  
 @cust varchar(10),  
 @tr_name nvarchar(50),  
 @tt_weight int,  
 @tr_type int,  
 @MainID int,  
 @tt_vin nvarchar(50)     
 AS  
 begin  
 declare @guid uniqueidentifier  
 exec insmain @calendar2,@inout,@cust,@tr_name,@tt_weight,@tr_type,@MainID,@tt_vin,@guid output  
 insert fotoblock (img_name,photo,calendar,[guid])values(@img_name,@photo,@calendar,@guid)  
 end  

Firstly, I inserted some sample data into [tx_main] table.

INSERT INTO [dbo].[tx_main] ([calendar],[inout],[cust]) VALUES  
('2020-09-01 09:00:00','1','aa'),  
('2020-10-07 09:00:00','2','bb'),  
('2020-10-08 09:00:00','3','cc')  

Then I would execute the procedure [insphoto] with all paramters provided.

 exec [insphoto] 'ddd',13234554,'2020-10-02 08:00:00','2020-10-03 09:00:00','1','aaa','BB',1,1,1,'ABC'  

Finally, I checked the result of all tables and found that the latest guid of tx_main table was sucessfully inserted into the fotoblock table.

select * from [tx_main]  
select * from [tx_transport]  
select * from [fotoblock]  

Output:

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

I am not sure that I understand what you are trying to do. insmain takes in total nine parameters, all mandatory. In the procedure insphoto, you call it with a single parameter. And on top of that, you are passing a uniqueidentifier to the first parameter - which is a datetime. Is your idea that insmain would somehow automatically guess which guid value you want back?

With your current procedure, you would have to get the value of the @George Wang output parameter from insmain, and then insphoto would have to accept it as an input parameter. There is no way at the time you are in insphoto to say "give my the most recently inserted guid in insmain". (And who says that you want the most recently added? Maybe you only want the second-latest?)

Is there any particular reason why you have both IDENTITY columns and guids? Looks to me that there is one too many of them.

Answer 3

Reason is tx_main and fotoblock are in the different databases. And, yes, I want the most recently inserted guid in ins main, but I don't know how to write the procedure correctly, that is why I ask for help

Answer 4

That brings us to the next question: why are they in different databases? There may be very well a good reason for it, but just don't have two databases for the sake of it.

The best solution is that the client passes the guid to the insphoto stored procedure. There is not really a way to get the most recently inserted guid. Sure you can to

SELECT TOP 1 uni
FROM   tx_main
ORDER BY id DESC

But there are several problems here. Maybe at some point you use DBCC CHECKIDENT to start the ids on a lower number. Or maybe another process have inserted a row into tx_main after the one that is matching your photo.

So pass the guid from the client. It's the only reasonable way to do it.