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 id
and so on, and a person_course table (an intermediate table in a
many-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 ONLY
optional parameters on all fields in the person table AND the field
course_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 add
an optional parameter on the field course_id it dosn't produce the
right results. Some times the course_id is null (because some persons
havn't joined a class yet).
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 new to SQL Server, so if I'm doing anything stupid don't bemean :)I have a procedure that I use to return data based on optionalparameters. It works fine, except when the underlying data contains anull on one if the fields being searched.My system uses a default wildcard for all parameters, so this excludessuch records. I need a way to add in " OR fldName IS NULL " where theparameter is empty or '%'. I've looked at using CASE WHEN, but itdoesnt seem to like SQL Keywords being part of the WHEN clause.I'd hate to have to resort to executing concatonated strings made fromIF and ELSE statements. Just too messy and not at all pretty!Any Ideas? Here's what I've got:ALTER PROCEDURE [dbo].[procFindUnits]@strUnitIDnvarchar = '%',@strProjectNamenvarchar = '%',@strAddressnvarchar = '%',@strTenancynvarchar = '%',@strTenurenvarchar = '%'ASBEGINSET NOCOUNT ON;SELECTtblUnits.strUnitID,tblProjects.strProjectName,qryAddresses.Address_OneLine,lkpTenancyTypes.strTenancyType,lkpTenureTypes.strTenureTypeFROM tblUnits INNER JOINtblProjects ON tblUnits.intProjectID = tblProjects.intProjectIDLEFT OUTER JOINlkpTenancyTypes ON tblUnits.intTenancyType =lkpTenancyTypes.intTenancyType LEFT OUTER JOINlkpTenureTypes ON tblUnits.intTenureType =lkpTenureTypes.intTenureTypeID LEFT OUTER JOINqryAddresses ON tblUnits.strUnitID = qryAddresses.strUnitIDWHERE(tblUnits.strUnitID LIKE @strUnitID)AND (tblProjects.strProjectName LIKE @strProjectName)AND (qryAddresses.Address_OneLine LIKE @strAddress)AND (lkpTenancyTypes.strTenancyType LIKE @strTenancy)AND (lkpTenureTypes.strTenureType LIKE @strTenure)END
I joined different tables and got a result like this:
result | process | goal | date | ------- ---------- ------ ----------- ok | process4 | 1 | 12.10.2013 bad | process1 | 2 | 13.10.2013 ok | process1 | 4 | 12.12.2013 good | process4 | 1 | 03.01.2014 ok | process1 | 3 | 10.04.2013 bad | process3 | 6 | 09.01.2014 bad | process4 | 3 | 30.12.2013 best |NULL| NULL
Now I want to count the results by counting the processes and group them by the result.
But it should be count the latest result per process only, e.g. for goal "1" just "good" at 03.01.2014. I solved that with a subquery (date=SELECT MAX(...)..).
But now the result "best" disappears, because that column has no date.
Secondly I want to count results for a specific process, e.g. for process4. Every goal has max. one process, with different dates. But one process could have more than one goal.
I want to have this result for process4:
count | result ------ ------- 1 | good 1 | bad 0 | ok 0 | best
But I got only:
count | result ------ ------- 1 | good 1 | bad
I have tried a lot, but nothing works.
The whole result (best, good, ok, bad) are stored in an other table and I joined it.
Hello, Is it possible to define optional parameters for a stored procedure? What I want is to create a search with about 8 parameters, but most users will only use 3 or 4, so It would be nice If I could only provide the used parameters in my code. And have sql give the unused parameters a default value (possibly %) thx.
I need to use a parameter in a stored procedure that is optional and if it is optional I need all records returned, even if there is an entry in the field that the parameter is appllied to. Is this possible? I know I can assign a default value. but the value in the field could be one of many choices. I want to be able to specify a choice and have only those records returned or leave it blank and return all records.
Hi All,I have a stored proc which looks like this.Create ....(@id int,@ud int,@td int=0)if @td=0select bkah from asdf where id=@id and ud=@udelseselect bkah from asdf where id=@id and ud=@ud and td=@td---------------------------------I am wondering if i could replace if condition with the following lineselect bkah from asdf where id=@id and ud=@udand ( @td<>0 and td>@td )IS sql server 2000 smart enough not to use the td>@td in the query if@td is 0Thanks all
hi all, I am using SQL reporting services 2005, i like to have a parameter as optional, is there is any diresct way to set a parameter as optional without setting through the default value.
Is it possible to have the field as a unique key and a optional one?It is like.. for example, office code has to be unique (cannot beduplicated with the same code) and it could be null too.
I'm managing an amature online university and I've been charged with creating a deans list. I have a table for exam results for each course.. currently totaling 5. I have an employeeID column and a total_points column in each table. Sooooo I need to join all the tables and get an average for total_points where the employeeID matches across tables. I have no idea how to write this select.. any help?
Hi, I'm having some issues with the SqlDataSource. I want to use it to populate a GridView, but using an optional parameter to filter the results.
This is what I have right now (hopefully haven't made any typos - can't copy/paste):
<asp:SqlDataSource ID="test1" runat="server" SelectCommand="SELECT * FROM SomeTable WHERE (@MyParam IS NULL OR MyColumn = @MyParam) ORDER BY SomeColumn" ConnectionString="<% ConnectionStrings:MyConnString %>" > <SelectParameters>
When I test the SQL query in the query designer it works (returns only rows having the value passed as a parameter when one is specified, otherwise it returns all rows), so it seems like that part is OK. The "All" (as in "return all"/no filtering) entry in the DropDownList has a value of a zero lenght string, and the ControlParameter has the convert empty string to null to true (and the default value is the same), so it should get converted to a null when "All" is selected, hence returning all rows. But it doesn't work. It works fine for all the entries with text, but the zero lenght string somehow doesn't work - I get no results at all instead of it returning all rows (but the query itself worked fine when I tested it).
What am I missing? I just can't find what I'm doing wrong. Any ideas/hints? (I also need to do the same with an ObjectDataSource, so hopefully I can get this to work!) I can't think of an easy way to find out if the zero lenght string gets converted to a null or not (I've even tried adding OR @MyParam = '' to the query and it still didn't work....) Right now I'm stuck....
(Also posted this question on microsoft.public.dotnet.framework.adonet newsgroup)
I want the procedure to check for the existence of a paramter and if it isthere, it will process these instructions, otherwise it will process theseinstructions. Any ideas? Thanks for your advice.Regards,CK
I want to use an optional parameter at the command line for my package.
I planned to make the first task a script which simply checks the variable (which is a string), and if it is empty, go one way, and if it is not, go another way. Is the best to go?
I have a query with 17 separate, optional, parameters. I have declared each parameter = NULL so that I can test for NULL in the case that the user didn€™t not pass in the parameter.
I am new enough to SQL Server that I am having difficulty building the WHERE clause with all of these optional parameters.
One solution I was advised on by a well paid SQL programmer, was to use a string in the stored proc and dynamically build the WHERE clause and exec it at the end of the sp. But the whole point of a stored proc is that it can be compiled and cached to make it faster, yet the string approach makes it have to compile every time it€™s run! Not a good solution, but maybe it€™s the best I can do . . .
I have tried many different approaches using different functions, etc. but I€™ve hit a brick wall. Any help in sorting it out with YOUR techniques would be greatly appreciated:
1. To add the parameter to the WHERE clause and test for NULL I€™ve used the COALESCE function such as €œWHERE table.fieldname = COALESCE(@Param, table.fieldname)€?. This works well if there is only one item in the parameter, but in the case that I pass multiple items to the parameter, it completely fails.
2. To handle multiple items, for example, if @Param = €˜3,7,98€™ (essentially, a csv separated list of keys)
doesn€™t work because @Param needs to be parsed from a string into an array of integers in the parameter. So, I am using a UDF I discovered to parse the multi-item parameter. The UDF can be found at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag01/html/TreatYourself.asp and it returns a table variable that can be used in an IN statement. So I€™m using
Code SnippetISNULL(table.fieldname, 0) IN (SELECT value FROM dbo.fn_Split(@Param,€™,€™))
which works brilliantly in my WHERE statement AS LONG AS @Param ISN€™T NULL. So how do I test for NULL first and still use this approach to multi-item parameters?
I€™ve tried
Code SnippetWHERE @Param IS NULL OR ISNULL(table.fieldname, 0) IN (SELECT value FROM dbo.fn_Split(@Param,€™,€™))
and though it works, the OR causes it to slow way down as it compares every record for the OR. (It slows down by approximately 800%.) The other thing I tried was
Code SnippetISNULL (table.fieldname, 0) IN (CASE WHEN @Param IS NULL THEN ISNULL(table.fieldname, 0) ELSE (SELECT value FROM dbo.fn_Split(@Param,€™,€™)))
This fails with €œSubquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression€? due to the multiple values in the parameter. (I can€™t understand why the line without the CASE statement works, but the CASE line doesn€™t!)
Am I even on the right track, cuz this is driving me mad and I just need a way to deal with optional multi-item parameters in an IN statement? HELP!
my problem is that i have a integer report parameter that must be multivalued. The parameter is populated by query. The thing is that in the beginning, there is no data in the dataset of the specific parameter. The table which is source to the dataset will br populated after some time from an XML.
Reporting services prompts the user to select a value for the parameter. But there is no value to select, yet. I cannot have leave blank because it is a string and not an int and i cannot have null because the parameter is multivalued. Any suggestions?
I have a script that is supposed to run thru 2 joined tables and update a field in the 3rd table. The script works but takes approx. 4 hours to run against 250k records.
UPDATE a SET Con_Mailings = STUFF((SELECT '; ' + c.ListName FROM [server].[xxxxx_MSCRM].[dbo].ListBase c with (nowait) INNER JOIN [server].[xxxxxx_MSCRM].[dbo].[ListMemberBase] b with (nowait) ON b.ListID = c.ListID WHERE b.EntityID = a.TmpContactID FOR XML PATH('')),1,1,'') FROM [xx_Temp].[dbo].[Lyris_CombinedTest] a
I should end up with something like this in the con_mailings field:
In my report I have two parameters PID and patientName, but user want to enter either PID or patientName. When I preview the report user can only enter one parameter not both. Is there any way  to create two parameter available but users can enter only one parameter value?Here is my report layout and query I used for that report. These two parameters comes from one column 'String_NVC' as from query and column name 'Description' as from the report layout.
SELECT Username_NVC AS Username, String_NVC, Time_DT AS UserActionDateandTime, FROM test
I have several reports for which the user has asked to have an optional muti-value parameter. They want to be able to select zero, one, many, or all values in the parameter list. The parm list is created through a query and the values are not static.
I would like to allow the user to leave the muti-value field empty if they want to allow all values to appear on the report. I've read some discussion about populating a multi-value default with the same query that produces the multi-value list values - presto, everything is selected. However, this is not a desirable solution for me because I "echo" the users parameter selections in the report heading. Selecting all values (and some parms have a lot of values) would cause the "parm feedback" section to grow large and unreadable.
In short, I don't want to tell the user they have to select everything when they really want to select nothing.
Is there any way to have a muti-value parm that won't insist the user select one or more values?
I have a ssrs report with Name, phone ,state and city as columns. I have check box as one of my parameter(optional). If user checks that checkbox then it should group by state, if checkbox is left blank no need to do any grouping. How can i do this in ssrs 2012.
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 need to pass in null/blank value in the date field or declare the field as string and convert date back to string.
I tried the 2nd option but I am having trouble converting the two digits of the recordset (rs_get_msp_info(2), 1, 2))) into a four digit yr. But it will only the yr in two digits. The mfg_start_date is delcared as a string variable
option 1 I will have to declare the mfg_start_date as date but I need to send in a blank value for this variable in the stored procedure. It won't accept a null or blank value.
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)
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 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)
I have a table, named systems, with fields as follows.
field datatype ___________________ system varchar(50) modelID int .... .... ... .... etc...
The modelID field is related to the Models table. Therefore, the value it expects is a number that already exists in a Models.modelID field.
In the systems table, I allow nulls for the model ID field. If I edit the table directly through SQL front end it allows me to enter a system name for example and leave modelID value as NULL.
My problem occurs when I try inserting into the table from ASP. Whether I leave the value I'm passing to the stored procedure for insert blank or NULL it gives me problems.
Is this a typical issue that can be resolved with a simple property change?
Please help me resolve this, I need to have the ability to leave certain fields empty even though they are related to other tables. Is that possible?
