If statment in Stored Procedure

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.

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

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.

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.

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

If @RequestIndefinite = 1 Then @RequestEnd = NULL End If

didn't work

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

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

ignore my last post, I posted on the wrong thread.

Hi dba123,

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

>>>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.

>>>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.

>>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.

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

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.  

I was referring to your ASP code.  (Your question was asking how you pass a Null value to a Stored Procedure using ASP).

isnt this essentially what i've already said ?

ShogunWade ....maybe but you never explained when I asked you for further explainationon your post for that.....I didn't understand your EXEC portion