SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


strange result in stored procedure or PROBLEM


strange result in stored procedure or PROBLEM

Author
Message
midan1
midan1
SSC-Addicted
SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)

Group: General Forum Members
Points: 408 Visits: 271
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



Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86069 Visits: 41096
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87293 Visits: 45272
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
midan1
SSC-Addicted
SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)

Group: General Forum Members
Points: 408 Visits: 271
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
midan1
SSC-Addicted
SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)

Group: General Forum Members
Points: 408 Visits: 271
yes yes
you right
TNX



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search