quote:

---

Originally posted by majidbhutta


Hi Dears Below is the Sp that i want to use but there are many Syntax erros plz verify and correct them Thnx

create procedure CheckStatus

@EmpID int
as
-- i need some variables so i declared them
declare @ShiftId int
declare @DayTime smalldatetime
declare @ShiftStartT smalldatetime
declare @ShiftEndT smalldatetime
declare @ShiftInOutStatus int
declare @ShiftValidity bit
declare @RTValue int


-- Now i m assigning the values to the declared variable by selecting some value from tables
-- from this point i m having syntax erroes i dont know what are they all atables and all fields are ok verified

set @ShiftId=SELECT S_Id FROM Emp_Info WHERE Emp_Info.Emp_Id=@EmpIDnot the syntax error but see to it that it just return only one record..
set @DayTime=SELECT GETDATE()
set @ShiftStartT=SELECT S_ST FROM Emp_Shift WHERE Emp_Shift.S_Id=@ShiftIdnot the syntax error but see to it that it just return only one record..
set @ShiftEndT=SELECT S_ET FROM Emp_Shift WHERE Emp_Shift.S_Id=@ShiftIdnot the syntax error but see to it that it just return only one record..
set @InOutStatus=SELECT IOStatus FROM Emp_Status WHERE Emp_Status.Emp_Id=@EmpIdnot the syntax error but see to it that it just return only one record..
-- here i m using a nested case for some checks to insert values into tables
CASE -- Main

Case	WHEN @InOutStatus=0 OR @InOutStatus=-1
-- Calling  udf_IsShiftValid(datetime,datetime,datetime) a function that ia also ok and returns two possible value 0 or 1 
		THEN @ShiftValidty=udf_IsShiftValid(@ShiftStartT,@ShiftEndT,@DayTime)
		CASE --nested case starts here
			WHEN @ShiftValidity=1-- Insertin value into Emp_Status table and assihning value to @RTValue
			THEN (INSERT INTO Emp_Status(IOStatus) VALUES(@ShiftValidity) WHERE Emp_Status.Emp_Id=@EmpID
			SET @RTValue=1)
			WHEN @ShiftValidity=0  THEN SET @RTValue=-1
		END-- Nested case End Here
		WHEN @InOutStatus=1
		THEN
		INSERT INTO Emp_Status(IOStatus) VALUES(0) WHERE Emp_Status.Emp_Id=@EmpID
		SET @RTValue=0
	END

Instead of the above code use the series of if and else if .. since case can only work in the select clause..

somthing like this

if @InOutStatus=0 OR @InOutStatus=-1
-- What Ever you want to do ..
Else if ..
-- do somthing ..

.......
...

Else

-- Default..

RETURN @RTValue

GO

---

set @ShiftId=(SELECT S_Id FROM Emp_Info WHERE Emp_Info.Emp_Id=@EmpID)
or 
SELECT ShiftId = S_Id FROM Emp_Info WHERE Emp_Info.Emp_Id=@EmpID