dottedquad


Currently I have the follow code:

Code Snippet

USE [PnpCart]
GO
/****** Object: StoredProcedure [dbo].[pnpcart_GetCustomer_Details] Script Date: 08/12/2007 20:12:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: dbo
-- Create date: 08/01/2007
-- Description: Gets Customer Details
-- =============================================
ALTER PROCEDURE [dbo].[pnpcart_GetCustomer_Details]
-- Add the parameters for the stored procedure here
@inSearchBy varchar(20),
@inSearchFor varchar(100)

AS

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
CASE Email
WHEN Email
SELECT Email FROM dbo.pnpcart_Customer_Details WHERE Email = @inSearchFor
WHEN UserName
SELECT UserName FROM dbo.pnpcart_Customer_Details WHERE UserName = @inSearchFor
WHEN HomePhone
SELECT HomePhone FROM dbo.pnpcart_Customer_Details WHERE HomePhone = @inSearchFor
END
END


I'm getting the following error:

Msg 156, Level 15, State 1, Procedure pnpcart_GetCustomer_Details, Line 19
Incorrect syntax near the keyword 'CASE'.
Msg 156, Level 15, State 1, Procedure pnpcart_GetCustomer_Details, Line 22
Incorrect syntax near the keyword 'WHEN'.
Msg 156, Level 15, State 1, Procedure pnpcart_GetCustomer_Details, Line 24
Incorrect syntax near the keyword 'WHEN'.
Msg 102, Level 15, State 1, Procedure pnpcart_GetCustomer_Details, Line 27
Incorrect syntax near 'END'.

I need a hand with creating this simple stored procedure. Thanks in advance :-)

-Thanks



Re: need help with my first stored procedure

Suprotim Agarwal


Dear dotterquad,

Try this

Add another variable @inputstr VARCHAR(20)

Then after BEGIN

CASE WHEN @inputstr = 'Email' THEN SELECT Email From ......

CASE WHEN @inputstr = 'UserName' THEN SELECT Username = ......

and so on.

Have not tested the query. Kindly do so.

HTH,

Suprotim Agarwal







Re: need help with my first stored procedure

dottedquad

I've changed my code to the following:
Code Snippet



ALTER PROCEDURE [dbo].[pnpcart_GetCustomer_Details]
-- Add the parameters for the stored procedure here
@inSearchBy varchar(20),
@inSearchFor varchar(100)

AS

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
CASE @inSearchBy
WHEN @inSearchBy = 'Email'
SELECT
Email
FROM dbo.pnpcart_Customer_Details
WHERE Email = @inSearchFor
END

Now I only receive one error:

Msg 156, Level 15, State 1, Procedure pnpcart_GetCustomer_Details, Line 19
Incorrect syntax near the keyword 'CASE'.






Re: need help with my first stored procedure

Suprotim Agarwal

Dear dottedquad,

Try this :

CASE WHEN @inSearchBy = 'Email' THEN SELECT .....

CASE WHEN @inSearchBy = 'UserName' THEN SELECT...

I am not able to test your code as i do not have sql on this machine. So you will have to test it and tell me the results Smile

HTH,

Suprotim Agarwal






Re: need help with my first stored procedure

Aneesh

You can use this also


ALTER PROCEDURE [dbo].[pnpcart_GetCustomer_Details]
-- Add the parameters for the stored procedure here
@inSearchBy varchar(20),
@inSearchFor varchar(100)

AS

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
CASE @inSearchBy
WHEN 'Email' THEN
SELECT
Email
FROM dbo.pnpcart_Customer_Details
WHERE Email = @inSearchFor
END







Re: need help with my first stored procedure

Arnie Rowland

CASE is NOT a complete query. It is only a part of the 'adjective'.

You MUST use CASE in a SELECT statement, or as part of a IF test or WHILE test.

Also, using WHILE as you have creates an endless loop IF the @InSearchBy value = 'Email'

For example, in your situation, an IF test would be better, avoiding the endless WHILE loop:

BEGIN

SET NOCOUNT ON

IF @InSearchBy = 'Email'

SELECT Email

FROM dbo.pnpcart_Customer_Details

WHERE Email = @InSearchFor

END

However, this doesn't make any sense. You don't need to do a SELECT since you already have the Email value.






Re: need help with my first stored procedure

oj

Case/when is an expression. Think of it as IIF().

What you're looking for is IF/Else - which is a flow control.

So, your proc should look like this.

USE [PnpCart]
GO
/****** Object: StoredProcedure [dbo].[pnpcart_GetCustomer_Details] Script Date: 08/12/2007 20:12:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: dbo
-- Create date: 08/01/2007
-- Description: Gets Customer Details
-- =============================================
ALTER PROCEDURE [dbo].[pnpcart_GetCustomer_Details]
-- Add the parameters for the stored procedure here
@inSearchBy varchar(20),
@inSearchFor varchar(100)

AS

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

If (@inSearchBy='Email')

begin
SELECT Email FROM dbo.pnpcart_Customer_Details WHERE Email = @inSearchFor
end

else if(@inSearchBy='UserName')

begin

SELECT UserName FROM dbo.pnpcart_Customer_Details WHERE UserName = @inSearchFor
end

else if(@inSearchBy='HomePhone')

begin
SELECT HomePhone FROM dbo.pnpcart_Customer_Details WHERE HomePhone = @inSearchFor

end
END






Re: need help with my first stored procedure

dottedquad

Your suggestion worked as planned with some modification:

Code Snippet

IF (@inSearchBy='Email')
begin
SELECT * FROM dbo.pnpcart_Customer_Details WHERE Email = @inSearchFor
end
IF (@inSearchBy='UserName')
begin
SELECT * FROM dbo.pnpcart_Customer_Details WHERE UserID = @inSearchFor
end
IF (@inSearchBy='HomePhone')
begin
SELECT * FROM dbo.pnpcart_Customer_Details WHERE HomePhone = @inSearchFor
end


From my understanding SELECT * FROM dbo.pnpcart_Customer_Details WHERE UserID = @inSearchFor should return everything partaining to that search but it only returns the column's data that it's searching for. Now, if I create a new query and execute the following statement:
SELECT * FROM dbo.pnpcart_Customer_Details WHERE Email = 'foo@foo.com' it outputs everything in that record it finds. I have no idea why this is doing this. Any help is greatly appreciated thanks.




Re: need help with my first stored procedure

dottedquad

Apparently i forgot to execute the new sql code and now it works :-)

-Thanks