Out Parameter Value Is NULL

Mar 19, 2008

I Created a Procedure that Generates an ID value based in the current Date (Year/Month) and a value which are stored in a table. This ID value is update in the table, and passed on to an output parameter as folows:





Code Snippet




CREATE PROCEDURE [dbo].[CNSPGetNewGuideID]
@SectionID bigint
,@Year bigint
,@Month bigint
,@ID bigint output
AS
BEGIN




(...)

SELECT @ID = [Year]* 10000000 + [Month] * 100000 + [Number]
FROM [dbo].[CNGuideID]
WHERE [Year] = @Year
AND [Month] = @Month





print 'New ID : '
print @ID
END


This is working and the value printed here is the correct one.

but when I execute the procedure inside another one, the variable i pass on as an output parameter remains NULL...





Code Snippet




CREATE PROCEDURE [dbo].[CNSPInsertNewGuide]
@SectionID int
,@ClientID int
,@UsuaryID bigint
,@UsuarySectionID int
,@ID int OUTPUT
AS
BEGIN

DECLARE @Date as datetime
DECLARE @Year as int
DECLARE @Month as int

SET @Date = GetDate()
SET @Year = YEAR(@Date)
SET @Month = MONTH(@Date)

EXEC CNSPGetNewGuideID @sectionID,@Year,@month,@ID

(...)




I also tried just declaring a variable and executing the procedure but the same thing happens





Code Snippet
declare @ID2 bigint


EXEC CNSPGetNewGuideID 06,2008,03, @ID2

print 'ID : '
print @ID2




this is the result I get:

New ID :
20080300030
ID :

I must be missing something...
Can anyone help?

thanks.

View 3 Replies


ADVERTISEMENT

Reporting Services :: Give Meaning Full Name To Allow Null Value Check Box In Report Parameter Instead Of NULL?

Oct 20, 2015

In my report i have CNAME parameter , which allows null value. I checked Allow null value check box in report parameter properties.

when i preview the report , it displays checked NULL check box beside CNAME parameter . I want to give some meaningful name(i.e.ALLCustomers) to this checkbox instead of NULL. 

Is it possible through SSRS designer?

View 5 Replies View Related

Subscription Issue With Null Default Parameter - Key Cannot Be Null

May 3, 2007

I have a report that is run on a monthly basis with a default date of null. The stored procedure determines the month-end date that it should use should it be sent a null date.

The report works fine when I tell it to create a history entry; however, when I try to add a subscription it doesn't appear to like the null parameter value. Since I have told the report to have a default value of null it doesn't allow me to enter a value on the subscription page.

Now, I suppose I could remove the parameter altogether from the stored proc, but then the users would never be able to run the report for a previous time period. Can someone explain to me why default values aren't allowed to be used on subscriptions when they seem to work fine for ad hoc and scheduled reports? This is really quite frustrating as most of my reports require a date value and default to null so that the user doesn't have to enter them for the latest data.



An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help




Key cannot be null. Parameter name: key

View 1 Replies View Related

Reporting Services :: Share Point List Data Set Parameter Allow Null In Multivalue Drop Down Parameter

Aug 28, 2015

my dataset from sharepoint list. and this dataset value assign to parameter. i want when no any parameter is selected than it should filter like "ALL". when i select alow null value it give me prompt error you  can not select null in multivalue parameter.How can i do it. i am using share point list.

View 3 Replies View Related

Filters For Dataset From Parameter - How To Ignore If The Parameter Is NULL ?

Apr 10, 2007

Hi,

I am starting to use reporting services and I created a report that takes 4 parameters for a Filter on the dataset.

The idea is the create snapshot nightly and then display the report filtered based on parameters to users.



I'd like that the filter be ignored for a parameter if the parameter is passed as NULL,

Right now I defined it this way :



Left =Fields!RegionCode.Value
Operator =
Right =IIF(IsNothing(Parameters!RegionCode.Value),Fields!RegionCode.Value,Parameters!RegionCode.Value)

I do this for the 4 parameters that are passed, if they are null, make then equals to the field.

I was wondering if there is a way to ignore the whole parameter all together, I would guess it'll be faster to execute.



Thanks

View 5 Replies View Related

Null Parameter

May 5, 2008

I have a sql proc. I need to make a parameter optional. I've setup the parameter to use a dataset as its source, and I need NOT to allow the value to be null. I've NOT selected allow nulls check box, But, when the parameter is null (‘’), still data comes back in a table as empty or if it is date will default to 1/1/1900. How do I send a null value to the database through sql proc and raise an error without inserting a null value? I’m hoping to avoid creating a special query just for that purpose.

