I have a site that is for tracking information about our assets. The assets table holds all the info for the asset but the location is stored as a code (200, 100, 450 ect..) and the site pulls the info from another table that stores the code and the name associated with it so that the page displays a name rather than number. The location code table has three columns: ID, Code, and Name. The current location column in the assets table stores the code that corresponds to the location code table so that the data stored in the assets table can be kept to a minimum. What I need to do is be able to check to make sure that the location code table entry is not modified if it is use by an asset. The site passes the ID of the entry to be changed as well as the new code and name that will be used. (I will post my sproc that I currently use) I can check to make sure the new code and name are not in use but I am not sure how to check to make sure the existing entry isn't being used by the asset table. This sproc is used to both add a new location entry as well as modify an existing entry.
USE [dnndev.me] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[AssetManager_AddLocation] @LocationCode int, @LocationName nvarchar(50), @LocationID int AS BEGIN DECLARE @return INT = 0; IF NOT exists (select @LocationID from dbo.AssetManager_LocationCode where LocationID = @LocationID) begin IF NOT EXISTS (select @LocationCode from dbo.AssetManager_LocationCode where LocationCode = @LocationCode) begin IF NOT EXISTS (select @LocationName from dbo.AssetManager_LocationCode where LocationName = @LocationName) begin INSERT INTO dbo.[AssetManager_LocationCode] ([LocationCode], [LocationName]) VALUES (@LocationCode, @LocationName) select @return = 0; end ELSE begin SELECT @return = 1; end end ELSE SELECT @return = 2; end else if not exists (select CurrentLocationID from dbo.AssetManager_Assets where CurrentLocationID = @LocationCode) begin update dbo.[AssetManager_LocationCode] set LocationCode = @LocationCode, LocationName = @LocationName where LocationID = @LocationID select @return = 0; end else SELECT @return; END
if not exists (select CurrentLocationID from dbo.AssetManager_Assets where CurrentLocationID = @LocationCode)
is where I am having my trouble because I want to make sure the entry in the locationCode table associated with the LocationID is not in use in the Assets table but the LocationID isn't used.
(I think that if I keep trying to explain it I will end up rambling and making even less sense, so please all I ask is that you ask constructive questions not mock me for not making perfect sense)
P.S. not sure if it matters but I am using SQL express 2012
Edited by hispaladin, 26 February 2014 - 01:39 PM.