Select Statement/If statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select Statement/If statement

Author

Topic

munkee
Starting Member

5 Posts

Posted - 2011-11-01 : 12:07:43

Hi all,

Having a go with stored procedures and I am confused by the best way to perform the following.

I want to supply a value as an input parameter and then execute code that is dependent on this value.

In VBA this is typically handled as a Select Case statement but I do not know the equivalent in SQL server.

ALTER PROCEDURE [dbo].[sp_Status_Timing] 
	-- Add the parameters for the stored procedure here
	@NCC_ID int,
	@StatusType int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
IF exists (select * from tblStatusTiming where NCIDLINK=@NCC_ID AND StatusType=@StatusType)
BEGIN 
  --Run update  
END 
ELSE 
BEGIN 
	-- Run insert
END 

	
END

I want to check the @StatusType parameter and execute a different form of update or insert dependent on the value.

The only way I can explain is something I tried such as:

Select @StatusType
Case 1
-- Code to run
Case 2
-- Code to run
Case 3
-- Code to run
Else
-- Code to run
End Select

I have tried this with IF statements in SQl but it looks quite messy also.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-01 : 12:12:55

you can use CASE expression in t-sql also like below

http://msdn.microsoft.com/en-us/library/ms181765.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-01 : 12:18:10

Depends on the code.
If it is just updating different columns or different values then it is probably a single statement.
If the processing is very different then it would need to be if statements

if @StatusType = 'type1'
begin
...
end
else if @StatusType = 'type2'
begin
...
end
else if @StatusType = 'type3'
begin
...
end

If you give more info about the sort of processing maybe we could help more.
Usually, if there is seperation of processing like this, then it is often common processing followed by a seperated bit at the end.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

munkee
Starting Member

5 Posts

Posted - 2011-11-02 : 03:36:57

The code I am converting is currently sat in a ms access database. Some of the code I will be keeping on the client side but I want to do all the record editing on the server side:

                   Select Case Forms.frm_log_testing.NCStatus
                      Case 1 'initial notification
170                       With rst
180                           .Edit
190                           ![Day Entered] = Me.DteReport
200                           ![UserIDMoved] = Forms.frmhidden.txtSecurityID
210                           .Update
220                       End With
230                   Case 2 'Awaiting Acceptance
240                       With rst
250                           .Edit
260                           ![StatusType] = Me.NCStatus
270                           ![Day Entered] = Date
                            
280                           ![UserIDMoved] = Forms.frmhidden.txtSecurityID
290                           .Update
300                      End With
310                      strEmail1 = "The nonconformance event you reported with ID : " & Me.NCC_ID & " has now moved to the following status: Awaiting Acceptance <p>" _
                         & "Regards, <br> NCC Log"
                         
320                           If Len(DLookup("[ReportedEmailAddy]", "tbllog", "[NCC_ID]=" & Me.NCC_ID)) > 0 Then
                              'if an email addres is found then get on with it
330                            Call EmailIT(strEmail1, "Nonconformance event feedback for case ID: " & Me.NCC_ID, strEmailAddy = DLookup("[ReportedEmailAddy]", "tbllog", "[NCC_ID]=" & Me.NCC_ID), "NCCLog-DoNotReply@Siemens.com")
340                           Else
                              'if not then do nothing dont bother sending out a email
350                           End If
                         
                        
360                   Case 3 ' Action Being Taken
370                    With rst
380                           .Edit
390                           ![StatusType] = Me.NCStatus
400                            ![Day Entered] = Date
410                           ![UserIDMoved] = Forms.frmhidden.txtSecurityID
420                           .Update
430                       End With
440                       strEmail2 = "The nonconformance event you reported with ID #: " & Me.NCC_ID & " has now moved to the following status: Action Being Taken <p>" _
                          & "The case is now under the ownership of " & Me.NCOwner.Column(1) & "<p> Regards, <br> NCC Log"
                          
450                           If Len(DLookup("[ReportedEmailAddy]", "tbllog", "[NCC_ID]=" & Me.NCC_ID)) > 0 Then
460                               Call EmailIT(strEmail2, "Nonconformance event feedback for case ID: " & Me.NCC_ID, DLookup("[ReportedEmailAddy]", "tbllog", "[NCC_ID]=" & Me.NCC_ID), "NCCLog-DoNotReply@Siemens.com")
470                           Else
480                           End If
                          
490                   Case 4 ' Awaiting Review
500                       With rst
510                           .Edit
520                           ![StatusType] = Me.NCStatus
530                           ![Day Entered] = Date
                          
540                           ![UserIDMoved] = Forms.frmhidden.txtSecurityID
550                           .Update
560                       End With

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-02 : 05:07:20

for this purpose you can CASE WHEN itself in sql server

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-02 : 06:19:30

This depends on Forms.frm_log_testing.NCStatus
What is the equivalent for the SP. It looks like a static value for the process.
What is the equivalent of rst - is that a table?

You could maybe use a case statement and a single update statement. Get NCStatus into a variable @NCStatus

update tbl
set [Day Entered] = case when @NCStatus in ('initial notification') then getdate() else [Day Entered] end ,
[UserIDMoved] = case when @NCStatus in ('Awaiting Acceptance','Action Being Taken','Awaiting Review') then NCStatus else [UserIDMoved] end ,
...

But I would be tempted to use the if statements as it might be easier to maintain and also give scope for other processing (also doesn't touch unchanged columns which might be important).

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources