?
Solved

If statment in Stored Procedure

Posted on 2004-11-04
22
Medium Priority
?
265 Views
Last Modified: 2008-03-10
I am having syntax issues with this:

ALTER                PROCEDURE sp_Add_ResourceRequest

      @ResourceID                  int = NULL,
      @RequestedByID                  int = NULL,
      @RequestStart                  datetime,
      @RequestedDiskSize            int= NULL,
      @CreatedDate                  datetime,
      @PurposeID                  int = NULL,
      @RequestIndefinite            bit,
      If @RequestIndefinite = 0
            @RequestEnd                  datetime
      else
      @RequestDetails                  varchar(500)
                End If
AS

I want to only add the Requestend date only if the indefiinte value is 0, otherwise, the end date is indefinite and shouldn't be inserted into my table...not sure if my use of the If statment is correct syntax here in my sp
0
Comment
Question by:dba123
  • 12
  • 4
  • 3
  • +2
22 Comments
 
LVL 1

Author Comment

by:dba123
ID: 12496320
Can anyone recommend a solid book out there on stored  procedures?  I like to use a combination of the web and books but haven't found a decent sp book that covers basic syntax rules such as this.
0
 
LVL 6

Assisted Solution

by:Duane Lawrence
Duane Lawrence earned 200 total points
ID: 12496379
Microsoft SQL Books online is a good start.

Hit the F1 key in Enterprise Manager or Query Analyser.

Duane
0
 
LVL 18

Accepted Solution

by:
ShogunWade earned 1400 total points
ID: 12496405
Hendersons   "The Gurus Guide to SQL Stored Procedures"   the best.


your code prob is that you have an IF statement in your param definitions:


ALTER                PROCEDURE sp_Add_ResourceRequest (
     @ResourceID               int = NULL,
     @RequestedByID               int = NULL,
     @RequestStart               datetime,
     @RequestedDiskSize          int= NULL,
     @CreatedDate               datetime,
     @PurposeID               int = NULL,
     @RequestIndefinite          bit,
           @RequestEnd               datetime,
     @RequestDetails               varchar(500)) AS
BEGIN

--My code goes here

END

     
0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
LVL 15

Expert Comment

by:mcmonap
ID: 12496419
Hi db123,

You can't so what you want, you would probably be best off giving them both a default value and then testing them in the body of your SP:

ALTER                PROCEDURE sp_Add_ResourceRequest

     @ResourceID               int = NULL,
     @RequestedByID               int = NULL,
     @RequestStart               datetime,
     @RequestedDiskSize          int= NULL,
     @CreatedDate               datetime,
     @PurposeID               int = NULL,
     @RequestIndefinite          bit,
     @RequestEnd               datetime IS NULL
     @RequestDetails               varchar(500) = NULL
AS

     If @RequestIndefinite = 0
          Do something with @RequestEnd
     else
          Do something with @RequestDetails
0
 
LVL 1

Author Comment

by:dba123
ID: 12496598
I could do something like this but it isn't very efficient because i would be repeating the same insert statement minus the end date which bothers me because I don't want to code sloppy like this:

If @RequestIndefinite = 0
     Insert (RequestID,RequestedBYID, RequestStart, RequestEnd.....)
else
     Insert (RequestID,RequestedbyID, RequestStart,......)     - skips the RequestEnd since Indefinite is 1

but that to me is bad coding because I have so much text in my sp and basically the lines are the same minus a field.
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12496670
Ok.  I havte to completely disagree with you but....

There are may people who develop SQL databases who think that "good code" is generic.  I catagorically say they are wrong and live in an unreal world.

Good SQL code is code that is fast. efficient, optimal, maintains locks for minimal time.   Bad SQL code is the use of dynamic sql when not absolutely required, for example.

Given that all you are trying to do in your code is not specify the RequestEnd if @RequestIndefinite = 1
then the most efficient way is to ditch the @RequestIndefinite  parameter and pass NULL into the @RequestEnd parameter

