Nulls in SP

I have a stored procedure (see below) that I need to make the last 2 
parameters optional and place a NULL value when they are not sent.  Can 
someone help show me how this is done? Thanks.

David

ALTER PROCEDURE [dbo].[mc_insHistoryDates]
 @PeopleLinkID  int,
 @HistoryTypeID  int,
 @HistoryReasonID int,
 @HistoryDate  date,
 @HistoryDateTo  date,
 @HistoryText  varchar(1000)
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

 INSERT INTO [MCFICore].[dbo].[HistoryDates]
           ([PeopleLinkID]
           ,[HistoryTypeID]
           ,[HistoryReasonID]
           ,[HistoryDate]
           ,[HistoryDateTo]
           ,[HistoryText])
 VALUES
  (@PeoplelinkID,
   @HistoryTypeID,
   @HistoryReasonID,
   @HistoryDate,
   @HistoryDateTo,
   @HistoryText);

 RETURN SCOPE_IDENTITY(); 


0
David
12/7/2009 7:21:51 PM
sqlserver.programming 1873 articles. 0 followers. Follow

2 Replies
821 Views

Similar Articles

[PageSpeed] 24

You simply add " parameter data_type = default_value " to the definition:

ALTER PROCEDURE [dbo].[mc_insHistoryDates]
  @PeopleLinkID  int,
  @HistoryTypeID  int,
  @HistoryReasonID int,
  @HistoryDate  date,
  @HistoryDateTo  date = NULL,
  @HistoryText  varchar(1000) = NULL
....

