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.
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.
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
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.
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.
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)
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
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)
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)
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
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) { }
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 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
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).
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?
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)"
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?
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
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.
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
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?
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!)
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.
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.
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.
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?
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).
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
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
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
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
