January 13, 2009 at 2:00 am
I am quite new to SQL and programming languages. What I am trying to solve with this sproc is to insert the factor for conversion between different measurements. My problem is that almost every if-statement gets executed except the last one. If one unit is missing I want only statement 2 to be executed. What is wrong with my script?
create procedure test
@unit1 varchar (50),
@unit2 varchar (50),
@factor decimal (10,2),
@ingredient varchar(50)
AS
BEGIN
DECLARE @fromunit int, @tounit int
SELECT @fromunit = (SELECT id FROM Unit_To_Unit WHERE name = @unit1)
SELECT @tounit = (SELECT id FROM Unit_To_Unit WHERE name = @unit2)
IF EXISTS (SELECT * FROM Unit_To_Unit WHERE from_unit = @fromunit AND to_unit = @tounit)
begin
PRINT ('This conversion already exists in the database')
end
IF NOT EXISTS (SELECT * FROM Unit WHERE name = @unit1)
begin
PRINT ('This unit doesn't exists in the database')
end
IF NOT EXISTS (SELECT * FROM Unit WHERE name = @unit2)
begin
PRINT ('This unit doesn't exists in the database')
end
DECLARE @ingredientid INTEGER
SET @ingredientid = (SELECT id FROM Ingredient WHERE name = @ingredient)
IF NOT (@ingredient = '' AND @ingredientid IS NULL)
BEGIN
PRINT 'This ingredient doesnt exist in the database'
END
ELSE
BEGIN
INSERT INTO Unit_To_Unit (from_unit, to_unit, factor, ingredient_id)
VALUES(@fromunit, @tounit, @factor, @ingredientid)
PRINT 'Succesful operation'
END
END
January 13, 2009 at 3:08 am
m_grasdal (1/13/2009)
IF NOT (@ingredient = '' AND @ingredientid IS NULL)
Replace this with
IF NOT (ISNULL(@ingredient, '') = '')
Regards,
Nitin
January 13, 2009 at 4:17 am
Just something to think about really since I don't know what your table design looks like for the Ingredient table and all the ways possible for you to populate values into that table, e.g. EXCEL file import, flat file import basically some kind of bulk load operation or whether this procedure is the only way to get data into the table. If the latter is the case then what I'm saying next is not such a worry. But if not...
Assuming there may be some data in the Ingredient table already, if this procedure had an empty string ('') passed as a parameter value for @ingredient and the Ingredient table contained a row(s) with a "name" field containing such a value ('') then your stored procedure would mis-report the situation since a row(s) would be found with a "name" of empty string ('') but this would be reported as 'doesn't exist in the database'.
I guess what I am really saying here, don't forget about check constraints on your table to specifically EXCLUDE empty string in the "name" column if that is really what you want and think about using a NULL value as a means of detecting whether the row has indeed got any data in the "name" column (of the Ingredient table). It may be that letting in unwanted empty string ('') data at the point of any record insertion causes you to have to check for this value all over the place in future coding.
Just something to think about.
Regards,
Simon
January 15, 2009 at 3:50 am
Thank you for your comments.
My problem with this procedure is that if one unit doesn't exist in the database I want the script to return the Message
" The unit doesn't exist in the database" and then not insert the values into the table. Now it is trying to insert the vaules into the table even when that comes in a else clause. How can I do that in the script?
January 15, 2009 at 5:07 am
Check for nulls in the necessary variables before trying the insert. You did that previously in your stored procedure, but you don't have the necessary control code to prevent the insert if the data is missing.
Remove the insert from the 'if -else' that you had and change as follows...
IF (@fromunit is null) or (@tounit is null) or (@ingredientid is null)
BEGIN
PRINT 'Missing data'
ELSE
INSERT INTO Unit_To_Unit (from_unit, to_unit, factor, ingredient_id)
VALUES(@fromunit, @tounit, @factor, @ingredientid)
PRINT 'Succesful operation'
If it was easy, everybody would be doing it!;)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply