July 29, 2012 at 8:00 am
Can someone please help me with this stored procedure.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE GetBNCurrentLocation
@bn varchar(10)
AS
DECLARE @mkt_out datetime
DECLARE @eng_out datetime
DECLARE @dft_out datetime
DECLARE @pur_out datetime
DECLARE @mfg_out datetime
DECLARE @lab_out datetime
DECLARE @eng_rel datetime
DECLARE @paintbox_out datetime
DECLARE @ship_out datetime
DECLARE @direction varchar(15)
DECLARE @department varchar(25)
BEGIN
SET NOCOUNT ON;
SELECT @mkt_out=eng_in,@eng_out=dft_in,@dft_out=dft_out,@pur_out=pur_out,@mfg_out=gageroom_out,
@lab_out=lab_out,@eng_rel=eng_rel,@paintbox_out=paintbox_out,@ship_out=ship_out,@direction=direction
FROM Trackem
WHERE bn=@bn
CASE
WHEN @mkt_out IS NULL THEN SET @department='Marketing'
WHEN @eng_out IS NULL THEN SET @department='Engineering'
WHEN @dft_out IS NULL THEN SET @department ='Drafting'
WHEN @pur_out IS NULL THEN SET @department = 'Purchasing'
WHEN @eng_rel IS NULL THEN
CASE @direction
WHEN 'Gageroom' THEN
IF @mfg_Out IS NULL
SET @department='Manufacturing'
WHEN 'Lab' THEN
IF @lab_Out IS NULL
SET @department= 'Lab'
WHEN 'BOTH' THEN OR @direction IS NULL
IF @mfg_Out IS NULL
SET @department='Manufacturing'
ELSE IF @lab_out IS NULL
SET @department = 'Lab'
ELSE
SET @department='ENG REL'
ELSE
IF @mfg_Out IS NULL
SET @department='Manufacturing'
ELSE IF @lab_out IS NULL
SET @department = 'Lab'
ELSE
SET @department='ENG REL'
END
WHEN @ship_out IS NULL THEN SET @department='Shipping'
END
SELECT @department
END
GO
July 29, 2012 at 10:42 am
Can you describe what the problem is?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
July 29, 2012 at 1:01 pm
Sorry if I was unclear , when I try to run this stored procedure, it gives me error that 'Incorrect syntax near Case'
July 29, 2012 at 1:50 pm
CASE has to be part of a select statement. For conditionals outside selects, use IF.
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
July 29, 2012 at 3:15 pm
Thank you that fixed it.
July 30, 2012 at 2:59 am
Hello schauhan13,
I think your Stored Procedure is 'perverted' by some kind of procedural thinking, and a set-based approach could be better. Lets me explain:
Your CASE statement tries to calculate a value for the @department var, so you can maintain that CASE statement with a little syntax change like that
CREATE PROCEDURE GetBNCurrentLocation
@bn varchar(10)
AS
DECLARE @mkt_out datetime
DECLARE @eng_out datetime
DECLARE @dft_out datetime
DECLARE @department varchar(25)
BEGIN
SET NOCOUNT ON;
SELECT @mkt_out=eng_in,@eng_out=dft_in,@dft_out=dft_out
FROM Trackem
WHERE bn=@bn
SET @department=CASE
WHEN @mkt_out IS NULL THEN 'Marketing'
WHEN @eng_out IS NULL THEN 'Engineering'
WHEN @dft_out IS NULL THEN 'Drafting'
END
SELECT @department
END
GO
I simplified your CASE logic for clarity.
Next, you can eliminate the intermediate SELECT:
CREATE PROCEDURE GetBNCurrentLocation
@bn varchar(10)
AS
DECLARE @department varchar(25)
BEGIN
SET NOCOUNT ON;
SELECT @department=CASE
WHEN eng_in IS NULL THEN 'Marketing'
WHEN dft_in IS NULL THEN 'Engineering'
WHEN dft_out IS NULL THEN 'Drafting'
END
FROM Trackem
WHERE bn=@bn
SELECT @department
END
GO
And now you can still remove the @department var:
CREATE PROCEDURE GetBNCurrentLocation
@bn varchar(10)
AS
BEGIN
SET NOCOUNT ON;
SELECT CASE
WHEN eng_in IS NULL THEN 'Marketing'
WHEN dft_in IS NULL THEN 'Engineering'
WHEN dft_out IS NULL THEN 'Drafting'
END
FROM Trackem
WHERE bn=@bn
GO
Finally, this 'BNCurrentLocation' is actually a calculated field on Trackem (suposing that bn is a unique key). Depending on your needs you can forget this stored procedure and define 'BNCurrentLocation' as a calculated field inside the table, or you can calculate it through a view and join that view when needed.
Best regards,
Francesc
July 30, 2012 at 3:21 am
schauhan13 (7/29/2012)
Sorry if I was unclear , when I try to run this stored procedure, it gives me error that 'Incorrect syntax near Case'
Code is much easier to debug outside of a stored procedure. Run it in SSMS (or your chosen client) and debug it there. Once it works and generates correct results, pack it into a stored procedure with plenty of descriptive comments.
Just as a matter of interest, you are declaring and populating a load of variables from one row in a table. What are you planning to do with those variables?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 30, 2012 at 6:37 am
Thank you very much!!!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply