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


ADVERTISEMENT

How To Check For Null / Empty For A Variable In Post Deployment Script

Oct 28, 2015

I am using SSDT to deploy my database project. This project has a post deployment script and i am creating a new user in this post deployment script. I have used sqlcmd variable to capture the username. I am doing a null/empty check for this variable and my code looks like below.

IF (([$(UserName)] IS NOT NULL) OR ([$(UserName)] <> ''))
BEGIN
CREATE USER [$(UserName)] WITHOUT LOGIN WITH DEFAULT_SCHEMA = dbo;
GRANT CONNECT TO [$(UserName)];
END

When i execute this, I am getting below error.

Could not deploy package.

Error SQL72014: .Net SqlClient Data Provider: Msg 207, Level 16, State 1,Line 15 Invalid column name 'User Name'.

Error SQL72045: Script execution error. The executed script:

View 2 Replies View Related

Check For Null Or Empty Values And Return 0

Mar 13, 2014

I am using the below query to calculate column values. But I need to return zero when a column values is empty or null.

select [Funding] [Fundings],
[Original] AS [Originals],
[Variance] = SUM([Previous_Year]-[Current_Year]),
[SumValue] = SUM([CurrentYear]/4),
[ActualValue] = SUM([Variance] * 0.75),
[FinanceYear],
[New Value] = SUM([Previous_Year]+[Current_Year])
from Finance
GROUP BY [Original], [FinanceYear]

View 1 Replies View Related

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

Anyway To Check If A Text Field Is Blank (not Null But Just A Empty String '') ?

Oct 27, 2004

i have a trigger on a table right now... when fields are inserted, theres a text field inserted and i want to check if that text field = '' (the empty string, not NULL) and if it doesn't equal that, then perform some row updates on other tables, but if it is empty, to not do anything else in the trigger... right now i have this:


Code:


IF ((SELECT Note FROM XATPoDetail WHERE ReqNbr = (SELECT ReqNbr FROM Inserted)) LIKE(''))



Note is the text field, XATPoDetail is the table where its being inserted into. I had to do the select FROM the table because it wouldn't let me select a text data type from the "Inserted" virtual table

but it tells me me "Error 279: The text, ntext, and image data types are invalid in this subquery or aggregate expression"

thanks

View 2 Replies View Related

Anyway To Check If A Text Field Is Blank (not Null But Just A Empty String '') ?

Oct 27, 2004

i have a trigger on a table right now... when fields are inserted, theres a text field inserted and i want to check if that text field = '' (the empty string, not NULL) and if it doesn't equal that, then perform some row updates on other tables, but if it is empty, to not do anything else in the trigger... right now i have this:


IF ((SELECT Note FROM XATPoDetail WHERE ReqNbr = (SELECT ReqNbr FROM Inserted)) LIKE(''))


Note is the text field, XATPoDetail is the table where its being inserted into. I had to do the select FROM the table because it wouldn't let me select a text data type from the "Inserted" virtual table

but it tells me me "Error 279: The text, ntext, and image data types are invalid in this subquery or aggregate expression"

thanks

View 3 Replies View Related

T-SQL (SS2K8) :: Check Variable Values Dynamically

Nov 9, 2014

I have created dynamic sql to declare variables based on columns from the table and i set values to those variable now here is the issue . i want to check the variable values how do i do that dynamically

