Ignore NULL Argument In SELECT

Aug 14, 2007

I have a stored procedure which accepts 3 arguments that are used in the WHERE clause of a SELECT Statement

CREATE PROCEDURE [dbo].[MyProcedure] @argYear INT, @argMonth INT, @argDay INT

AS

SELECT *

FROM MyData

WHERE
MyData.Year = @argYear AND MyData.Month = @argMonth AND MyData.Day = @argDay


The problem that I am having is @argDay is an "optional" argument. If @argDay is NULL then I want to basically ignore the "AND MyData.Day = @argDay" condition.

Is there an easier way to do this than:

IF @argDay is NULL

SELECT *


FROM MyData


WHERE

MyData.Year = @argYear AND MyData.Month = @argMonthELSE

SELECT *


FROM MyData


WHERE

MyData.Year = @argYear AND MyData.Month = @argMonth AND MyData.Day = @argDay

END IF

Thanks

View 7 Replies


ADVERTISEMENT

'column' Argument Cannot Be Null.

Apr 14, 2008

Hi i get the above error whenever i try and run my page, what i am trying to do is embed a repeater within a datalist, here is my code;public void Page_Load(object sender, EventArgs e)
{string strID = Request.QueryString["id"];
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["streamConnectionString"].ConnectionString);SqlCommand command = new SqlCommand("stream_Users", conn);
command.CommandType = CommandType.StoredProcedure;command.Parameters.Add("@userID", SqlDbType.Int).Value = Request.QueryString["id"];
SqlDataAdapter cmd1 = new SqlDataAdapter(command);
//Create and fill the DataSet.DataSet ds = new DataSet();
cmd1.Fill(ds, "userName");
//Create a second DataAdapter for the Titles table.SqlDataAdapter cmd2 = new SqlDataAdapter("select * from UserSpecialties", conn);
cmd2.Fill(ds, "specialty");
//Create the relation bewtween the Authors and Titles tables.ds.Relations.Add("myrelation",
ds.Tables["userName"].Columns["userID"],ds.Tables["specialtyName"].Columns["userID"]);
//Bind the Authors table to the parent Repeater control, and call DataBind.DataList1.DataSource = ds.Tables["userName"];
Page.DataBind();
//Close the connection.
conn.Close();
}

View 2 Replies View Related

Analysis :: Column Argument Cannot Be Null Parameter Name

Jul 22, 2015

All I did was change the names on two measures in a measure group, tried to refresh the DSV and ka-blooey!

'column' argument cannot be null parameter name:column

View 3 Replies View Related

Ignore Null Fields

Sep 29, 2005

Hi,

I have a query like this one

SELECT expense_id, CAST(expense_id AS char(10)) + ' - ' + CAST(trip_km AS char(5))+ ' - ' + CAST(expense_amount AS char(5)) + ' - ' + charge_centre AS ExpenseDesc

If charge center is null, I need to ignore this field. How can I achieve this? The reason is that if any of the field is null, it will return ExpenseDesc as null.

Thanks

View 1 Replies View Related

Getting Avg To Really Ignore Null Values

Sep 2, 2005

Using SQL2000. According to Books Online, the avg aggregrate functionignores null values. ((3+3+3+3+Null)/5) predictably returns Null. Isthere a function to ignore the Null entry, adjust the divisor, andreturn a value of 3? For example:((3+3+3+3)/4) after ignoring Nullentry.If there's more than one null value, then adjust divisor accordingly.For example: ((5+5+5+4+Null+5+5+Null)/8) would be ((5+5+5+4+5+5)/6)after nulls ignored.Thanks for any help or advice.

View 8 Replies View Related

How To Ignore Null Value With Dateadd

Oct 4, 2007

Salamo alikom,

when i write this sentence in textbox in reporting service 2005 :
= iif(DateAdd( "h",Parameters! t.Value,Fields! completionTime. Value),"" ,Fields!completi onTime.Value)

Because this column contain null value.

So how to ignore null value with dateadd

AnyBody Can Help me.

View 3 Replies View Related

Ignore Column If Result Set Is Null

Jul 12, 2013

Is there a way to ignore a column/variable when the whole result set for the applied filter (where) is null?

Not all clients have data for every variable, i.e. some variables are client specific. There are too many variables and clients to amend the select query every time so I just want to ignore a col if its null.

I hope that makes sense (my inability to describe it might explain my inability to find anything related to it!)

The next step would be to run all clients' data in one go using, I believe, a cursor, but one step at a time!

