Click here to Skip to main content
15,907,001 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
SQL
CREATE PROC [dbo].[CRM_PRC_FETCHQUOTESTATUS]                                
(                                
  @FIELD_NAME NVARCHAR(100) = NULL,                                
  @VENDOR_ID  NVARCHAR(100) = NULL    
)    
AS                                
BEGIN                                
  SET NOCOUNT ON                     

  SELECT     
    QS_STATUS AS 'Text',    
   (
     CASE WHEN @FIELD_NAME  ='POLICYSTATUS' 
       THEN QS_STATUSCODE
       ELSE QS_STATUS 
     END
   ) AS  'Value'    
   FROM CRM_MST_QUOTESTATUS WITH (NOLOCK)    
   WHERE QS_TYPE = @FIELD_NAME     
END
Posted
Updated 5-Jan-14 23:47pm
v2
Comments
OriginalGriff 6-Jan-14 5:44am    
And?
Your question is?
What is it doing that it shouldn't, or not doing that it should?

sql-case statement should be used if multiple conditions are there
you are using only one case, that you can perform in if condition.


SQL
if (@FIELD_NAME  ='POLICYSTATUS' )
   select  QS_STATUSCODE AS 'Text' , QS_STATUSCODE  AS  'Value'   FROM CRM_MST_QUOTESTATUS WITH (NOLOCK)
   WHERE QS_TYPE = @FIELD_NAME
   else
   select  QS_STATUS AS 'Text' , QS_STATUSCODE  AS  'Value'   FROM CRM_MST_QUOTESTATUS WITH (NOLOCK)
   WHERE QS_TYPE = @FIELD_NAME


please have a look at this doc
A Simple Use of SQL CASE Expression[^]
 
Share this answer
 
CREATE PROC [dbo].[CRM_PRC_FETCHQUOTESTATUS]
(
@FIELD_NAME NVARCHAR(100) = NULL,
@VENDOR_ID NVARCHAR(100) = NULL
)
AS
BEGIN
SET NOCOUNT ON

SELECT
QS_STATUS AS Text,
Value =
CASE WHEN @FIELD_NAME ='POLICYSTATUS'
THEN QS_STATUSCODE
ELSE QS_STATUS
END
FROM CRM_MST_QUOTESTATUS WITH (NOLOCK)
WHERE QS_TYPE = @FIELD_NAME
END

Having a database that stores field names and field values is an antipattern, you should not do it, it throws away most of what is useful about using a database at all.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900