drop table test
create table test
(
id varchar(10) not null,
col1 varchar(10) ,
col2 varchar(10)

[Code] .....

Now my next step is verify if the variable is blank or not how do i do that ?

How do i verify all of the columns one after the other .

I am after the statement like this dynamically

-- IF NOT (@col1 = '') THEN set @SQL = @SQL + '[col1] = ' + @col1 + ' '
--IF NOT (@col2 = '') THEN set @SQL = @SQL + '[col2] = ' + @col2 + ' '

I need to check if the columns are blank or not dynamically as i do not want to hard code the column names there.

View 4 Replies View Related

Change Not Null To Null, Default Value To Empty

Aug 3, 2005

hi,my structure table in database:Amount float(53) not null default 0when i try to run his script:alter table ABC alter column Amount float(53) nullit can only set the Amount to allow null, but can't set the defaultvalue to empty.anyone know how to set the field to allow null and default set toempty, no value.thanks

View 5 Replies View Related

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

Check If A Table Is Not Empty

Nov 15, 2006

Hi,

i wanna know how can i check a table has datas inside, because i wanna use delete from table and if table is empty i dont wanna run this statement.

thanks in advance

View 5 Replies View Related

Check If SqlDataSource Is Empty In CodeBehind

Feb 10, 2006

This is probably an easy one.
What is best way to determine if a SqlDataSource is empty (i.e. the query produced no results) in the CodeBehind?
I'm using this:
if (SqlDataSource1.SelectCommand.Contains(String.Empty))
{
      //Add code for scenario here.
}
It seems to work, but something just doesn't feel right about it for some reason.
Thanks

View 2 Replies View Related

How To Check If A Table Is &<empty&> Ie Doesnt Contain Any Rows

Aug 18, 2007

Hi, is there a way I can check if a table is empty and contains norows?I have a Table1 which being dynamic can sometimes end up with nowcolumns what so ever. I'm using Table1 in one of my views along with 2other tables and I would like put a condition at the...something likeAND Table1 IS NOTEMPTYIs there a way to do this in MS SQL?Many thanksYas

View 7 Replies View Related

How To Check If A Table Is Empty Using Query Code

Aug 9, 2013

I want to check if a table is empty using query code?

How to do this?

View 4 Replies View Related

T-SQL Syntax To Check For Nulls And Empty In One Statement

May 5, 2008

What is the T-SQL command to check for NULL or '' in a field in one statement? I would like to change the following code to be more readable (without the OR).

IF @agent IS NULL OR @agent = ''



Thanks!

View 1 Replies View Related

Data Flow Task Empty After Check In

Dec 5, 2006

On my office PC I have a strange problem with Data Flow Tasks. When I check-in a package into Source Control (Team Foundation) I usually have some empty Data Flow Tasks in the packages...even if I haven't changed that Flow Task.

This is really frustrating cause when running the package all goes well, but after a whil you notice that some tables are empty.

Does anyone know what the problem is?

View 18 Replies View Related

Empty &&<&&> IS NOT NULL

Jul 22, 2007

Hello,



I have a query that returns the appropriate values I need, however there is one field I'd like to add and utilize but my problem is I only want to use it if it contains data.



If I filter it with IS NOT NULL it returns all the records, including the empty records. The field is simply empty, and doesn't come back as NULL. If I filter it with =' ' , it shows all the records with the empty records only.



I need to do the opposite, be able to filter it only if it's not empty.



Any help would be appreciated.

View 4 Replies View Related

Empty String To Null

Jul 11, 2001

example
create view v_GuestOrder
as
Select T1.id_Guest,T2.OrderName
from Guest T1
left join Order T2 T2.id_order = T1.Id_order


select * from v_GuestOrder
--
Id_Guest OrderName
-------- -----
1 spoon
2 phone
3
4 tv

I need something similar to

Select
id_Guest,
case orderName
when '' then Null -- Sql server gives error in thsi case
end as orderName
from v_GuestOrder

So I need to assign NULL to OrderName is query return empty string,
it will be treated by Crystal reports as Null

Please help , thanks

View 3 Replies View Related

NULL On Empty Fields

Mar 16, 2008

How can I make empty cells show NULL on my table? Some cells show NULL others won't. Does this mean that they have contents?

The reason being is that, when I use the code

Select *
From Employees
Where JobDescription1 Like '%montly%'

Those with empty jobdescription1 show with the legitimate results.

Any help please?

Thanks!

View 2 Replies View Related

COALESCE: Empty Instead Of NULL?

Mar 25, 2008

I've inherited a terribly designed database. When cells in the tables have nothing in them, rather than being NULL, they're just empty. So now I can't use COALESCE...

Is there a way for COALESCE to check if a cell is empty instead of NULL? And if not, is there a way to get around this?

View 1 Replies View Related

T-SQL (SS2K8) :: How To Find All Empty Files In A Database

May 4, 2015

I need to find all empty files in the database (SQL Server 2008R2 SP2) The files become empty after the archival of a partitioned tables.

I was trying this:

select f.name, *
from sysfiles f (nolock)
left join sys.filegroups fg (nolock)
on f.name = fg.name
left join sys.indexes i (nolock)
on i.data_space_id = fg.data_space_id
left join sys.all_objects o (nolock)
ON i.[object_id] = o.[object_id]
where i.name is NULL and o.name is NULL

Did not work.

My file names are the same as the filgroup names containing the file, this is why I was joining by name.

View 2 Replies View Related

Help Please (Check File Exists/ Archive File/ Check If File Empty)

Mar 10, 2008



Hello World,

I'm new to SSIS and would like a little assistance getting started, if possible...


Here is what I want to do:


Check if file exist (C:DTS UpgradeFilexxx.txt) --->

Archive file (C:DTS UpgradeArchive) --->

Check if file has data (true or false)


AND/OR

If there are any good website that have good direction, let me know


Thanks in advance for your help!!!

View 5 Replies View Related

Checking If String Is NULL Or EMPTY In SQL

Nov 9, 2007

I need to check in my Stored procedure if the information passed is null or empty so I can decided to insert the new value or keep the old. How do I accomplish this please in T-SQL. Thanks in advance.

View 6 Replies View Related

Insert Empty String Or Null To Sql Db

Jan 12, 2008

Hi:
Trying to insert null value into sql table, but not working, if I use:
 if (strMyText.Length == 0)        command.Parameters.Add("@Text", DBNull.Value); // or using:("@Text", null), or using:("@Text", DBNull) else         command.Parameters.AddWithValue("@Text", strMyText); 
When I go back to table, I see the value is: 'NULL', has single quotation mark, suppose to be: NULL
Where is the problem?
Thanks a lot.
Jt

View 3 Replies View Related

Null, Empty Strings, And Efficiency

Oct 12, 2004

************* Edited by moderator Adec ***************
Inserted missing < code></ code> tags. Always include such
tags when including code in your postings. Don't force the
moderators to do this for you. Many readers disregard
postings without the code tags.
**************************************************

Well met,

Let's say I have a web form that allows users to insert and update records in a SQL database. Is it better to set empty web controls (textbox, etc.) to DBNull or let it go as an empty string into the database?

Example code:

if (tboxNAME.Text == "")
{
sqlCommand1.Parameters["@NAME"].Value = DBNull.Value;
}
else
{
sqlCommand1.Parameters["@NAME"].Value = tboxNAME.Text;
}

Is the above overkill? It seems like it would be a good idea to set fields which the user empties back to Null rather than an empty string.

Thanks,

-Tony

View 2 Replies View Related

Null Or Empty String Use More Space?

Aug 9, 2005

Hi all,   I have some columns in my database which allows null.  I want to know if leaving the field to be NULL or storing an empty string into the field, which will take up more space?? if the field type is varchar(100)

View 5 Replies View Related

HELP : Empty String To Null Conversion By SQL

Nov 10, 2005

Hi folks,

I've have about 100 tables, for some reasons, column values that are originally NULL was inserted as emtpy string. So, I am wondering if I can write JUST ONE SQL (hopefully don't have to specify the field names in the SQL as well) for each table so that all the empty strings will be converted back to NULL.