View 7 Replies View Related

Ignore NULL StoredProcedure Parameters

Mar 18, 2008

Hi,
I do have a stored procedure with parameters inside. I just want to ignore those parameters that are NULL so that my WHERE clause will not execute them anymore. Here's my code:

CREATE PROCEDURE SEARCHPATIENT
@patnCode varchar(10) = NULL,
@patnSurname varchar(20) = NULL,
@patnGivenName1 varchar(20) = NULL
AS
SELECT....
FROM ...
WHERE patnCode=@patncode AND
patnSurname =@patnSurname AND
patnGivenName1 =@patnGivenName1

BUT...
If the user passes @patnSurname as NULL, I got an SQL error message:
"There was an error executing the query.. Timeout expired... ". I was hoping I could write an WHERE clause where IT WILL ONLY EXECUTE those parameters WITH VALUE and IGNORE those NULLs.

Can someone help me on this?
Can I use IF THEN ELSE statement inside the WHERE clause?
Can I use CASE STATEMENT inside the WHERE clause?

Thanks in advance.

Joseph

View 3 Replies View Related

Argument Data Type Varchar Is Invalid For Argument 3 Of Convert Function

Jan 25, 2013

Where did i do wrong in conversion

original query
dateadd(hour, datediff(hour,CONVERT(VARCHAR(19),B.CreateDate,111 ),B.CreateDate)

I tried to use convert(varchar(50),Datediff,21)

Below is the exact code..

convert(varchar(50),dateadd(hour, datediff(hour,CONVERT(VARCHAR(19),B.CreateDate,111 ),B.CreateDate),21)

View 10 Replies View Related

Argument Data Type Text Is Invalid For Argument 1 Of Replace Function.

May 14, 2008



Hi There,

Could someone please tell me why I am getting the above error on this code:

select (replace
(replace
(replace
(replace (serviceType, 'null', ' ')
, '<values><value>', ' ')
, '</value><value>', ',')
, '</value></values>', ' '))
from credit


serviceType (text,null)

Thanks,
Rhonda

View 1 Replies View Related

Sqldatasource1.Select([Argument?])

Oct 8, 2007

Hi, nice to meet you all. I need to return a parameter value from a store procedure, i already done insert(), update(), delete() to return parameter back. Just only select() can't return parameter and it need argument, I try to write in Sqldatasource1.Select(Argument.empty), it can't work and return null. How should i solve the problem? Or I wirte the wrong argument? Can anyone help me? Thank in advance.

View 5 Replies View Related

SQL Server 2012 :: Stored Procedure Argument For Select Where Value IN Statement

Feb 17, 2014

I have a stored procedure that ends with

Select columnname from tablename order by ordercolumn

We will call that "sp_foldersOfFile". It takes 1 parameter, a fileID (int) value.

The result when I execute this from within Management Studio is a single column of 1 to n rows. I want to use these values in another stored procedure like this:

Select @userCount = COUNT(*) from permissions where UserID = @userID and
(projectid = @projectID or projectid=0) and
clientid = @clientID and
folderpermissions in (dbo.sp_FoldersOfFile(@fileID))

The Stored Procedure compiles but it does not query the folderpermissions in the selected values from the sp_FoldersOfFile procedure. I'm sure it is a syntax issue.

View 9 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

Argument Not Specified For Parameter 'arguments' Of Public Function Select(arguments As System.Web.DatasourceSelect Arguments As Collections Ienumerable

Mar 25, 2007

I have an SqlDataSource control on my aspx page, this is connected to database by a built in procedure that returns a string dependent upon and ID passed in.
 I have the followinbg codewhich is not complet, I woiuld appriciate any help to produce the correct code for the code file
 Function GetCategoryName(ByVal ID As Integer) As String          sdsCategoriesByID.SelectParameters("ID").Direction = Data.ParameterDirection.Input          sdsCategoriesByID.SelectParameters.Item("ID").DefaultValue = 3          sdsCategoriesByID.Select() <<<< THIS LINE COMES UP WITH ERROR 1End Function
ERROR AS FOLLOWS
argument not specified for parameter 'arguments' of public function Select(arguments as System.Web.DatasourceSelect Arguments as Collections ienumerable
 
Help I have not got much more hair to loose
Thanks Steve

View 5 Replies View Related

NULL Values In A SELECT In Another SELECT

Jan 23, 2008

Hi,I have a query like this :SELECTx1,x2,( SELECT ... FROM ... WHERE ...UNIONSELECT ... FROM ... WHERE ...) as x3FROM ...WHERE ...The problem is that I don't want to return the results where x3 isNULL.Writing :SELECTx1,x2,( SELECT ... FROM ... WHERE ...UNIONSELECT ... FROM ... WHERE ...) as x3FROM ...WHERE ... AND x3 IS NOT NULLdoesn't work.The only solution I found is to write :SELECT * FROM((SELECTx1,x2,( SELECT ... FROM ... WHERE ...UNIONSELECT ... FROM ... WHERE ...) as x3FROM ...WHERE ...) AS R1)WHERE R1.x3 IS NOT NULLIs there a better solution? Can I use an EXISTS clause somewhere totest if x3 is null without having to have a 3rd SELECT statement?There's probably a very simple solution to do this, but I didn't findit.Thanks

View 7 Replies View Related

Select ALL If Var Is NULL

Nov 21, 2013

How would amend the following so that if one or both variables were NULL the result would default of ALL (everything)for that variable

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[InLnFncSchool]
(
@School nvarchar(100) =NULL,

[Code] ....

View 8 Replies View Related

Select Null

Apr 17, 2008



What does it mean "select null"?
Is null meant nothing?

Thanks.



Code Snippet
select null
from(
select e.empno,e.ename,e.job,e.mgr,e.hiredate,
e.sal,e.comm,e.deptno,count(*) as cnt
from emp e

View 4 Replies View Related

Null To Zero In Select Statement?

Mar 11, 2004

How can I do a null to 0 in a select statement? I tried the NZ() function but it is not part of SQL.

Thanks very much,

View 5 Replies View Related

Select Sum() Returns Null

Jan 21, 2004

hi,

i have the following query to sum the total due balance for a customer:

select sum(outstanding)from out where customer = 'myvariable' the problem is when the customer has no outstanding it returns NULL is there a way to return 0 when there are no outstanding?

thanks

View 7 Replies View Related

BCP Help -Null Values In Select

Jan 26, 2004

I am using a bcp command to cretae a text file.

set @bcpcommand = 'bcp "select med + replicate('' '',10-datalength(med)),ml+ replicate('' '',20-datalength(ml)),iname + replicate('' '',10-datalength(iname)) from copy_tbl" queryout "'+ @filename + '" -U -P -c'

exec master..xp_cmdshell @bcpCommand

Some of the values in the select statement are Null values and are getting skipped in the text file .My output looks like
At A02 At1E
AtE A03 At2E
c100 c1230
I want them to allign but the third row has a null in the middle so it skips it and put the third value in the seconds place

View 2 Replies View Related

Select When Some Fields Are Null...

Jun 5, 2008

Here is the problem that I am struggling with. The structure is:

States: StateID, StateName
Counties: CountyID, CountyName
Cities: CityID, CityName
Zips: Zip, StateID, CountyID, CityID
Regions: RegionID, RegionName
Region_Data: RegionID, StateID, CountyID, CityID

The thing about the region is that it can be defined by states only, in which case CountyID and CityId are NULL, can be defined by Counties too, in which case only CityID is NULL or it can be defined up to City level, in which case all 3 are set to something. Example, Northeast would be all cities from northeast, but Pocono would be just some counties in PA, and so on...

The issue is now selecting all cities that belong into a region... Normally I would join the Zips table with the Region_Data table and retrieve all CityIDs... The issue is that, as I said, the CountyID and CityID might be null for some records, so I am not sure how to retrieve them?

I came up with one idea in which I create 3 temporary tables and I select in them all the records that have 1) only state, 2) only state and county, 3) all 3, then I join each and I union the results...

But I am wondering if there is a way to do the select in one shot?

Any idea? I appreciate your help,

Thank you,

Iulian



Regards,

Iulian

View 3 Replies View Related

How To Select Not Null Values

Oct 4, 2006

hi, i have 5 columns in my table

name age phone sex salary

dani 21 1233 m

see this output, there salary is null.
actually i want to use * (ie)
select * from emp, if i use this the following output has to come

name age phone sex

dani 21 1233 m

becos there is no value in salary column.
so please tall me how to do this,please give me query to do this.

View 5 Replies View Related

Help! Depressing Null In A Select...

Dec 18, 2007

I have the following problem:

Source Table






ArtNr
F1
F2
F3

1044
Eigenmarke
NULL
NULL

1044
NULL
Altmarken
NULL

1044
NULL
NULL
Blanko

Desired output after SQL select:






ArtNr
F1
F2
F3

1044
Eigenmarke
Altmarken
Blanko

How can I achieve a easy solution?

View 17 Replies View Related

NULL As Defaultvalue In Select Command

Feb 13, 2007

Hello everybody,
I have following datasource
  <asp:SqlDataSource ID="sqlIncidentTemplates" runat="server" ConnectionString="<%$ ConnectionStrings:Standard %>"
SelectCommand="SELECT [IncidentText] FROM [IncidentTemplates] WHERE [ConstrAreaID] = @ConstrAreaID AND [ConstrDeviceID] = @ConstrDeviceID">
<SelectParameters>
<asp:Parameter DefaultValue="" Name="ConstrAreaID" Type="Int32" ConvertEmptyStringToNull="true" />
<asp:Parameter DefaultValue="" Name="ConstrDeviceID" Type="int32" ConvertEmptyStringToNull="true" />
</SelectParameters>
 As far as I understand default select command should be "SELECT IncidentText FROM IncidentTemplates WHERE ConstrAreaID IS NULL AND ConstrDeviceID IS NULL". Unfortunately this is not the case. I assume that the "[ConstrAreaID] = @ConstrAreaID " is translated into "ConstrAreaID = NULL".
Could this be the reason and how can I solve this problem?
Thanks in advance

View 3 Replies View Related

Select Statement When Values Are Not Null

Sep 4, 2007

Hi.
I have an sql table which contains a number and a name. I would like to create a select statement that will display these two fields in the format :
"number | name", but if there is a null value in the number it will display only the name and vice versa.
How can I do it ?
Any help is appreciated.

View 1 Replies View Related

T-SQL (SS2K8) :: Select One Of Three Values That Are Not NULL?

May 6, 2014

I am working on some data that is JOINing to another table. Not a big thing. In the child table, there are different values for a single ID. I want to be able to select the Max ColorID that is Not Null, for each distinct CarID. The CarID is what I am joining the other table with. I need selecting the distinct row with the Max ColorID that is not Null. All this data is made up, so nothing looks logical in the design.

DECLARE @ColorList TABLE
(
CarID float
, ColorID int
)
INSERT INTO @ColorList
SELECT 1.55948815793043E+15, 9 UNION ALL
SELECT 1.55948815793043E+15, 27 UNION ALL

[code]....

So this would be the resultset:

1.55948815793043E+15, 27
1.62851796905743E+15, 27
1.51964586107807E+15, 9
1.55948815793043E+15, 27
1.47514023011517E+15, 5
1.64967408641916E+15, 27
1.51964586107807E+15, 9
1.56103326128036E+15, 27
1.49856249351719E+15, 9
1.5736407022847E+15, 6
1.64664602022073E+15, 27
1.51964244007807E+15, 27

View 3 Replies View Related

T-SQL (SS2K8) :: Select If Null Passed Else By INT

May 14, 2014

I'd like to be able to offer the option of selecting a project by the ProjectID number, and if a projectID is not supplied then the default result set would be select * from tbl_Projects.

ALTER PROCEDURE [USP_SelectProject]
-- Add the parameters for the stored procedure here
@ProjectNumber as int
AS
BEGIN

[code]....

View 9 Replies View Related

Null Value In Column In Select Query

Mar 18, 2015

Suppose I have added a xyz bit column in mytable. From now onwards new values inserted in mytable will have 0 or 1 in xyz column but the values that were previously stored will have NULL value in xyz column.

Now I want to write a query in which I will not give xyz parameter always like it can be null or value.

select * from mytable
where class='something'
and xyz is null

or

select * from mytable
where class='something'
and xyz = 1

Now how to write same query for both cases.

View 9 Replies View Related

Null Testing In Select Query

May 9, 2007

I am obviously a newbie to SQL Server but have been using Access for years and I have come up against what appears to be an age old issue concerning nulls and specifically the functions to test for them etc.

Below is the Access Query I am having difficulty with...
----------------------------------------------------------
SELECT DISTINCT Max(tbl_ScheduleDate.SchDate) AS CurrentDate, ([SchDate],"mmmm yyyy") AS MonthYear, tbl_Schedule.Area, tbl_Schedule.CP, IIf(IsNull(tbl_tmp_CP.CPID),"Not Uploaded","Uploaded") AS Loaded

FROM (tbl_Schedule INNER JOIN tbl_ScheduleDate ON tbl_Schedule.MaxMinID = tbl_ScheduleDate.MaxMinID) LEFT JOIN tbl_tmp_CP ON tbl_Schedule.CP = tbl_tmp_CP.CP

GROUP BY Format([SchDate],"mmmm yyyy"), tbl_Schedule.Area, tbl_Schedule.CP, IIf(IsNull(tbl_tmp_CP.CPID),"Not Uploaded","Uploaded"), tbl_ScheduleDate.SchDate, tbl_tmp_CP.CPID;
-------------------------------------------------------------

Ignore the Format() function as I know this is a problem and have a fix.

The problem is this field...
IIf(IsNull(tbl_tmp_CP.CPID),"Not Uploaded","Uploaded") AS Loaded

I am testing to see if data is loaded into table 'tbl_tmp_CP' that is related to 'tbl_Schedule' by checking if there is a null value or not and then adding the right response string value to a column called Loaded.

Can anyone suggest a way for me to do the above?

View 2 Replies View Related

Why Is My Select...Case Generate Only NULL Value?

Nov 9, 2007



Here's what I have and it's not working.



Code Block
SELECT [userID],
CASE
WHEN [mdlName] = NULL AND [mdnName] = NULL THEN [lstName] + ', ' + [fstName]
WHEN [mdlName] <> NULL AND [mdnName] <> NULL THEN [lstName] + ', ' + [fstName] + ' ' + [mdlName] + ' ' + [mdnName]
WHEN [mdlName] <> NULL AND [mdnName] = NULL THEN [lstName] + ', ' + [fstName] + ' ' + [mdlName]
WHEN [mdnName] <> NULL AND [mdlName] = NULL THEN [lstName] + ', ' + [fstName] + ' ' + [mdnName]
END AS [Full Name]
FROM [grUser]
ORDER BY [Full Name] ASC





The [Full Name] is always null. Any idea why? What I'm trying to do is if mdlName (middle name) and mdnName (maiden name) is null then display the lstName (last name) and fstName (first name) only and so on.

Any help is much appreciated.

View 4 Replies View Related

How To Select NULL Values For My Report

Apr 23, 2008

Hi All,

I use a parameter for product type in my report so that end user can select different product types from a drop down menu. Now my problem is that for some fields product type is blank(NULL). But end user can not see these NULL value when he click drop down menu. I can not use Allow Null value option in report paramter , because I have used multi value option.
Is there any other way to select these NULL values?

Thanks

View 1 Replies View Related

Problem With Isnull. Need To Substitute Null If A Var Is Null And Compare It To Null And Return True

Sep 20, 2006

Hey. I need to substitute a value from a table if the input var is null. This is fine if the value coming from table is not null. But, it the table value is also null, it doesn't work. The problem I'm getting is in the isnull line which is in Dark green color because @inFileVersion is set to null explicitly and when the isnull function evaluates, value returned from DR.FileVersion is also null which is correct. I want the null=null to return true which is why i set ansi_nulls off. But it doesn't return anything. And the select statement should return something but in my case it returns null. If I comment the isnull statements in the where clause, everything works fine. Please tell me what am I doing wrong. Is it possible to do this without setting the ansi_nulls to off??? Thank you

set ansi_nulls off


go

declare

@inFileName VARCHAR (100),

@inFileSize INT,

@Id int,

@inlanguageid INT,

@inFileVersion VARCHAR (100),

@ExeState int

set @inFileName = 'A0006337.EXE'

set @inFileSize = 28796

set @Id= 1

set @inlanguageid =null

set @inFileVersion =NULL

set @ExeState =0

select Dr.StateID from table1 dR

where

DR.[FileName] = @inFileName

AND DR.FileSize =@inFileSize

AND DR.FileVersion = isnull(@inFileVersion,DR.FileVersion)

AND DR.languageid = isnull(@inlanguageid,null)

AND DR.[ID]= @ID

)

go

set ansi_nulls on

View 3 Replies View Related

Sql SELECT Returning NULL On DateTime Field

Aug 8, 2006

Hi there ;
This Problem is goin to make me crazy!
I've got a table with couple of fields, 2 of them are DateTime, in Sql Express 05 .
I used asp.net 2.0 to insert datetime values, directly,  using sth like DateTime.Now.ToString() .
i use 2 selects as follows :
1)select * from X where  Date1='8/9/2006 11:17:41 AM'
2)select * from X where  Date2='8/9/2006 11:17:41 AM'
#1 is OK, but the second one returns a row with all columns set to NULL .
(X is the name of the table)
Thanks in advance

View 6 Replies View Related







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