Ok here is my issue. I have a database (very small) that stores asset data in one table and stores location codes in another, make codes in one and category codes in one. I have it set this way so that the asset table only has to store the integer for the location, make, and category rather than the full text names. The stored procedure I am trying to write is supposed to check to see if the category in the category table is in use in the asset table and if it is not, delete it and return (select) a value of 0, and if the value does exist in the asset table select a value of 1. The sproc more or less does work except that it will not select the correct value so my website code cannot work correctly because it is not getting handed the right returned value. here is my SQL code
ALTER PROCEDURE [dbo].[AssetManager_AddCategory] @Category nvarchar(50) AS if not exists (select @Category from dbo.CategoryCode where Category = @Category) begin INSERT INTO dbo.[CategoryCode] ([Category]) VALUES (@Category) select 0 end else begin select 1 end
The code will not delete an entry that is in use and will delete an entry that is not in use it just will not return the correct value.
Any ideas what I am doing wrong here?