THANKS JOHN

View 3 Replies View Related

Analysis :: Convert A Zero To Null Or Empty?

Sep 10, 2015

I am working on a cube and I want to hide the results of a calculation if it is zero.

It is an inventory problem based on a transaction pattern. (add plus one to the inventory, add minus one to the inventory).

The sum of those two is zero, but the cube get messed but by thousands of zero.

I found some solutions of displaying 0 for NULL but I want to dispaly NULL (or empty) for 0.

View 2 Replies View Related

T-SQL (SS2K8) :: Select Query Remove Empty Cells?

Jun 21, 2014

SELECT DISTINCT S.EnrollNo
,S.Name
,ET.Descriptions AS EventName
,SA.Name AS AttendStudent
,'' AS AttendFaculty
FROM StudentEvent SE
INNER JOIN SStudent S ON SE.PresentatorID = S.StudentID

[code]....

Expected OutPut

EnrollNo NameEventName AttendStudentAttendFaculty
PH1201ASHKARALIPresentationKUMARA Sawadkar
PH1201ASHKARALIPresentationNILESHRAJ. GNaveen
PH1201ASHKARALIPresentationSEENIVASAN Nitya
PH1201ASHKARALIPresentation-----------Ramesh
PH1201ASHKARALIPresentation-----------Sakthi

View 2 Replies View Related

Empty Status Of A Variable

Jul 23, 2005

The example bellow will receive a parameter.create procedure usp_InsertProducts@SKU varchar(30)Now how do I check whether is @SKU empty or not.Your help is kindly appreciated.RegardsEugene Anthony*** Sent via Developersdex http://www.developersdex.com ***

View 1 Replies View Related

Is My Object Variable Empty?

Sep 20, 2007

Hallo!

I got an object variable. I used it in a Recordset destination?.
How can I proof if it is empty?

Isnull(@[Benutzer::MyVar]) does not work!

View 5 Replies View Related

NULL Values Vs Empty String Vs Space

Oct 25, 2006

How do I define a field to have the default value = ''. Not NULL but not a space either in SQL Server 2005?

View 10 Replies View Related

Within A SELECT, How Do I Replace An Empty Or Null Value With A Value From Another Table?

Apr 20, 2008

Hello,I'm a beginner in SQL and I have been searching through the SQL Cookbook and Google but I can't seem to find an example of what I want to do. I want to create a report that will return names and emails using two of my tables. I want to use the email in my primary table in the select but if it is null or empty I want to replace it with an email from my secondary table. Below is what I would like to do but I got a syntax error with it in SQL Server 2000. SELECT MemberID As ID, MemberFirstName As FirstName, MemberLastName As LastName, (IF MemberEmail = '' THEN SELECT TOP 1 OtherEmail FROM OtherTable WHERE OtherID = MemberID) As EmailFROM PrimaryTableThanks for your time.Jason  

View 8 Replies View Related

SQL Server 2014 :: NULL And Empty Strings

Oct 29, 2015

I have always (or at least intended to) treat NULL and empty strings separately in my SQL querying history. Now I have run across something that mystifies me (but probably shouldn't) that I would like an explanation for.

Consider this bit o' code:

DECLARE @ORDER CHAR(10)
SET @ORDER=
(
SELECT NULL
)
IF @ORDER <> ''
PRINT 'Not an empty string'

IF @ORDER IS NULL
PRINT 'It is NULL'

Run this and you will get:

It is NULL

I was expecting:

Not an empty string
It is NULL

Why is NULL not passing the 'not an empty string' test? In other words, how does NULL = '' ? Is NULL cast to an empty string for this comparison?

View 9 Replies View Related







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