and insert it.   One Statement only.   Clean Tidy, efficient, good code.
0
 
LVL 1

Author Comment

by:dba123
ID: 12497370
true....ok, then how would I pass null to @RequestEnd and therefore RequestEnd field....you had it mixed up.  I want to ditch RequestEnd if RequestInfinite = 1
0
 
LVL 1

Author Comment

by:dba123
ID: 12497388
If @RequestIndefinite = 1 Then @RequestEnd = NULL End If

didn't work
0
 
LVL 1

Author Comment

by:dba123
ID: 12497416
Oh you are saying pass Null to that parameter via ASP to the sp....how do I pass null.   Setting the VBScript variable to "" results in an error when passed to my sp from ASP
0
 
LVL 1

Author Comment

by:dba123
ID: 12498029
hmm...good article.  Looking at the MSN article at that implicit vs. explicit chart, it shows that when converting from a varchar to datetime, that it is implicit...which means if I'm not wrong, you don't need CAST
0
 
LVL 1

Author Comment

by:dba123
ID: 12498036
ignore my last post, I posted on the wrong thread.
0
 
LVL 15

Expert Comment

by:mcmonap
ID: 12502262
Hi dba123,

Has this answered your question or do you still have a problem?
0
 
LVL 18

Assisted Solution

by:ShogunWade
ShogunWade earned 1400 total points
ID: 12502762
dba,  here is example:


ALTER                PROCEDURE sp_Add_ResourceRequest

     @ResourceID               int = NULL,
     @RequestedByID               int = NULL,
     @RequestStart               datetime,
     @RequestedDiskSize          int= NULL,
     @CreatedDate               datetime,
     @PurposeID               int = NULL,
     @RequestEnd               datetime IS NULL
     @RequestDetails               varchar(500) = NULL
AS
BEGIN
INSERT MtTable(...list of fields......) VALUES (@resourceID, @ ....etc...)
END
GO


EXEC sp_Add_ResourceRequest ... variables.....,NULL     <--- Last null is value passed for @requestend





PS one other minor point...... Dont call stored procedures sp_ ...     Why?   Because sp_  is for system stored procedures,    when you EXEC sp_ ...  SQL Server will first look in the master database for the proc.
0
 
LVL 1

Author Comment

by:dba123
ID: 12516123
>>>Dont call stored procedures sp
interesting, we start all our stored procs with that naming schema.  I am new to stored procedures and was just following the standard naming scheme everyone else was using for any of our databases.
0
 
LVL 1

Author Comment

by:dba123
ID: 12516131
>>>EXEC sp_Add_ResourceRequest ... variables.....,NULL

can you explain a little more on the syntax and about variables here.  So you are sayin insert all the fields except RequestEnd then execute it again and add null to RequestEnd only ?  what are the variables you are talking about here...do you mean the parameters?  If so, there wouldn't be any variables needed, I would just pass NULL to one field....RequestEnd for the EXEC - if that is what  you mean here.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 12517634
>>Oh you are saying pass Null to that parameter via ASP to the sp....how do I pass null. <<
Post your code as it stands now.
0
 
LVL 1

Author Comment

by:dba123
ID: 12520159
Here is my Stored Proc which works now but as you see, I have 2 sets of update statements dependant on the value of the Inifinite parameter.  I feel that the way I coded this is inefficient.  I no longer tried to do what I did at the beginning because it was not possible:

If I can't get a better way to code this, I'll just use as is and move on and close this question:

ALTER                      PROCEDURE sp_Add_ResourceRequest

      @RequestedByID                  int = NULL,
      @PurposeID                  int = NULL,
      @ResourceID                  int = NULL,
      @RequestStart                  datetime,
      @RequestEnd                  datetime,
      @RequestIndefinite            bit,
      @RequestDetails                  varchar(500),
      @RequestedDiskSize            int= NULL,
      @CreatedDate                  datetime

AS

SET NOCOUNT ON