Thanks.


Josephine

View 3 Replies View Related

Using NULL Xml Parameter

Feb 7, 2008

Hi all,

I'm passing a list of values to a stored procedure using an XML parameter - using SQL 2005.

The stored procedure is used to search a table for matching values so this list can contain one or many values, or could be NULL to indicate all values should be returned.

Returning one or more than one value works fine in the code sample given below. How can I phrase my query to cope with the scenario of the parameter being NULL to select all values?

I currently have the following code to cope with one or many values being passed:






Code Snippet

SELECT * FROM dbo.MyTable
WHERE idField IN
(SELECT paramvalues.id.value('.', 'int') FROM @testxml.nodes('/rootnode/idfieldvalue') as paramvalues(id))
where @testxml is the XML parameter passed into the stored procedure.

Thanks as always

Shivonne

View 1 Replies View Related

Null Parameter

Sep 26, 2007

In the report I created, there is a drop down used as a parameter, i.e. Sector.
This drop down gets populated with data. It also has <Select a Value> in there.
When no item is selected, i.e. only <Select a Value> is shown in the drop down list, when the view Report button is clicked, the message says:
Please select a value for the parameter 'Sector'
The view Report used a stored procedure which allows null for this Sector parameter.
The SP does work fine in the sql server query analyser because it returns data with and without the sector parameter.
Any thoughts please?
Thanks

View 3 Replies View Related

Key Cannot Be Null. Parameter Name: Key

Mar 27, 2007

We are using forms authentication with SSRS2005 and we are able to access Report Manager (http://servername/reports) and Reportserver (http://servername/reportserver). The problem we are having is every time we add a user to the Content Manager or the Publisher role. The user is able to login and reach the Report Manager but no folder is showing up and also we have the following error message : "Key cannot be null. Parameter name: key".



We removed users from the Content Manager and Publisher Roles and they are able to reach report manager and see the folders. This only happens with report manager. everything works fine with reportserver(http://servername/reportserver).

Any idea why we're getting an error message when trying to assign the Content Manager and publisher Roles to a user.



here is the error log from ReportServerWebApp :



aspnet_wp!ui!1!3/27/2007-16:38:49:: e ERROR: Key cannot be null.
Parameter name: key
aspnet_wp!ui!1!3/27/2007-16:38:49:: e ERROR: HTTP status code --> 500
-------Details--------
System.ArgumentNullException: Key cannot be null.

Parameter name: key

at System.Collections.Hashtable.Insert(Object key, Object nvalue, Boolean add)

at System.Collections.Hashtable.Add(Object key, Object value)

at Microsoft.ReportingServices.UI.BasePermissions.ReadPermissions(String[] permissions)

at Microsoft.ReportingServices.UI.Permissions.GetPermissions()

at Microsoft.ReportingServices.UI.Permissions.CurrentUser(String itemPath)

at Microsoft.ReportingServices.UI.FolderPage.Page_Init(Object sender, EventArgs e)

at System.EventHandler.Invoke(Object sender, EventArgs e)

at System.Web.UI.Control.OnInit(EventArgs e)

at System.Web.UI.Page.OnInit(EventArgs e)

at System.Web.UI.Control.InitRecursive(Control namingContainer)

at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
aspnet_wp!ui!1!3/27/2007-16:38:49:: e ERROR: Exception in ShowErrorPage: System.Threading.ThreadAbortException: Thread was being aborted.
at System.Threading.Thread.AbortInternal()
at System.Threading.Thread.Abort(Object stateInfo)
at System.Web.HttpResponse.End()
at System.Web.HttpServerUtility.Transfer(String path, Boolean preserveForm)
at Microsoft.ReportingServices.UI.ReportingPage.ShowErrorPage(String errMsg) at at System.Threading.Thread.AbortInternal()
at System.Threading.Thread.Abort(Object stateInfo)
at System.Web.HttpResponse.End()
at System.Web.HttpServerUtility.Transfer(String path, Boolean preserveForm)
at Microsoft.ReportingServices.UI.ReportingPage.ShowErrorPage(String errMsg)



Thanks!



chi

View 4 Replies View Related

How Can I Pass NULL To A Parameter

Dec 11, 2006

How can I pass NULL to a parameter, if now entry is made in the textbox?
Dim KeywordParam As New SqlParameter("@Keyword", Me.KeyWordText.Text)
MyCommand.Parameters.Add(KeywordParam)

View 1 Replies View Related

Include All If Parameter Value Null?

Mar 14, 2007

Hi:I have an application in which I am producing a lists of media outlets based on a number of 6 possible parameters including location, medium, format, scope, language and coverage.  I have an interface in which users can select some or all of the possible parameters.  Where they don't choose a parameter - e.g. location, I would like the SELECT query to include all locations.    I am at the point now of writing 6 X 6 = 36 possible queries to handle the different combinations of possible parameters.  This seems a bit excessive and clumsy.  I am just learning about stored procedures and was wondering if it is possible to create a procedure that would accept the 6 parameters including ones with null or 0  values and generate a query that would ignore those that were null or zero. Any help in this regard greatly appreciated.Roger Swetnam 

View 5 Replies View Related

Delete Parameter Getting Set To NULL Why?

May 27, 2008

I have datagrid whose data source is set to SqlDataSource. SqlDataSource's connection string is set dynamically at pageload. When i click on delete link in gridview it executes delete command but row doesnt get deleted from data source. When i run Sql server profiler i found the delete statement is getting executed but the parameter is always null. why?
  <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"
AutoGenerateColumns="False"
Caption="Events emitted by the application." DataKeyNames="EventId" OnDataBound="GridView1_DataBound" Width="600px" OnSelectedIndexChanged="GridView1_SelectedIndexChanged" OnRowDeleting="GridView1_RowDeleting" OnRowCommand="GridView1_RowCommand">
<Columns>
<asp:BoundField DataField="EventId" HeaderText="EventId" SortExpression="EventId" />
<asp:BoundField DataField="EventTime" HeaderText="EventTime" SortExpression="EventTime" />
<asp:BoundField DataField="Message" HeaderText="Message" SortExpression="Message" />
<asp:CommandField SelectText="Vew Details" ShowDeleteButton="True" ShowSelectButton="True" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" DeleteCommand="DELETE FROM aspnet_WebEvent_Events WHERE (EventId = @EventId)"
SelectCommand="SELECT EventId,EventTime,Message FROM aspnet_WebEvent_Events WHERE (ApplicationVirtualPath = @ApplicationVirtualPath)">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList2" Name="ApplicationVirtualPath" PropertyName="SelectedValue" />
</SelectParameters>
<DeleteParameters>
<asp:Parameter Name="EventId" Type="String"/>
</DeleteParameters>
</asp:SqlDataSource>
 protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
}

protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName == "Delete")
SqlDataSource2.Delete();
}  

