troubleshooting Question

Case when statement sql stored procedure

Avatar of Bran-Damage
Bran-DamageFlag for United States of America asked on
Microsoft SQL ServerMicrosoft SQL Server 2005
5 Comments1 Solution512 ViewsLast Modified:
I am in the process of writing a stored procedure and depending on what the user passes in, it is going to do different things.

When I am trying to create this stored procedure I get the following error
Msg 156, Level 15, State 1, Procedure stp_HarmonizedCodes, Line 26
Incorrect syntax near the keyword 'case'.
Msg 102, Level 15, State 1, Procedure stp_HarmonizedCodes, Line 30
Incorrect syntax near 'END'.

Basically, what I am trying to do is if the user did not pass in the @Code field, it will be null and it will select all values from the table with just the PlantID that was passed in.

If they do supply a value to the @Code field, then the select statement will use the @code field to do the select.

Any ideas?
CREATE PROCEDURE stp_HarmonizedCodes
@Method varchar(10),
@Code nvarchar(50) = null,
@Description nvarchar(256) = null,
@Active bit,
@PlantID nvarchar(50)



AS
BEGIN

	SET NOCOUNT ON;
	if @Method = 'Select' 
		begin
			Select * from HarmonizedCodes 
			case when @Code is not null then where code = @Code and plantid = @PlantID 
			else where plantid = @PlantID End
		End

END
GO
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 5 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros