strange result in stored procedure or PROBLEM

Question

strange result in stored procedure or PROBLEM

midan1

SSCertifiable

Points: 5100
More actions
December 23, 2007 at 4:58 pm

#184969

this is my problem in stored procedure
1) TABLE SilokE IS MY TABLE OF THE ALL EMPLOYEE
2) I need to see only the employee than in the table v_un
3) i see all the employee
4) problem in this line ( [new date] = @mydate2,[new_shift2] = )
i see 2 rows of result from fields [new date] + ,[new_shift]
how to see only 1 field for each field
new date new_shift new date empid name
---------------------------------------------------------------
2007-12-01 99 2007-12-02 99 2568947 aaa
2007-12-01 99 2007-12-02 99 2845209 bbbb
2007-12-01 99 2007-12-02 99 4807756 ccc
2007-12-01 99 2007-12-02 99 9819590 ddd
2007-12-01 99 2007-12-02 99 10055648 eee
2007-12-01 99 2007-12-02 99 10815413 ffff
2007-12-01 99 2007-12-02 99 11070042 gggg
2007-12-01 99 2007-12-02 99 11162047 hhh
-------------------------------------------------------------------------------
i need to see only one for each field
new date new_shift id name
---------------------------------------------------------------
2007-12-01 99 2568947 aaa
2007-12-01 99 2845209 bbbb
2007-12-01 99 4807756 ccc
2007-12-01 99 9819590 ddd
2007-12-02 99 10055648 eee
2007-12-02 99 10815413 ffff
2007-12-02 99 11070042 gggg
2007-12-02 99 11162047 hhh
Code Block
DECLARE @yeara [varchar](4)
DECLARE @month1 [varchar](2)
DECLARE @day1 [varchar](2)
DECLARE @day2 [varchar](2)
DECLARE @day3 [varchar](2)
DECLARE @mydate1 [datetime]
DECLARE @mydate2 [datetime]
set @yeara ='2007'
SET @month1 ='12'
Set @day1 ='1'
Set @day2 ='2'
set @mydate1 = CONVERT([datetime] ,@day1 + '/'+ @month1 + '/' + @yearA ,103)
set @mydate2 = CONVERT([datetime] ,@day2 + '/'+ @month1 + '/' + @yearA ,103)
select
[new date1]=@mydate1,[new_shift] =
CASE
WHEN (empid IN (SELECT empid FROM v_un WHERE (shift = 51 )))
THEN
1
WHEN
(empid IN (SELECT empid FROM v_un WHERE (shift = 11 )))
THEN 2
else
99
END,
[new date2]=@mydate2,[new_shift2]=
CASE
WHEN (empid IN (SELECT empid FROM v_un WHERE (shift = 11 )))
THEN 1
WHEN
(empid IN (SELECT empid FROM v_un WHERE (shift = 12 )))
THEN
2
else
88
END,
SilokE.empid, SilokE.Fname FROM SilokE
tnx for all the help i get here

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 1

C'mon, Midan... this one is easy. Figure it out. If you're returning a column you don't want, then remove it from the SELECT list!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

In addition, corelated subqueries in the select caluse can result in poor performance as, depending on the plan chosen, SQL may have to execute each subquery once per row of the outer query.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

midan1 SSCertifiable Points: 5100 More actions · Answer 3

Jeff Moden (12/23/2007)
C'mon, Midan... this one is easy. Figure it out. If you're returning a column you don't want, then remove it from the SELECT list!

how

i must to give next day new date it is condition for evry day

so how to do it

and the second problem is i need to see only the employee that existing in the "empid FROM v_un"

now i see all

TNX

midan1 SSCertifiable Points: 5100 More actions · Answer 4

midan1

SSCertifiable

Points: 5100

December 25, 2007 at 2:57 pm

#762623

yes yes

you right

TNX