0

I have a question about this select statement. Im new to t-sql and creating stored procedures. Ive only used select statements not selecting variables , but columns. Help me understand the select statement in this? Why select a variable equal to the column name?

declare @CompanyName         varchar(100),
            @DFIFileId           int,
            @DFICompanyID        int,
            @DFIMappingID        int,
            @DFIWorkbookNoticeID int,
            @DFIWorkbookClaimID  int,
            @DFIWorkSheetID      int
    
    set @CompanyName = 'Cigna'
    
       --  Creates a new DFI Company record
        insert into DfiCompany (CompanyName, IsCreateAuditCharges, IsAutoAdj, AutoAdjMaxAmt, SavingsPct, SavingsMaxAmt, IsActive) 
                        values (@CompanyName, 0, 0, 0, 0.000, 0, 1)
        select @DFICompanyID = DfiCompanyID from DfiCompany where CompanyName = @CompanyName
jarlh
  • 42,561
  • 8
  • 45
  • 63
Austin
  • 1
  • 3
  • 1
    If `DfiCompanyID` is an autogenerated primary key, this is a _really bad way_ of reading that value after inserting a new record – Jamiec Oct 15 '21 at 14:42
  • Why is it bad and whats a better way?? – Austin Oct 15 '21 at 14:51
  • 1
    Does this answer your question? [SQL Server - Return value after INSERT](https://stackoverflow.com/questions/7917695/sql-server-return-value-after-insert) – Jamiec Oct 15 '21 at 15:05
  • Thanks for showing the alternate ways to do it. But it doesnt necessarily tell me why this is bad.. – Austin Oct 15 '21 at 15:20
  • 2
    What happens if you have 2 records in your database with the same `CompanyName`? Will you get back the id of the first one (hint hint) or the one you just inserted? – Jamiec Oct 15 '21 at 15:24
  • There is no requirement that a variable name matches a column name, you can name them anything. It's just clearer *to the reader* this way what belongs together – Hans Kesting Oct 15 '21 at 20:36
  • Aside: You can get a single value from a row using the slightly clumsy `set @FooValue = ( select Foo from TheTable where SomeId = @SomeUniqueIdValue );`. The `select` syntax also allows for getting multiple column values from the same row, e.g, `select @FooValue = Foo, @FooPrice = Price, @FooWeight = Weight from TheTable where SomeId = @SomeUniqueIdValue;`. – HABO Oct 17 '21 at 02:23

0 Answers0