-- 
Plamen Ratchev
http://www.SQLStudio.com
0
Plamen
12/7/2009 7:40:21 PM
Plamen Ratchev wrote:
> You simply add " parameter data_type = default_value " to the definition:
> 
> ALTER PROCEDURE [dbo].[mc_insHistoryDates]
>  @PeopleLinkID  int,
>  @HistoryTypeID  int,
>  @HistoryReasonID int,
>  @HistoryDate  date,
>  @HistoryDateTo  date = NULL,
>  @HistoryText  varchar(1000) = NULL
> ...
> 
Footnote to this: only constants or other parameters are admissible as 
default values (this isn't so bad) and SQL Server allows you to write string 
constants without quotes here (this is awful). If you are foolish enough to 
write

   @HistoryDateTo  date = GETDATE,

you'll find that this compiles just fine but will fail when invoking the 
procedure without a value supplied, as SQL Server will try to convert the 
string "GETDATE" to a DATE. Using the ANSI equivalent CURRENT_TIMESTAMP or 
writing GETDATE() will reveal the problem immediately, as these *will* fail 
on compiling.

Incidentally, being able to write string constants without quotes doesn't 
mean you can write

   @HistoryDateTo  date = 20090101,

or

   @HistoryDateTo  date = 2009-01-01,

which will both fail for different reasons (although only the latter will 
fail at compile time).

-- 
J.
0
Jeroen
12/7/2009 11:44:12 PM
Reply:

Similar Artilces:

if A1=Null and B1=A1 why is result in B1=0 ??
if A1=Null and B1=A1 why is result in B1=0 ?? because of this I get wrong result for average calculations: average for (6,8,0) <> average for (6,8,null) Use =IF(A1="","",A1) -- HTH RP (remove nothere from the email address if mailing direct) "WGeorg" <WGeorg@discussions.microsoft.com> wrote in message news:09FCCF25-D0DC-4B1E-BFA1-CEF9A9327C46@microsoft.com... > if A1=Null and B1=A1 why is result in B1=0 ?? > because of this I get wrong result for average calculations: average for > (6,8,0) <> average for (6,8,null) Use this ...

Null Is Null
I've got a text box in a report that's not returning properly. Here's its Control Source: =[Reports]![rptFGPackConfigsPKWeights]![srptFGPKConfigsSUPK_totalwt]! [SumWtg]+IIf(IsNull([Reports]![rptFGPackConfigsPKWeights]! [srptFGPKConfigsTPK_totalwt]![SumWtg]),Null,"") This returns #Error. It's definitely my IIf statement as it returns properly when removed however there are times when [srptFGPKConfigsTPK_totalwt].[SumWtg] will be null. In fact, this subreport will not appear if no records exist. I suspect that this is the problem. How can I write this Control Source t...

Syntax for Is Not Null ?
When one of our forms is opened (Current event) I want a message box fired by certain conditions (that the Ref control does have a value, but the Country control is empty). I have tried various bits of code along the lines shown below, but keep getting runtime errors. Wot's wrong with the following, please? If [Me.Ref] is not null and [Me.Country] = "" Then MsgBox "Please enter the country!" Many thanks CW CW - Try this: If (not isnull([Me.Ref])) AND IsNull([Me.Country]) Then MsgBox "Please enter the country!" -- Daryl S ...

When is a Null not a Null?
I have a form which is used for adding and entering data depending on where it is called from. When called in add mode (acFormAdd) a blank form is openned. When the cursor enters the text box [Name] on the form field I am trying test whether it is empty so that a search form can be openned if a new name is to be added. (I don't want the search form to open in cases when the form already has data in and I am editing rather than adding data). The form is based on a query and the data displayed in [Name] is the result of a calculated field in the query which concatenates firstname and surn...

What is the Null character?
What is the character sequence that Excel uses to represent an empty cell? I am using a formula and when it returns false, I would like for it to represent the cell as if it was empty. I have tried using "" as an empty cell, but excel recognizes that this is a value, not an empty cell. You can't do what you want to do. A formula always returns *something*. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Quinn Ramsey" <Quinn Ramsey@discussions.microsoft.com> wrote in message news:D67F6311-0CCA-48B5-A...

SP returning XML
Can someone give me an example of how to use a stored procedure that returns XML data? I want to call the SP from a VBScript program, and put the XML into a data island in the generated HTML. Peter Morris wrote: > Can someone give me an example of how to use a > stored procedure that returns XML data? > > I want to call the SP from a VBScript program, > and put the XML into a data island in the generated HTML. Do you mean a procedure that runs a query containing a FOR XML clause? Bob Barrows -- Microsoft MVP - ASP/ASP.NET Please reply to the newsgroup. This email acco...

Charting null values
I have a chart that is plotting null values. I tried the formula =if(C25="",N/A(),Sum(C22:C25)). I've also deleted the formula to create a completely null cell. I've been to Tools, Options, Chart and selected Plot empty cells as Not plotted, zero and Interpollated As well as turning Plot Visible Cells only (turned it on and off) Nothing seems to make a difference. Any suggestions? Stephanie, what kind of chart are you using? Can you give an example of how your data table looks like? br, Henk "StephanieH" wrote: > I have a chart that is plotting ...

NULL values
Working with a downloaded table form a Lotus Notes contact manager. Now in access call tblclients. I query certain info. to update the records. BUT...'Is Null' does not work. It comes back empty and there are definitely empty contents in the fields I chose. I know how to do this as it works in my other tables on other databases. The only difference I can see in this table as opposed to tables created directly in Access is the Field Design property called Unicode Compression saying No instead of the default Yes. I changed the Unicode to Yes in case this had something to do with it and ...

PrimaryContactID NULL
Hello, i have discovered a strange behavior of CRM regarding the relationship between accounts and contacts: Some of my accounts show up a referenced contact but do not have a PrimaryContactID GUID in the databasefield. Both where converted from a lead which is refrenced with the correct GUID in the OriginatingLeadID Field in the database? Any idea how this can happen? Regards Sebastian When you convert a lead into an account/contact the contact references the account throught the parent customer field. The account does not reference the contact (this would be the primary contact fi...

Using CORREL with arrays containing null values
XL Gurus... I'm using the CORREL function, but one of the arrays I'm comparing has null values (entered as #N/A), so my result is #N/A. How can I modify my formula to correlate all the points in the 2 arrays, apart from the null values and their corresponding entries in the other array? You might incorporate an IF function, along the lines of IF(ISNUMBER(your array function here),your array function here,0) It seems redundant, but it evaluates the array function to determine if it is numeric. ...

Hide Sub Report(s) when records are null
I am running Access 2007 sp2 MSO. I have a report that has three (3) sub reports. There are times when not all three sub reports have values to display. Is there a way to code the given report to not be visible if there are no records to report? The second part of this question would be if there is a way to make the report invisible is it also possible to code so that the space where the report would normally reside would not be taken with a blank space (the foot print of where the report normally resides)? -- Bruce ...

Nulls in SP
I have a stored procedure (see below) that I need to make the last 2 parameters optional and place a NULL value when they are not sent. Can someone help show me how this is done? Thanks. David ALTER PROCEDURE [dbo].[mc_insHistoryDates] @PeopleLinkID int, @HistoryTypeID int, @HistoryReasonID int, @HistoryDate date, @HistoryDateTo date, @HistoryText varchar(1000) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; INSERT INTO [MCFICore].[dbo].[HistoryDates] ([PeopleLinkID] ...

Unique unless null?
Is there a way to set up a constraint, or something with the effect of a constraint, to prevent two rows in a table from having the same non-null value in a particular column while allowing multiple rows to have NULL in that row? Consider a table, some of whose data is imported from an external source, and the table has a column for storing the primary key value associated with the imported data in its source database. In other words, one way of looking at this column is that it's a 1..(0, 1) foreign key into another table that isn't present. Hi Harlan What version a...

Is null
Hi I'm having a problem with the "Is null" criteria in one of my queries. My query shows that there are 420 records with "is null" in the copies column. However in the corresponding table there are 467 records that have a blank entry in the copies column. Where am I going wrong? Thanks again! Adam Are you sure they're all Null? Some of the records could contain an empty field (""), or a space character (" "). http://www.databasedev.co.uk/null_values_defined.html "Adam" wrote: > Hi > > I'm having a problem with th...

GP ver 10 SP 3 Proj Acc Emp Exp problem (again)
Hi Folks I'm logging this again and I see that I'm not the only one experiencing this problem so could some Proj Acc fundi please try and help me and Jeff - it would be appreciated. I'm on PA build no 1215. In both Fabrikam and in my live company if I post an EE entry it arrives in the Payables module but does not get to the GL even though my Post to and Post Through are both marked for the Project and Purchasing series. This happens if I do a Transaction posting or if I do a Batch level post. If I then run Checklinks on Financail and Purchasing I am told that the distrib...

how do i use an if is null, and if is not null statement together
I have to compare two columns of data, and show the status in column three. Column1 = enrolleddate, Column 2= DisenrolledDate, Column 3 = Status If column1 and column2 are null, "Active", if column2 is not null, then disenrolleddate Thanks for helping me. If you were using a spreadsheet, you might need that third column. In Access you can simply use a query to do the comparison and generate the "calculated" value. Regards Jeff Boyce Microsoft Office/Access MVP "latha" <latha@discussions.microsoft.com> wrote in message news:A4366DA3-9E0E-48C8-BB30-452...

OnCtlColorDlg & return NULL
The MS documentation about WM_CTLCOLORDLG says: "If an application processes this message, it must return the handle of a brush. The system uses the brush to paint the background of the dialog box." The documentation about "CWnd::OnCtlColor" says something quite similar. But then when the wizzard generates OnCtlColor the included comment says: "TODO: Return a different brush if the default is not desired". Now I wonder if really a brush MUST(!) be returned. Returning NULL works. Nevertheless, that doesn't mean it's valid to do so. Finally, if I can re...

Determine NT 4.0 SP Level help..
Is there an easy way to figure out what service pack a NT 4.0 server was running by looking at a file time/date stamp from a restore tape?? We need to restore Exchange from a few years back, and it's critical to match the OS service pack in order to the get info store to mount... When looking at the tape catalogs, is there an easy way for me to figure out what NT service pack was running??? Did NTDETECT.COM or NTLDR change with each SP??? If so, is there a matrix anyway showing file time/date stamps and the associated service pack? Any help is greatly appreciated! Troy Tr...

NULL DATE
This works fine if there is a date in the textbox, but if the textbox is empty or null, I get an error. This is part of an SQL statement. What can I do if it's Null so that it still works. Thanks DS Format(Forms!frmBSDiscountNames![TxtExpDate], "\#mm\/dd\/yyyy\#") Change you line to Nz(Format(Forms!frmBSDiscountNames![TxtExpDate], "\#mm\/dd\/yyyy\#")) This will return a zero legnth string instead of a null value, and will allow your code to process it. Note that if at any point you are comparing that field to Null, it will fail, and you will have to compar...

Currency change partial in PoS 1.0 SP 1a
I changed default currency in the control panel but the change only reflect in Point of Sale Manager. Cash tender in PoS still shows $. How can I effect the change throughout the system? Your input will be highly appreciated. hi you need to restart the POS, and also make sure to check the Currencies window in Store Manager program it could be possible that there is currency format selected. Regards "Sokan33" wrote: > I changed default currency in the control panel but the change only reflect > in Point of Sale Manager. Cash tender in PoS still shows $. How can I &...

Checking a forms fields for null
Hi All, I can think of a few ways to accomplish this, but would like to know of a better way. just like when you fill out forms on the web if the field is mandatory it pops up a msg say somthing like "This field isn't optional" if it is left blank. What is the best way to do this in access on a form? I'm using now: if ISNull(myFeild1) then msgbox"Please fill in myFeild1" end if If ISNull(myFeild2) then msgbox"Please fill in myFeild2" end if if ISNull(myFeild3) then msgbox"Please fill in myFeild3" end if I use s...

Outlook 2003, Winxp SP#3, Comcast cannot get settings right
I have the following settings ( according to Comcast correct) Incoming: 110 mail.comcast.net Outgoing: 587 smtp.comcast.net But it will not connect. Any ideas? Won't connect to Pop 3 or SMTP? "Ritter 197" <Ritter197@comcast.net> wrote in message news:52FFD82B-1157-4691-ADF3-EEA1CD20B135@microsoft.com... :I have the following settings ( according to Comcast correct) : Incoming: 110 mail.comcast.net : Outgoing: 587 smtp.comcast.net : But it will not connect. : Any ideas? to POP3 "Tom [Pepper] Willett" <tom@your...

Norton On GP 10 SP 4
Hello all, we just installed Symantec Antiviurs version 9.0 on to our great plains server and now we can't login to GP and can't kill it once it's (sort of running) but all clients are fine. We tried to uninstall Symantec and no luck there either. Anyone else run into this problem? I am looking for an alternative to Manually uninstalling Symantec. Yes i did try and disable all the services before the uninstall and it does not even show up on our coporate edition even after doing the discovery. Everything else on the server is fine except GP... TIA On Oct 13, 2:12=A0pm,...

httpCookie is null
I have some code that when published to an IIS 6 box it works, but IIS 7 it does not. It has to do with cookies. Have some code in a SessionModule that when the client comes to the site, a cookie is created for the duration of their session. The code looks something like this Dim ctx As HttpContext = HttpContext.Current Dim cookie As HttpCookie = ctx.Request.Cookies("TestIt") If cookie Is Nothing Then Dim newGuid As String = Guid.NewGuid().ToString Dim mTimeTick As String = System.DateTime.Now.Ticks Dim mSmashIt A...

Inserting NULL into CString
Hello all, I need to provide parameters to stored procedures, and I've been using a 'lstrcpy' to do this; but along comes a stored procedure that needs NULLS as part of the prameter string. How do I get NULLs into a string? //old stored procedure string CString str = "123456"; //new stored procedure string CString str = "123456,\0,\0,\0"; lstrcpy( (char *) pSqlStmt, "{CALL info_sp("+str+")}"); retcode = SQLExecDirect(hstmt, pSqlStmt, lstrlen((char *) pSqlStmt)); When I look at 'pSqlStmt' for the strin...