T-SQL (SS2K8) :: Always Set A Field To NULL

Jan 26, 2015

I would like to force a column to be null all the time. I cannot alter the table structure or alter the code that inserts data.

create table dbo.tblCustomer
(
CID int IDENTITY(1,1) not null,
Fnamevarchar(20) not null,
Lnamevarchar(20) not null,
Extravarchar(20) null

[Code] ....

So when this is executed the field Extra is always NULL

INSERT INTO tblCustomer (Fname, Lname, Extra)
VALUES ('bob', 'smith', 'ignore'), ('jane', 'doe', 'empty')
update dbo.tblCustomer set Extra = 'something'

If I've understood After triggers correctly the data will be written and the trigger will fire and overwrite. To avoid 2 writes

I could create an INSTEAD OF trigger

CREATE TRIGGER TR_I_Customer
ON tblCustomer
INSTEAD OF INSERT AS
BEGIN
SET NOCOUNT ON
INSERT INTO tblCustomer
(Fname, Lname, Extra)
SELECT Fname, Lname, NULL
FROM Inserted
END

This will not write the "extra" field twice. However if a new Nullable column were added; it would be v.easy to forget to update the Instead Of trigger. Everything would still work OK but I would be effectively ignoring the new column as well.

What I would like in the instead of trigger is do something like this...

UPDATE INSERTED SET Extra=NULL

Continue with insert without supplying column /value list.

What would be the best way of achieving this, trigger is the only way I could think of?

View 6 Replies


ADVERTISEMENT

T-SQL (SS2K8) :: Field Has No Value But Is Not NULL Or Empty?

Aug 13, 2015

I added a new field to an existing ETL process which uses SSIS to ingest a CSV file. The new field in the file, Call_Transaction_ID, will not always be populated for every record and so can be NULL or empty for certain records.

Here's the problem:After the file is extracted into a staging table, the Call_Transaction_ID field is showing blank or empty when it has no ID for that particular record. The problem is when I try to then ETL this data into a fact table - I'm trying to set the Call_Transaction_ID field to -1 if it is NULL or empty, however SQL Server doesn't see the field as empty even though there is no value in the field so -1 will NEVER return.

Using a WHERE DATALENGTH(Call_Transaction_ID) = 0 returns 0 records, again because SQL Server doesn't see the field as empty or NULL.

What do I do now to get around this? How do I fix it?

View 5 Replies View Related

Pass In Null/blank Value In The Date Field Or Declare The Field As String And Convert

Dec 30, 2003

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

mfg_start_date = CStr(CDate(Mid(rs_get_msp_info(2), 3, 2) & "/" & Mid(rs_get_msp_info(2), 5, 2) & "/" & Mid(rs_get_msp_info(2), 1, 2)))

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.

With refresh_shipping_sched
.ActiveConnection = CurrentProject.Connection
.CommandText = "spRefresh_shipping_sched"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("ret_val", adInteger, adParamReturnValue)
.Parameters.Append .CreateParameter("@option", adInteger, adParamInput, 4, update_option)
.Parameters.Append .CreateParameter("@mfg_ord_num", adChar, adParamInput, mfg_ord_num_length, "")
.Parameters.Append .CreateParameter("@mfg_start_date", adChar, adParamInput, 10, "")
Set rs_refresh_shipping_sched = .Execute
End

Please help

View 6 Replies View Related

T-SQL (SS2K8) :: Get Last Non Null Value From Column

Apr 14, 2015

I have to populate a table of exchange rates which is easy enough however because rates are held on Fridays but I need to make calculations on weekends or holidays I need to populate the Friday rate on non weekends and holidays. I have a sample table below with null values on the weekends for the last 90 days but need a script that will show the Friday exchange rate on Saturday and Sunday

Here was my latest attempt

;with cte as
(
select currxdate, [from], [TO], CurrXRate
from dbo.CurrXchange
)
select a.CurrXDate, a.[From],a.[To]
, isnull(a.CurrXRate, b.currxrate) as 'CurrXRate'

[Code] ....

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

T-SQL (SS2K8) :: Change A Selected Value To Null?

Dec 1, 2014

I am emailing data using this:

DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
USE R4W_001
SET @xml = CAST(( SELECT ITEM_ID AS 'td','',"td/@align" = 'right', replace(convert(varchar,convert(Money, AVAILABLE),1),'.00','') AS 'td','',

[Code] ....

The result displays:

ITEM_ID Available DESC EXPCT DELV
AC 16,015Apples 01/18/2015
ORG12 2,908 Oranges 12/30/2014
AHA 3,355Apricots 01/01/1753

Our systems stores 01/01/1753 when the date field is blank. In the above TSQL, how can I replace 01/01/1753 with null or blank in teh results?

View 5 Replies View Related

T-SQL (SS2K8) :: Actual Text Value Of Null

Jun 24, 2015

I have an instance where a process is trying to insert a Customer into the Customers table and CustomerLastName is a non nullable field. Customer's last name is Null. Should this be the reason this Customer is never in the end table?

View 5 Replies View Related

T-SQL (SS2K8) :: How To Modify Procedure For Input Is Null Or Zero

Apr 30, 2014

I am creating web application for state and dist wise map. I've the tables like

create table ind_state
(
ind_stat_id int,
ind_state_name varchar(50)
)
insert into ind_state values ('1','Pondi')

[Code] .....

My output is depends on the dist selection so made procedure

alter procedure LAT_TEST_DYNAM0
@dist_id int
as
begin
create table #temp (pincode varchar(10) ,latitude numeric(18,10),longitude numeric(18,10) ,descp varchar(5000))
if @dist_id!=0

[Code] ....

Myself doubt is when @dist_id is null or "0" how to show default value ?

Otherwise above my code is correct?

View 4 Replies View Related

T-SQL (SS2K8) :: Check If Variable Is Empty Or Null?

Sep 9, 2014

declare @user varchar(30) = ''
if(@user is not null or @user <> '')
BEGIN
print 'I am not empty'
END
ELSE
BEGIN
print 'I am empty'
ENd

The output should be 'i am empty' but when i execute this i am getting ' i am not empty'. even i did browse through but i don't find the difference in my logic.

View 9 Replies View Related

T-SQL (SS2K8) :: List When Both Column Values Are Null

Feb 24, 2015

I have the following tables:

tbl_Person (personID, first_name, lastname)
tbl_student (studentID)
tbl_stupar (personID, StudentID, relationship)
tbl_person_Phone (personID, phone)

I need to list all students who have both parents phone number is null. The parent relationship values 1 and 2 indicates the person is either Mom (value 2) or dad (value 1) of the student. Note: I am using student parent as an example to write my query.

View 4 Replies View Related

T-SQL (SS2K8) :: Filtered Index With IS NULL Predicate

May 2, 2009

I believe query optimizer can leverage the predicate used to define the filtered index. But I came to a situation that's not true:

USE tempdb;
GO

IF OBJECT_ID('Employees') IS NOT NULL
DROP TABLE Employees;
GO

CREATE TABLE Employees

[Code] ....

If we take a look at the query plan. we'll find an unnecessary Nested Loop and Key Lookup operator there with the IS NULL predicate.

Ironically, If I change the IS NULL to IS NOT NULL in both the index and Query, everything goes fine.

View 9 Replies View Related

T-SQL (SS2K8) :: Comparison In The Merge Statement About Null Values?

Aug 22, 2012

I use the merge statement in a sproc to insert, update and delete records from a staging table to a production table.

In the long sql, here is a part of it,

When Matched and

((Student.SchoolID <> esis.SchoolID
OR
Student.GradeLevel <> esis.GradeLevel
OR
Student.LegalName <> esis.LegalName
OR
Student.WithdrawDate <> esis.WithdrawDate
Student.SPEDFlag <> esis.SPEDFlag
OR
Student.MailingAddress <> esis.MailingAddress)

Then update

Set Student.Schoolid=esis.schoolid,
.....

My question is how about if the column has null values in it.for example

if schoolID is null in production table is null, but in staging table is not null, will the <> return true.or if either side of <> has a null value, will it return true.

I don't want it to omit some records and causing the students records not get updated.If not return true, how to fix this?

View 9 Replies View Related

T-SQL (SS2K8) :: Find Null Values Between 3 Datasets In One Table

Mar 21, 2014

I have a table with data in two columns, item and system. I am trying to accomplish is we want to compare if an item exists in 1, 2 or all systems. If it exists, show item under that system's column, and display NULL in the other columns.

I have aSQL Fiddle that will allow you to visualize the schema.

The closest I've come to solving this is a SQL pivot, however, this is dependent on me knowing the name of the items, which I won't in a real life scenario.

select [system 1], [system 2], [system 3]
from
(
SELECT distinct system, item FROM test
where item = 'item 1'
) x
pivot
(
max(item)

[Code]...

View 2 Replies View Related

T-SQL (SS2K8) :: Inserting Multiple Records Each Containing Null Value For One Of Fields

Apr 11, 2014

I'm trying to insert multiple records, each containing a null value for one of the fields, but not having much luck. This is the code I'm using:

Use InternationalTrade
Go
CREATE TABLE dbo.ACCTING_ADJUST
(
stlmnt_instr_id varchar(20) null,
instr_id varchar(20) not null,

[Code] ....

View 5 Replies View Related

T-SQL (SS2K8) :: How To Quickly Update XML Column In A Table To NULL

Mar 9, 2015

I have a table with hundreds of millions of records and need to update an xml column to null. I do something like this:

UPDATE [Mydb].[MySchema].[MyTable]
SET [XMLColumn] = null

Currently it is taking around 6 hours.

Is there a quicker way to do this?

View 1 Replies View Related

T-SQL (SS2K8) :: OUTER JOIN Not Producing Non Existent Record With IS NULL

Aug 17, 2015

I have this View and want to also see Clubs that do not have current memberships.I have the IS NULL but not seeing the Clubs that do NOT have memberships. attribute.PersonMembership is a SQL table that has membership information.

SELECT dbo.v060ClubOfficersPresOrNot.ClubNo, dbo.v060ClubOfficersPresOrNot.SortName, dbo.v060ClubOfficersPresOrNot.ClubName,
dbo.v060ClubOfficersPresOrNot.BSProgram, dbo.v060ClubOfficersPresOrNot.ClubSection, dbo.v060ClubOfficersPresOrNot.Code,
RTRIM(attribute.PersonMembership.InvoiceNumber) AS InvNo, dbo.v060ClubOfficersPresOrNot.President, dbo.v060ClubOfficersPresOrNot.Email,

[Code]...

The "PersonMembership" has all the membership records from 2015 through 2019 of membertypeid 1-4 for the sampling.Since the syntax used in Access do not carry over without modifications to SQL, SQL syntax to make it work in SQL.And if you know the proper SQL syntax for "Between Year(Date())+IIf(Month(Date())>=7,1,0) And Year(Date())+IIf(Month(Date())>=7,1,0)+4" instead of what I currently have in SQL, that would be wonderful.

View 9 Replies View Related

T-SQL (SS2K8) :: Encrypting SSN Field Using TDE?

Jul 15, 2014

Any example on encrypting a SSN Field using TDE?

[URL].....

View 9 Replies View Related

Null In Bit Field?

Jan 19, 2000

sorry, this is probably a really basic question ..........

is there any way to enable the bit data type in SQL Server to accept nulls?


thanks

nyoobee

View 1 Replies View Related

How To Add NULL To INT Field

Jun 12, 2007

Hi all,

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?

Thank you for any help in advance.

View 11 Replies View Related

How To Set A Field Value To Null

Mar 26, 2014

I have a column called person.last_name, and I want to set this field to NULL if person.last_name ='test'///

I have tried this but don't work: ISNULL ({person. last_name},'<>"test'))

View 7 Replies View Related

Set Field Value To Null

Apr 22, 2007

May i know how to generate a SQL query to set a field of row to Null? Thank you.

View 2 Replies View Related

T-SQL (SS2K8) :: Update Field Value For Numbers Only

May 13, 2014

Large design flaw, I know, but we have a table with a varchar field which contains alpha - numeric values. I need to change just the numerics, which can be in an position on the field:

Sample data of the field:
Rec1: Sally Morgan 201-555-1212
Rec2: 555-4040 John Smith
Rec3: Jane Houstin 201-555-6452 ext1223

Desired result:
Rec1: Sally Morgan 999-999-9999
Rec2: 999-9999 John Smith
Rec3: Jane Houstin 999-999-9999 ext9999

There's a bunch of UDFs out there for selecting just the numerics, but I'm having trouble throwing it into a viable script for repeatable execution.

View 8 Replies View Related

T-SQL (SS2K8) :: Case For Field Type

Jun 7, 2014

Is there an easy way to send back to my C# program the pascal case of a field type? If you get the parameters or columns for a table or procedure the type is in all lowercase (varchar instead of VarChar). But in C#, it is in an enum (SqlDbType) that has the variables as mixed case so you can't compare them and just pass the type. You need to figure out what the case should be - varchar should be VarChar and bigint should be BigInt.

View 1 Replies View Related

T-SQL (SS2K8) :: Update A Field With Condition

Aug 10, 2014

Table A - data example
Idno Entity Flag
1 1 y
2 1 n
3 1 y
4 1 y

Table B - data example
Idno Entity
1 1
2 1
3 1

Table C - data example
Idno Entity
4 1

I want to update the value flag in table A.Update flag from n to y if idno A in table B and and idno A not in table C, so from table A above, first record and 3rd record remain unchange but 2nd record, flag will be changed from n to y. 4th record where idno A not in table b and in Table C so flag will change from y to n. is it possible, or do i need to use the "fetch" method to compare record by record.

View 4 Replies View Related

T-SQL (SS2K8) :: Comparison Of Times In Same Field

Sep 22, 2015

I'm looking for comparing times in the same field. I have two tables, one table has times of sessions (multiple rows) and the other contains the session ID, start & end times of overall session block and each session length. I am looking for a way to compare from the start of the session until the end all the times sequentially and insert or mark all the sessions that fall between 10 min intervals.

i.e start 10:30 compare to 10:35 (no match), 10:30 to 10:40 (match 10:40 becomes new effective date), 10:40 to 10:45 (no match), 10:40 to 10:50 (match 10:50 new effective date).

I've attempted a couple of self join solutions but simply can't seem to get what I'm after.

CREATE TABLE MyTable (
[id] INT identity NOT NULL,
[sess_id] INT NOT NULL,
[Time] datetime NOT NULL
)
GO

[Code] .....

View 6 Replies View Related

Null For A Datevalue Field

Mar 26, 2008

Hello all,
Can some one help me with this issue.  It seems to be a common question and I have tried a lot of options but nothing seems to work for me. 
I have a text box and this is linked to ajax calendar extender.  They get a date and I am inserting/updating the date in a sql 2000 database using a stored procdure.
This is the code I use to grab the data from my web form 
If txtTrimsEntryDate.Text = String.Empty Then
_oTrim.TrimsEntryDate = System.Data.SqlTypes.SqlDateTime.Null
Else_oTrim.TrimsEntryDate = CDate(txtTrimsEntryDate.Text)
End If
If txtEstCompletion.Text = String.Empty Then
_oTrim.EstCompletionDate = System.Data.SqlTypes.SqlDateTime.Null
Else_oTrim.EstCompletionDate = CDate(txtEstCompletion.Text)
End If
If txtProjectClosedDate.Text = String.Empty Then
_oTrim.CloseDate = System.Data.SqlTypes.SqlDateTime.Null
Else_oTrim.CloseDate = CDate(txtProjectClosedDate.Text)
End If
_oTrim.InsertOrUpdateAProject()
 
 
This is the code for the stored Procedure
Dim oSql As clsSqlServer = Nothing
oSql = New clsSqlServer(AppSettings(_dbName))oSql.AddParameter("@projTrimsEntryDate ", _trimsEntryDate, SQLDataType.SQLDateTime, ParameterDirection.Input)
oSql.AddParameter("@projStartDate", _startDate, SQLDataType.SQLDateTime, ParameterDirection.Input)oSql.AddParameter("@estCompletionDate", _estCompletionDate, SQLDataType.SQLDateTime, ParameterDirection.Input)
oSql.AddParameter("@closeDate", _closeDate, SQLDataType.SQLDateTime, ParameterDirection.Input)
 Now when it inserts or updates if the value is null then it inserts "01/01/1900" in the database.  I want the value to be Null in database.
Is there a way to do this.   
 
 
 
 

View 3 Replies View Related

Exclude Row If A Particular Field Is Null

Jun 12, 2008

Is there any way of excluding an entire row if a particular field contains a null value? Even if other fields in the row aren't null...  This is in SQL Server 2005 

View 2 Replies View Related

How To Write Where For Field That Can Be NULL Or Int

Jan 31, 2005

There must be a better way to write this where clause. Can somebody help

if @id is null
begin
(bla bla bla) where parentid is null
end
else
begin
(bla bla bla) where parentid = @id
end

View 9 Replies View Related

How To Use BCP With Null Date Field

May 28, 2008

My tests have shown that if you use BCP to import records with a
date field as " ", it won't work.

Is there a way to get BCP to accept empty date fields?

View 3 Replies View Related

ASP UPDATE SET FIELD = NULL

Sep 4, 2003

Using an SQL server and writing ASP code trying to set a numeric field to null.
ie ssql = "UPDATE users SET customer_id = null where name = 'Joe'"

Keeps on generating a syntax error

View 2 Replies View Related

Want To Display Null In A Field

Sep 20, 2006

ad1 ad2
-----------------------------
younge joe
null null
youn null

want a result like that. anyone can help me to do it?


ad1+ad2
-----------------------
younge joe
null
youn

View 6 Replies View Related

Doing A 'between' On A NULL In A Date Field

Mar 29, 2007

Hi everyone,

I have two tables - caseinfo and steps.

Caseinfo shows when a particular case entered and exited a particular project. If the project hasn't ended yet, then the end date is NULL.

Steps shows the steps the case has gone through and the dates of those particular steps.

I need to join the tables to show the steps the case went through during a particular project, but I'm having trouble with the NULL values in the end dates.

If I join the tables so that the step date is between the start and end dates of the project, then I get no step information for the cases where the end date is NULL (that is, where the project hasn't ended yet).

Does anybody have any ideas?

Here are my tables, the query that shows the main idea (with the wrong result), and my expected results.

Thank you for reading.


--- create sample data
set dateformat ymd

declare @caseinfo table (caseid int, startdate smalldatetime, enddate smalldatetime)

insert @caseinfo
select 10, '2006-12-23', '2006-12-27' union all
select 20, '2006-12-23', NULL union all
select 30, '2006-12-23', NULL union all
select 40, '2007-1-15', '2007-3-4'

declare @steps table (caseid int, stepnumber int, stepdate smalldatetime)

insert @steps
select 10, 1, '2006-12-24' union all
select 10, 2, '2007-1-3' union all
select 10, 3, '2007-2-5' union all
select 20, 1, '2006-12-26' union all
select 20, 2, '2007-1-7' union all
select 20, 3, '2007-1-9' union all
select 30, 1, '2007-1-14' union all
select 40, 1, '2007-1-23' union all
select 40, 2, '2007-3-2' union all
select 40, 3, '2007-4-16'

--- the main idea (with the wrong results)

select *
from @caseinfo c
left join @steps s on s.caseid = c.caseid and s.stepdate between c.startdate and c.enddate

--- expected result

declare @expresult table (caseidexp int, startdateexp smalldatetime, enddateexp smalldatetime, stepnumberexp int, stepdateexp smalldatetime)

insert @expresult
select 10, '2006-12-23', '2006-12-27', 1, '2006-12-24' union all
select 20, '2006-12-23', NULL, 1, '2006-12-26' union all
select 20, '2006-12-23', NULL, 2, '2007-1-7' union all
select 20, '2006-12-23', NULL, 3, '2007-1-9' union all
select 30, '2006-12-23', NULL, 1, '2007-1-14' union all
select 40, '2007-1-15', '2007-3-4', 1, '2007-1-23' union all
select 40, '2007-1-15', '2007-3-4', 2, '2007-3-2'

select *
from @expresult

View 1 Replies View Related







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