View 4 Replies View Related

Value Cannot Be Null. Parameter Name: DataSet (was SQL Help :()

Feb 11, 2005

I keep getting "Value cannot be null. Parameter name: dataSet" when I run this statement and bind it to a dataset using a sqldataadapter in ASP.NET using VB.NET.


"SELECT playerstats.playerid, playerstats.gameid, SUM(playerstats.fta), SUM(playerstats.ftm), SUM(playerstats.tpm), SUM(playerstats.rb), SUM(playerstats.fga), SUM(playerstats.fgm), SUM(playerstats.tp), SUM(playerstats.st), SUM(playerstats.a), 100(SUM(playerstats.fgm)/SUM(playerstats.fga)) AS fgp, player.playerid, player.lname + ', ' + player.fname AS fullname FROM playerstats, player WHERE playerstats.playerid = player.playerid AND player.leagueid = " & ddlLeague.SelectedValue & " ORDER BY " & strSortField


I know there is data in the db.

Please help :confused:

View 6 Replies View Related

Dealing With A Null Parameter In SP

Jul 20, 2005

I have a stored procedure as a recordsource from a contacts table. Inthis example, users can enter parameters to limit contacts by firstletter of last name or company name or keywords:Example:@myName nvarchar(30) = null,@Alpha char(1) = nullSELECTContacts.ContactID, ContactType,CASE WHEN Contacts.ContactType = 0THEN Contacts.CompanyNameELSEisNull(Contacts.LastName,'?') + ', ' +isNull(Contacts.FirstName,'?')ENDAS CNAMEFROMContactsWHERE(Keywords Like '%' + @myKeyword + '%' OR @myKeyword is Null)So far, so good, but...The problem is I want to also give the user the option of filteringalphabetically by first letter. I can't figure out how to deal withnulls in this example (user doesn't enter anything as parameter@Alpha):AND(@Alpha = CASE ContactType WHEN 0 THEN Left(LastName,1) ENDOR@Alpha = CASE ContactType WHEN 1 THEN Left(CompanyName,1) END)Any help is appreciated,LQ

