trjhtr

This is a simple script which inserts data into a couple of tables. I'm very new to SQL so any feeback, of any kind, would be much appreciated!

DECLARE 
 @Model_ID INT
 , @ModelVersion VARCHAR(10)
 , @ExistsInDM INT
 , @IsActive BIT

SET @ModelVersion = '2.46.7' -- new version number
SET @IsActive = '1'

SELECT @Model_ID = Model_ID
FROM [sch_AM].[tblDMModelVersion] WITH (NOLOCK)
WHERE [ModelVersion] = @ModelVersion 

IF @Model_ID IS NULL 
BEGIN
 INSERT INTO [sch_AM].[tblDMModelVersion]
 ( [ModelVersion]
 ,[IsActive]
 )
 VALUES ( @ModelVersion
 ,@IsActive
 )

 EXECUTE sch_AM.usp_GetActiveModel_ID @ModelVersion, @Model_ID OUTPUT

 SELECT @ExistsInDM = COUNT(Model_ID) 
 FROM [sch_AM].[tblDMModelToTable] WITH (NOLOCK)
 WHERE Model_ID = @Model_ID

 IF @ExistsInDM = 0
 BEGIN
 INSERT INTO [sch_AM].[tblDMModelToTable]
 SELECT
 @Model_ID
 ,[Table_ID]
 ,[TableVersion]
 ,[UserViewable]
 ,[TableRequiredByModel]
 FROM [sch_AM].[tblDMModelToTable] WITH (NOLOCK)
 WHERE Model_ID = 1
 END
END

SELECT * FROM [sch_AM].[tblDMModelVersion] WHERE Model_ID = @Model_ID
SELECT * FROM [sch_AM].[tblDMModelToTable] WHERE Model_ID = @Model_ID

Just 1 will work here. It will do a cast to 1

SET @IsActive = 1 

You can also assign in the declare

DECLARE 
 @Model_ID INT
 , @ModelVersion VARCHAR(10) = 'lasdf'
 , @ExistsInDM INT
 , @IsActive BIT = 1


SELECT @Model_ID = Model_ID
FROM [sch_AM].[tblDMModelVersion] WITH (NOLOCK)
WHERE [ModelVersion] = @ModelVersion 

Above will get the last value read.

with (nolock)

Is typically not advised.