-- ### Insert Resource Request
If @RequestIndefinite = 0
      INSERT INTO ERD.dbo.tbl_ResourceRequest
      (ResourceID, RequestedByID, CreatedDate, RequestStart, RequestEnd, RequestIndefinite, RequestDetails, RequestedDiskSize, PurposeID, Approved, Denied)
      VALUES
      (@ResourceID, @RequestedByID, @CreatedDate, @RequestStart, @RequestEnd, @RequestIndefinite, @RequestDetails, @RequestedDiskSize, @PurposeID, 0, 0)
else
      INSERT INTO ERD.dbo.tbl_ResourceRequest
      (ResourceID, RequestedByID, CreatedDate, RequestStart, RequestIndefinite, RequestDetails, RequestedDiskSize, PurposeID, Approved, Denied)
      VALUES
      (@ResourceID, @RequestedByID, @CreatedDate, @RequestStart, @RequestIndefinite, @RequestDetails, @RequestedDiskSize, @PurposeID, 0, 0)

SELECT SCOPE_IDENTITY() AS NewResourceRequestID

SET NOCOUNT OFF
0
 
LVL 1

Author Comment

by:dba123
ID: 12520168
you can see that on the else portion, I skip the insertion of RequestEnd thus producing a null in actuality because I'm not dong anthing to that field during the update if else is invoked.  
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 12521056
I was referring to your ASP code.  (Your question was asking how you pass a Null value to a Stored Procedure using ASP).
0
 
LVL 15

Assisted Solution

by:mcmonap
mcmonap earned 400 total points
ID: 12521492
Hi dba123,

I think something like below might allow you to drop your repetion of code, although I would point out your current way is not really inefficent (perhaps just in-elegant?!) Only one of the inserts will ever be processed so the only inefficient bit is typing coding itself.  You will still need to declare all your variables however if you call the the sp using the parameter names for the variables you can simply miss off the one you don't want ie:
--With a request end and request indefinite=1
sp_Add_ResourceRequest @ResourceID=192, @RequestedByID=123, @CreatedDate='2004-11-08', @RequestStart='2004-11-08',  @RequestDetails='some details', @RequestedDiskSize=36000, @PurposeID=1, @RequestIndefinite=1, @RequestEnd='2004-11-08'
--No request end and request indefinite=0
sp_Add_ResourceRequest @ResourceID=192, @RequestedByID=123, @CreatedDate='2004-11-08', @RequestStart='2004-11-08',  @RequestDetails='some details', @RequestedDiskSize=36000, @PurposeID=1, @RequestIndefinite=0

--start code
ALTER PROCEDURE sp_Add_ResourceRequest
        @RequestedByID         int = NULL,
        @PurposeID             int = NULL,
        @ResourceID            int = NULL,
        @RequestStart          datetime,
        @RequestEnd            datetime,
        @RequestIndefinite     bit,
        @RequestDetails        varchar(500),
        @RequestedDiskSize     int= NULL,
        @CreatedDate           datetime
AS

SET NOCOUNT ON

-- ### Insert Resource Request
INSERT INTO ERD.dbo.tbl_ResourceRequest
        (ResourceID,
        RequestedByID,
        CreatedDate,
        RequestStart,
        RequestEnd,
        RequestIndefinite,
        RequestDetails,
        RequestedDiskSize,
        PurposeID,
        Approved,
        Denied)
SELECT  @ResourceID,
        @RequestedByID,
        @CreatedDate,
        @RequestStart,
        CASE WHEN @RequestIndefinite = 0 THEN @RequestEnd ELSE NULL END,
        @RequestIndefinite,
        @RequestDetails,
        @RequestedDiskSize,
        @PurposeID,
        0,
        0

SELECT SCOPE_IDENTITY() AS NewResourceRequestID

SET NOCOUNT OFF
--end code
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12523094
isnt this essentially what i've already said ?
0
 
LVL 1

Author Comment

by:dba123
ID: 12527816
ShogunWade ....maybe but you never explained when I asked you for further explainationon your post for that.....I didn't understand your EXEC portion
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question