View 2 Replies View Related

Allow Null Value In Multi-value Parameter

Jun 28, 2007

Hello,



I have a multi-value parameter field and each item comes from a table in the DB.

This multi-value parameter allows to select all or some of the items.

What if I don't want to select ANY item??



I read in the forum the several tray to do the same but the answers are not so clear..



If I say that at the end is a bug in SSRS... AM I right?



Thank you



Marina B.

View 1 Replies View Related

DateTime Parameter With NULL Value

Apr 8, 2008

Hi,
I have a report with datetime parameter which is required to be optional (there should be an option not to select this parameter which would cause in not narrowing result of this report by this parameter).

I used datetime parameter (to have the calendar control for selecting date) for which I enabled NULL value. The result is that next to this parameter there is a checkbox "NULL" which allows to send NULL as a value of this parameter. Everything is fine except for the prompt: "NULL"... Is there a way to change it? I would see there something like "Empty" or "Not selected", which would be understandable for users of the report.

Or is there any other way to send an empty date as a value of this parameter? (Setting some unusual value like 1/1/1900 for empty date is not sufficient as the empty value shouldn't appear in the parameter control).

Thank you,
Michal

View 9 Replies View Related

Null Hidden Parameter

Aug 1, 2007

In my report I have a dataset that is returning an ID field among other things. I am setting a hidden parameter = to that ID field. That parameter is then used to return another dataset.

My problem is that the original dataset with the ID field does not always bring back data (this is expected). If data is not brought back, I get an error when I try to execute the report ("The 'X' parameter is missing a value").

I have the parameter set to "Allow Null Values".

This value is not required in the SQL Server stored procedure that returns values for the new dataset.
The parameter is of type Integer. (So I am not able to set the "Allow Blank Value" property)
This is NOT a multi-value parameter.


Does anyone have any suggestions/ideas/workarounds to solve my problem?

View 2 Replies View Related

.NET Framework :: Parameter Out With Null Value

Sep 3, 2015

I am having a trouble when I try to retrieve a SQL parameter outside to .Net Clr

I did a clr example like this:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Text;
using Microsoft.SqlServer.Server;

[Code] ...

I now my trouble and it is because the parameter @p_it is null when I try to sed it to pipe in SqlContex but really it is not null. When I test the clr with this code:

CREATE ASSEMBLY
[Test]
FROM
N'Z:SomeRouteTest.dll'
WITH PERMISSION_SET = SAFE
GO
CREATE PROCEDURE [dbo].[Test]

[Code] ....

I obtain the next error message:

"Mens 6522, Nivel 16, Estado 1, Procedimiento Test, Línea 14
A .NET Framework error occurred during execution of user-defined routine or aggregate "Test":
System.ArgumentNullException: El valor no puede ser nulo. -----> The value can not be null
Nombre del parámetro: message
System.ArgumentNullException:
   en Microsoft.SqlServer.Server.SqlPipe.Send(String message)
   en Test.StoredProcedures.Test(SqlString p_t)"

View 3 Replies View Related

Set NULL To Datetime Parameter

Dec 14, 2006

Hi All,

I have a problem while trying to set "NULL" value to my datetime parameters.

I have two datetime parameters (AllowNull=True) on my report. (BeginDate and EndDate)

it works fine in preview mode in reporting design.

The problem occurs, if I try to set an emtpy value to these parameters in my asp.net application.

The code looks like;

Dim repParams(1) As ReportParameter

repParams(0) = New ReportParameter("BeginDate", String.Empty)

repParams(1) = New ReportParameter("EndDate", String.Empty)

-----

ReportParameter method requires String parameters. If user set any datetime value, I am sending "String.Empty" value to my paramaters in order to set them as NULL. But I am getting "rsReportParameterTypeMismatch Error."

How Can I set NULL value to these parameters from asp.net application?

Thans alot in advance.

View 9 Replies View Related

Null Parameter In Uniqueidentifier Field

Dec 10, 2005

I have the following database structure:

The table aspnet_users, which we all know

Another table with an uniqueidentifier which references the user iD

I want to set the value of the iD in one of the entries in the second table to null.

I tried this:
string assigned is null, i am passing this as a method parameter
...............
string queryString = "UPDATE  [table2] SET  AssignedUserId=@assigned WHERE ProblemId = @id";
        System.Data.IDbCommand dbCommand = new System.Data.SqlClient.SqlCommand();
...................
System.Data.IDataParameter dbParam_au_pr = new System.Data.SqlClient.SqlParameter();
        dbParam_au_pr.ParameterName = "@assigned";
        if (assigned == null)
        {
            dbParam_au_pr.Value = null;
        }
        else
        {
            dbParam_au_pr.Value = assigned;
        }
        dbParam_au_pr.DbType = System.Data.DbType.String ;
        dbCommand.Parameters.Add(dbParam_au_pr);

I also tried using "" instead of null, or not using that "if" statement at all.
I get an error that says:

Parameterized Query '(@id nvarchar(1),@assigned
nvarchar(4000))UPDATE  [tracker_Probl' expects parameter @assigned,
which was not supplied.
Please help with this as soon as possible.Thanks

View 1 Replies View Related

Stored Procedure With Bit Parameter As Null

Dec 16, 2013

I'm trying to write a stored procedure for a search criteria. A user will have few controls on ASP.Net webpage to enter/select the search criteria, based on which , it returns the data.

A Textbox( to enter Username), A Textbox (to enter Employee name),
A DropDownList (To select the profile. Its Id is sent as parameter) &
A DropDownList (To select status). This is where I am stuck.

The last dropdown has 3 list items
1. --All-- value is null/empty
2. Active value is 0
3. Blocked value is 1

Though the Database table will have only 0's and 1's for the status column, the user should be able to see all records when -all-- is selected.I also tried excluding the status parameter and its condition, but no luck either way.

View 3 Replies View Related

Set Input Parameter To Zero If Null Or Blank

Jan 8, 2015

I am passing few parameters to the sql function to do some calculations. If the input parameter is null or blank, then I want to set the input parameter to the value zero

View 1 Replies View Related

Null Label In Parameter Area

Mar 8, 2007

We have some reports that allow null values for parameters and I find the "Null" label next the parameters in the report viewer to be completly the wrong thing i want to show to business users.

Has anyone worked out a way to change this label to something more meaningful?

aaron

View 4 Replies View Related

I Can't Get A SQL QUery To Accept A Null For A Parameter

Mar 1, 2007

I have straight forward Insert Query, which takes values mainly from text boxes, however I am having trouble when the value is null.

eg
cmdP0.Parameters["@EmpID"].Value=txtEmpNo.Text;

When I run the Query I get...
Message="Parameterized Query '(@ID int,@EmpID int,@Photo nvarchar(260),@DoB smalldatetime,@Med' expects parameter @EmpID, which was not supplied."

I also get this when the parameter can be a string.
I have set the parameter properties so that SourceColumnNullMaping to true (it was set as false - so hoped this might fix it!)

No luck.

View 5 Replies View Related

Hide Column If Parameter Is Null

Feb 19, 2008


I have a parameter in my report, and if it is set to Null or Zero, I want to hide a column. How would I do this? I have already tried using


Code Snippet=IIF(Parameters!Rate.Value >= 0, false, true)

in the Visibility property for the relevant column, but it doesn't make any difference. The column still shows up even if the Rate parameter is set to Null or zero. I only want the column to show if the user enters a value in the Rate parameter which is greater than zero.

=

View 4 Replies View Related

How To Handle A Null Guid As Parameter In Sql 2005

Jul 5, 2007

I have a web application. In some instances, I have a need to send guid parameter as null while making a sql query to SQL 2005. My question is as how to handle this null guid on .net and sql side.
Thanks! in advance.

View 2 Replies View Related

How To Ignor Filtering Criteria If There Is A Null Value For Parameter

May 15, 2008

I have gridview and i want to ignor that filtering if that parameter is empty how do i write that query.
my query is
select * from UserMAster where UserId=@USerID and RoleId=@RoleId
if @UserId is blank then i want to select all records without filtering with user Id a
nd if i have passed null or anything as parameter in roleId then alsoi want all the records if it contain value then i want to do filetring is it possible to with query. or else send me some alternative so that i don't need to execute more query.
 

View 7 Replies View Related

HOw To Pass The Null Value To The Parameter Of The Stored Procedure

May 11, 2005

sSQL = "spBPT_Fuel_Set_Status_Approved"
cmdDailyPrices.CommandText = sSQL
cmdDailyPrices.Parameters.Add("@user", "Philippe")
cmdDailyPrices.Parameters.Add("@verbose", "0")
cmdDailyPrices.Parameters.Add("@Day_1_add", rowBand1.Cells(DayParameters.AddFactor).Value)
cmdDailyPrices.Parameters.Add("@Day_1_multiply", rowBand1.Cells(DayParameters.MultiplyFactor).Value)
cmdDailyPrices.Parameters.Add("@Day_2_add", "NULL")
cmdDailyPrices.Parameters.Add("@Day_2_multiply", "NULL")
For @Day_2_add and @Day_2_multiply parameters I want to pass the value as NULL not string "NULL"
could you please let me know how to do this?
 
Thanks

View 1 Replies View Related

Executing Stored Procedure With Parameter NULL

Jun 25, 2015

ALTER PROCEDURE [dbo].[p_sub_agent_Grp_report]
@parent_pay_agent_cd VARCHAR(25) ,
@tagno NUMERIC(18,0) = NULL,
@labFromCLS VARCHAR(10),
@labToCLS VARCHAR(10),
@status VARCHAR(1)

[Code] .....

1. EXEC [dbo].[p_sub_agent_Grp_report] 'BD0003' , 173, '2015-06-01' , '2015-06-25', 'Y'
2. EXEC [dbo].[p_sub_agent_Grp_report] 'BD0003' , NULL, '2015-06-01' , '2015-06-25', 'Y'

2nd Stored procedure executes successfully and 1st stored does not executes where only difference is 3rd parameter value 173 and NULL value.

Is it because " IF @tagno IS NOT NULL" used in stored procedure is not working while @tagno is sent as 173 in sp parameter. Error throwing while executing 1st stored procedure is as below.

Msg 8115, Level 16, State 6, Procedure p_sub_agent_Grp_report, Line 48
Arithmetic overflow error converting nvarchar to data type numeric.

Msg 8115, Level 16, State 6, Procedure p_sub_agent_Grp_report, Line 48
Arithmetic overflow error converting nvarchar to data type numeric.

Note: sub_agent_tag_no column of table Sub_agent_tag_dtl has datatype Numeric(18,0).

View 2 Replies View Related

Returning Values When Null Parameter Selected

Apr 3, 2008

i have a stored procedure that has parameters. this dataset is used in a crystal report. the parameters are in the sp and will return records if a value is selected. i would like to return records if one parameter or all parameters are selected.
there are 3 parameters, date range, receipt, po #
if i select a distinct value and leave the others null, or 2 values 1 null etc, i would like to return the records, i am having trouble with the syntax. thank you

View 1 Replies View Related

Case Statement On Where Clause If Parameter =NULL

Jun 3, 2008

I am working on a Function that takes multiple parameters. I have a query that populates a temporary table, and then it processes some logic. My question is, if the parameter is passed as null, I dont want the query to be affected by this null value. Rather, I would like to not pass it at all to the query. So if the parameter is NULL, dont pass it through the query. I have the following but its not compiling right:


SELECT bom.pEngr_BOM_ID , bom.fEngr_Item_ID, det.pEngr_BOM_Detail_ID, 1, bom.Bill_Type, bom.Rev_Ltr, bom.Series_Ltr
FROM dbo.Engr_BOM_Control bom WITH (nolock)
INNER JOIN dbo.Engr_BOM_Detail det WITH (nolock)
ON det.fEngr_BOM_ID=bom.pEngr_BOM_ID
WHERE bom.pEngr_BOM_ID=@v_pEngr_BOM_ID
AND det.fEngr_BOM_ID=@v_pEngr_BOM_ID
CASE WHEN @v_Bill_Type IS NOT NULL THEN
AND bom.Bill_Type=@v_Bill_Type
END

View 3 Replies View Related

Optional Parameter On Joined Field Which Could Be Null?!

Jul 20, 2005

I have two tables: eg. a person-table (no nulls allowed), with an idand so on, and a person_course table (an intermediate table in amany-to many relationship between person table and courses tables),with two fields person_id and course_id.But I want to make ONE multipurpose stored procedure, which has ONLYoptional parameters on all fields in the person table AND the fieldcourse_id in the person_course table.I have no problems making optional parameters on the person table (eg.P.ID=ISNULL(@PersonID, P.ID ) ) BUT the problem is, when I try to addan optional parameter on the field course_id it dosn't produce theright results. Some times the course_id is null (because some personshavn't joined a class yet).Is there a way around it?Ole S. Pedersen

View 1 Replies View Related

Parameter Value From Empty Text Box - Null Or 0 Length?

Jul 20, 2005

Hi,I run a stored procedure with a parameter given from a text box in an accessadp. If the text box is empty then what is passed to the parameter? I can'tseem to get it to flag up as either null, or 0 length.Any tips?Cheers,Chris

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved