If statment in Stored Procedure

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
LVL 1
dba123Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dba123Author Commented:
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
Duane LawrenceCommented:
Microsoft SQL Books online is a good start.

Hit the F1 key in Enterprise Manager or Query Analyser.

Duane
0
ShogunWadeCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

mcmonapCommented:
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
dba123Author Commented:
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
ShogunWadeCommented:
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
dba123Author Commented:
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
dba123Author Commented:
If @RequestIndefinite = 1 Then @RequestEnd = NULL End If

didn't work
0
dba123Author Commented:
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
dba123Author Commented:
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
dba123Author Commented:
ignore my last post, I posted on the wrong thread.
0
mcmonapCommented:
Hi dba123,

Has this answered your question or do you still have a problem?
0
ShogunWadeCommented:
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
dba123Author Commented:
>>>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
dba123Author Commented:
>>>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
Anthony PerkinsCommented:
>>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
dba123Author Commented:
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
dba123Author Commented:
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
Anthony PerkinsCommented:
I was referring to your ASP code.  (Your question was asking how you pass a Null value to a Stored Procedure using ASP).
0
mcmonapCommented:
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
ShogunWadeCommented:
isnt this essentially what i've already said ?
0
dba123Author Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.