Jump to content


 


Register a free account to unlock additional features at BleepingComputer.com
Welcome to BleepingComputer, a free community where people like yourself come together to discuss and learn how to use their computers. Using the site is easy and fun. As a guest, you can browse and view the various discussions in the forums, but can not create a new topic or reply to an existing one unless you are logged in. Other benefits of registering an account are subscribing to topics and forums, creating a blog, and having no ads shown anywhere on the site.


Click here to Register a free account now! or read our Welcome Guide to learn how to use this site.

Photo

a simple SQL question (I think)


  • Please log in to reply
1 reply to this topic

#1 hispaladin

hispaladin

  • Members
  • 245 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Middle of a corn field
  • Local time:10:45 AM

Posted 26 February 2014 - 01:37 PM

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

the line 

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.


BC AdBot (Login to Remove)

 


#2 hispaladin

hispaladin
  • Topic Starter

  • Members
  • 245 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Middle of a corn field
  • Local time:10:45 AM

Posted 28 February 2014 - 09:59 AM

Got the problem fixed for anyone else who has a problem like this.  I used this line of code

if not exists (select top 1 a.AssetID from dbo.AssetManager_Assets a inner join dbo.AssetManager_LocationCode l on a.CurrentLocationID = l.LocationCode where l.LocationID = @LocationID)





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users