August 21, 2004 at 6:04 pm
need help with my PROCEDURE
how can i do this ???
USE [sidor_mdb]
GO
CREATE PROCEDURE [update_main_yoel_1]
(@na_1 [int],
@qkenlo [int],
@s_qkenlo [int],
@t_qkenlo [int],
@u_qkenlo [int])
--------------------------------------the problem
SET @qkenlo = SELECT na, mafname
FROM dbo.main_yoel WHERE (NOT (mafname IN (0)))
"then i need that
@qkenlo get value 1
Else 0
"
0= View empty
1=View not empty
and olso to
@s_qkenlo , @t_qkenlo , @u_qkenlo
-----------------------------------end problem
AS UPDATE [sidor_mdb].[dbo].[main_yoel]
SET [qkenlo] = @qkenlo,
[s_qkenlo] = @s_qkenlo,
[t_qkenlo] = @t_qkenlo,
[u_qkenlo] = @u_qkenlo
WHERE
( [na] = @na_1)
-----------
thnks ilan
August 22, 2004 at 4:07 pm
Ilan,
You can use Case on the right side of the "=" sign in SET statments... something like this (could understand exactly what you were trying to do in your example but this should help.
WHEN columnb = 0
--Jeff Moden
Change is inevitable... Change for the better is not.
August 22, 2004 at 4:45 pm
but how i use CASE on update thet dependant ON select
-----------
SET columna = CASE
--1
when @fld1=SELECT na, mafname1
FROM dbo.main_yoel WHERE (NOT (mafname1 IN (0)))
--2
when @fld2=SELECT na, mafname2
FROM dbo.main_yoel WHERE (NOT (mafname2 IN (0)))
--3
when @fld3=SELECT na, mafname3
FROM dbo.main_yoel WHERE (NOT (mafname3 IN (0)))
------if the table is empty the i get the value =0
--------else the value=1
-----------------------------------------------
thnks ilan
August 22, 2004 at 9:32 pm
Ilan,
Not trying to be difficult here but I guess I don't understand the problem... please describe what you are trying to do instead of trying to write code for it... thanks.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 22, 2004 at 10:27 pm
Ilan, it looks like you're trying to do some update processing that depends on the results of several select queries (but as Jeff said, it's not 100% clear from your pseudo-code). But is it something like this:
select @fld1=mafname1
FROM dbo.main_yoel WHERE (NOT (mafname1 IN (0)))
select @fld2=mafname2
FROM dbo.main_yoel WHERE (NOT (mafname2 IN (0)))
select @fld3=mafname3
FROM dbo.main_yoel WHERE (NOT (mafname3 IN (0)))
set @update_field = (--some logical processing here based on your local variables)
--Perform the update using data contained in @update_field
update ...
Regards
Phil
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 22, 2004 at 11:38 pm
how can i update with this
and put all with the update
---------------------------------
SELECT
--1<<<<<<<<<<<<<<<<<<<<<<
[fld1] =
CASE
WHEN
( select
FROM dbo.main_yoel WHERE (NOT (mafname1 IN (0)))
-if the table is empty THEN @fld1=0
ELSE -if the table is empty THEN @fld1=1
END ,
--2<<<<<<<<<<<<<<<<<<<<<<
[fld2] =
CASE
WHEN
( select
FROM dbo.main_yoel WHERE (NOT (mafname2 IN (0)))
-if the table is empty THEN @fld2=0
ELSE -if the table is empty THEN @fld2=1
END ,
--3<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
[fld3] =
CASE
WHEN
( select
FROM dbo.main_yoel WHERE (NOT (mafname3 IN (0)))
-if the table is empty THEN @fld3=0
ELSE -if the table is empty THEN @fld3=1
END ,
----------------------------
thnks ilan
August 23, 2004 at 12:17 am
I'm afraid I am now in the same boat as Jeff. I do not understand what you are trying to achieve. Can you explain the problem without trying to write the code for it? - that is only confusing things, in my opinion.
Regards
Phil
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 23, 2004 at 7:06 am
Sounds to me you just wants to validate the existance of data however I am not sure you want to do
select * FROM dbo.main_yoel WHERE (NOT (mafname1 IN (0))
for sure as unless all your records will be mafname1 = 0 then you will always get an output of 0 to your field.
Instead I assume you really want to know that no records are mafname1 = 0 which would be
select * FROM dbo.main_yoel WHERE mafname1 = 0
I need clarification on this.
Also may be able to do a join otherwise just need to understand the relationship of this table to the other.
But this will probably help a bit with where you should be going
--1<<<<<<<<<<<<<<<<<<<<<<
[fld1] =
CASE
WHEN
EXISTS (select 1
FROM dbo.main_yoel WHERE (NOT (mafname1 IN (0)))
--if the table is empty
THEN 0
ELSE --if the table is empty
1
END ,
--2<<<<<<<<<<<<<<<<<<<<<<
[fld2] =
CASE
WHEN
EXISTS ( select 1
FROM dbo.main_yoel WHERE (NOT (mafname2 IN (0)))
--if the table is empty
THEN 0
ELSE --if the table is empty
1
END ,
--3<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
[fld3] =
CASE
WHEN
EXISTS ( select 1
FROM dbo.main_yoel WHERE (NOT (mafname3 IN (0)))
--if the table is empty
THEN 0
ELSE --if the table is empty
1
END
August 23, 2004 at 7:31 am
1. When you say "table is empty", I think you mean that the WHERE condition returns 0 rows. If so, then this is an easy way to do that logic:
SELECT @found_count = COUNT(*) FROM table WHERE condition;
2. In your example:
SELECT @qkenlo_count = COUNT(*) FROM dbo.main_yoel
WHERE NOT (mafname IN (0))
2A. Note: Your WHERE condition as written confuses me a little. If mafname is a char or varchar field, and if you are looking for empty entries (that is, empty strings or NULL values), you might try this:
WHERE TRIM(ISNULL(mafname,""))="" -- will count empty/NULL entries
WHERE NOT TRIM(ISNULL(mafname,""))="" -- will count not-empty entries
3. Note that the @found_count can be a number larger than one if the where condition finds multiple rows. To convert it to be either 1 or 0, you can do this:
SELECT @found = CASE
WHEN @found_count = 0 THEN 0
ELSE 1
END
4. Putting it all together:
CREATE PROCEDURE [update_main_yoel_1]
(@na_1 [int],
@qkenlo [int],
@s_qkenlo [int],
@t_qkenlo [int],
@u_qkenlo [int])
AS
DECLARE @qkenlo_count [int]
DECLARE @s_qkenlo_count [int]
DECLARE @t_qkenlo_count [int]
DECLARE @u_qkenlo_count [int]
SELECT @qkenlo_count = COUNT(*) FROM dbo.main_yoel
WHERE [[where-condition]]
SELECT @qkenlo = CASE
WHEN @qkenlo_count = 0 THEN 0
ELSE 1
END
-- ----------------------------------------
-- Repeat the above logic for s_qkenlo, t_qkenlo, u_qkenlo
-- ----------------------------------------
UPDATE [sidor_mdb].[dbo].[main_yoel]
SET [qkenlo] = @qkenlo,
[s_qkenlo] = @s_qkenlo,
[t_qkenlo] = @t_qkenlo,
[u_qkenlo] = @u_qkenlo
WHERE
( [na] = @na_1)
Bob Monahon
August 23, 2004 at 9:57 pm
As I understand it, you want to perform the following for records where field na = parameter @na_1.
1. If the current value of the field is 0 or Null, set it to 0.
2. If the current value is < 0 or > 0 and Not Null, set it to 1.
If the above is true, the following code will work.
CREATE PROCEDURE [update_main_yoel_1]
@na_1 [int]
AS
Update dbo.main_yoel_1
set qkenlo = case When isnull(abs(qkenlo),0) > 0 Then 1 Else 0 End,
s_qkenlo = case When isnull(abs(s_qkenlo),0) > 0 Then 1 Else 0 End,
t_qkenlo = case When isnull(abs(t_qkenlo),0) > 0 Then 1 Else 0 End,
u_qkenlo = case When isnull(abs(u_qkenlo),0) > 0 Then 1 Else 0 End
From dbo.main_yoel_1
Where na = @na_1
P.S. You may want to consider defaulting the fields to 0 and changing the type to bit instead of int.
Hope this helps
mike
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply