Isnull And Ntext Variables (short Question)

Jul 23, 2005

Hello to all,

Below the sample code:

declare @arg_szMsgText ntext
set @arg_szMsgText = isnull(@arg_szMsgText, N'unknown message')

Now the error message I get:

"The assignment operator operation cannot take a ntext data type as an
argument."

Explanation:
I'm using it in a SP. I was trying thousand things to make it running,
and the only way I maked was, I get it when I set the return value from
isnull fucntion to nvarchar or varchar variable.
The problem is, the field in DB, where the value of @arg_szMsgText is
written) is ntext, so I need to use this format.

In the description of isnull function (help) is written:

----------------------------------------------------
Arguments
check_expression

Is the expression to be checked for NULL. check_expression can be of
any type.

replacement_value

Is the expression to be returned if check_expression is NULL.
replacement_value must have the same type as check_expresssion.

Return Types
Returns the same type as check_expression.

-----------------------------------------------------

so ite means, I should be able to use this in such a way ...

I know I can assign the value in other way (like by if @variable is
null ....), but if there is already function doing this, why not to use
it, if it helds the code more clear?

Any ideas? Maybe I'm doing something wrong.

Matik

View 3 Replies


ADVERTISEMENT

Function That Replaces Ntext And Compares Ntext With Nvarchar

Nov 28, 2007

I am running this query to an sql server 2000 database from my aspcode:"select * from MyTable whereMySqlServerRemoveStressFunction(MyNtextColumn) = '" &MyAdoRemoveStressFunction(MyString) & "'"The problem is that the replace function doesn't work with the ntextdatatype (so as to replace the stresses with an empty string). I hadto implement the MySqlServerRemoveStressFunction, i.e. a function thattakes a column name as a parameter and returns the text contained inthis column having replaced some letters of the text (the letters withstress). Unfortunately, I could not do that because user-definedfunctions cannot return a value of ntext.So I have the following idea:"select * from MyTable whereCheckIfTheyAreEqualIngoringTheStesses(MyNtextColum n, '" & MyString &"')"How can I implement the CheckIfTheyAreEqualIngoringTheStessesfunction? (I don't know how to combine these functions to do what Iwant: TEXTPTR, UPDATETEXT, WRITETEXT, READTEXT)

View 2 Replies View Related

A Short Question ?

Mar 13, 2004

how can i convert the varchar value to a column of data type int?

i am trying to do this

declare @w varchar(50)
select @w = col1 from myTable


select colname from mtable
where mtableID in (@w)

but the mtableID is a int

and i always got

Syntax error converting the varchar value to a column of data type int.

thanks for everyone for helping

View 1 Replies View Related

Two Short Questions

Mar 17, 2004

can anyone tell me do i have to use sqldmo in asp.net C# to backup or restore database?
and how can i execute the *.sql file in asp.net C#?


thanks

View 1 Replies View Related

Cells Too Short

Apr 3, 2008

When I get the results from my query, some of the columns labels are too short and look like this "ran....". How do I extend the cells??

Thanks

US Navy - We are fueled, armed, and go for launch.

View 2 Replies View Related

Short Date?

Mar 13, 2007

Hi all. Is it possible to convert the date to shortdate?

select e.hireddate from hremployees e

it returns 2/07/2007 12:00:00 AM

How can i neglect the time?

Thanks
-Ron-

View 5 Replies View Related

SP2 - The Long And Short Of It

Apr 17, 2007

Problems Encountered Upgrading SQL 2005 SP1 to SQL 2005 SP2 (9.0.3042.1):

In Summary:

Maintenance Plans Broken
Database Mail Broken
Reporting Services Report Viewer web part displays US Format dates instead of report design English-Australian. .
In Detail:

Microsoft SQL Server 2005 SP2 was downloaded from Microsoft.com. This download occurred after 5 March 2007 and the file version was 9.0.3042.1 (SQLServer2005SP2-KB921896-x86-ENU.exe)
During the Installation of SP2 by a Domain Administrator an error occurred on the Database Engine. A Hotfix Exception was raised and the setup completed with errors.
The Error Log reported a problem:
----------------------------------------------------------------------------------
Product : Database Services (MSSQLSERVER)
Product Version (Previous): 2047
Product Version (Final) :
Status : Failure
Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixSQL9_Hotfix_KB921896_sqlrun_sql.msp.log
Error Number : 29534
Error Description : MSP Error: 29534 Service 'MSSQLSERVER' could not be started. Verify that you have sufficient privileges to start system services. The error code is (1067) The process terminated unexpectedly.

----------------------------------------------------------------------------------


The SQL Server service could not be manually started. An error was reported in the event viewer:
One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.


It was noted that a new file mssqlsystemresource.ldf had been copied to the %data% folder.
Note: As part of separating data and log files, both the master and mssqlsystemresource databases had been previously moved to another volume %data% whilst their respective log files had been moved to a separate volume also %log%. This had occurred after SP1 was initially installed and months prior to the SP2 install using Microsoft KB articles to detach and reattach the databases. It appears that the SP2 installer does not work correctly if mssqlsystemresource mdf and ldf files are not in the same location.
Whilst the Engine could not be started €“ the new mssqlsystemresource.ldf file was manually moved to the %log% file, overwriting the previous version.
The SQL Server Service was now able to start without any errors.
Since the SQL Server Service has been started two major errors have become apparent:
The following error is recorded when trying to create a new maintenance plan:
TITLE: Microsoft SQL Server Management Studio
------------------------------

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Invalid column name 'from_msx'.
Invalid column name 'has_targets'. (Microsoft SQL Server, Error: 207)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=207&LinkId=20476

Also when using the maintenance plan wizard the following error is recorded when the Wizard attempts to save the plan at the end:
TITLE: Maintenance Plan Wizard Progress
------------------------------

Saving maintenance plan failed.

------------------------------
ADDITIONAL INFORMATION:

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

------------------------------

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Invalid column name 'msx_job_id'. (Microsoft SQL Server, Error: 207)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=207&LinkId=20476

------------------------------

Database Mail is also broken on the server. The Database Mail Log reports an €śActivation Failure€? when a test email is sent. (the email does not arrive). The SQL Log and event viewer also report the following message:
The activated proc [dbo].[sp_sysmail_activate] running on queue msdb.dbo.ExternalMailQueue output the following: 'Error executing extended stored procedure: Invalid Parameter'


Another error affects Reporting Services reports that are accessed using the SharePoint V2.0 Web parts ( rsWebparts.cab) . The date picker in the Report Viewer web part now only recognises US format dates. It is important to note that the same reports run through the Report Manager web site work correctly using DD/MM/YYYY (English-Australian ) date format for the report picker.
It is noted that the rsWebparts.cab file was not updated by SQL 2005 SP2 install. However, the behaviour of the Report Viewer web part has definitely been affected by the SP2 install.
Are the first two issues related? Are they due to the fact that mssqlsystemresource mdf and ldf files had been moved to an unexpected location prior to SP2? How can this be fixed?
Are the Reporting Services SharePoint (V2) web parts going to be updated? We are not in a position to upgrade our Portal 2003/SharePoint Servcies V2 installation at the present time.

View 2 Replies View Related

Long Vs Short

Mar 26, 2008

Here is a fairly basic question in which I'd like assistance. In determining a standard for a schema, for simplicity, my thinking is that using the long data type would meet our purposes.

Are there any thoughts on mapping out where we would not need to take advantage of long vs short? I realize the space but for this particular table, space won't an issue.

Thanks!

View 7 Replies View Related

Short Cut To Updating Table...

Dec 18, 2007

Is there a way to short cut through the fields in a table when updating them or inserting?
 ie. I have a table with 78 fields and could grow with time. Is there a way to run the UPDATE or INSERT TO [table](blah....) without listing all the table fields?

View 5 Replies View Related

CountyLong Name And County Short Name

Aug 31, 2004

Hello, i have database front end is Access, and backend is sql server. The front end, there are two dropbox, called 'CountyLongName', and 'CountyShortName'. I want to: when i select a countyLongName, there it will pop up the exact CountyShortName in anohter field.

For example, when i click Howard Couty in the 'CountyLongNmae', it will automatically display "HW" in the 'CountyShortName" Field. How can i do that? Thanks.

View 5 Replies View Related

Short Cut To Exec Jobs

Apr 23, 2007

any keyword shortcut to exec a sql job?
do i have to exec step individually when there are more than 2 steps?

View 4 Replies View Related

Combining These 2 Short Stored Procedures

Dec 18, 2003

CREATE PROCEDURE MyBooks_Selling
(
@MemberID SMALLINT
)
AS

SELECT * FROM v_BookInfo_Sellers_Extended WHERE MemberID=@MemberID

GO
GRANT EXEC
ON MyBooks_Selling
TO bto
GO

CREATE PROCEDURE MyBooks_Buying
(
@MemberID SMALLINT
)
AS

SELECT * FROM v_BookInfo_Buyers_Extended WHERE MemberID=@MemberID

GO
GRANT EXEC
ON MyBooks_Buying
TO bto
GO







Is there a way to make it so I could combine those 2 prcedures and choose which table i would like to select from based on another input parameter? I tried it that way but it didnt work...so im asking here to make sure

thx

View 2 Replies View Related

Short Diplay For Datetime Type

Jun 13, 2006

Hi all,
I have a view with a datetime column,
when viewing the column data the format is:

" 2006-06-12T12:00:00.0000000+03:00 "

while i'm only want to see the date part

(that is, 2006-06-12, and if possible in the format: dd/mm/yyyy)
now, when i'm using convert( varchar(10), [column_name],101)
i'm getting the format i need but the problem is that i'm
"losing" the date type, is it possible to view it as the
format i stated and still as a datetime type ??

Regards,
Pit

View 6 Replies View Related

SQL To Find Short, Fat And Stupid People

Jul 23, 2005

This is a question concerning query optimisation. Sorry if it's a bit long,but thanks to anyone who has the patience to help - This is my first posthere...If I have two tables: 'tblContact' and 'tblCategory' where categories arelike:Code Name010101 Short010102 Fat010103 StupidThe junction table 'tblConCat' has fields CctConID, CctCatCode to tell mewhich contacts have which category codes. These are nchar(6) fields, if itmakes any difference.If I need to find all people who are short, fat and stupid I can see twoways:Solution One:SELECT tblContact.* FROM tblContact WHEREConID IN (SELECT CctConID FROM tblConCat WHERE CctCatCode='010101') ANDConID IN (SELECT CctConID FROM tblConCat WHERE CctCatCode='010102') ANDConID IN (SELECT CctConID FROM tblConCat WHERE CctCatCode='010103')Solution Two:Build a helper table which contains the codes I'm looking forSELECT tblContact.* FROM tblContactWHERE ConID IN(SELECT CctConIDFROM tblConCat INNER JOIN tblHelperON tblConCat.CctCatCode = tblHelper.HlpCatCodeGROUP BY CctConID HAVING Count(*)=3)I have tried them both out and although I looked at the query analyzer itprovided more information than I knew what to do with. In practise theyboth provide similar working times (I wait about a second) but I thought thefirst looked rather inefficient and thought my helper table might help.There are about 30,000 contact records, 180 category records and 120,000junction table records.All I am looking for comments on any pros and cons of these two approaches -does one look that bad? The database was migrated from Access where thehelper table really did help, but using SQL Server I might not need it.Thanks again, if you got this far!

View 16 Replies View Related

Empty Page 1 On Short Reports?

Mar 16, 2007

I have a suite of several SSRS reports that differ essentially on the stored procedure called. All have very the same one-page header block and all have a footer. Those where a large number of pages are produced, page without fault when printed or converted to PDF. 

Two page reports often break down on printing, so that page 1 only has the header and a footer and all the detail lines are printed on page2 (together with the footer). However on the screen the report paged correctly.

I have checked all the obvious problems such as page dimensions exceeding that of the print medium. (A series of such problems was fixed earlier in development.)

I have observed this behaviour with SQL2005 SP1 and SP2 (i.e. SP2 did not provide any fix in this area). The host OS for development and production is Windows 2003 server. The tests were done by viewing in IE6 and the fault only became apparent when going to print preview, printing or in PDF production.

This has the appearance of a bug in SSRS hereby intermediate length reports are not handled correctly. It appears to happen when the amount of detail lines just fills page 2. As soon as the report has sufficient lines to be 3 pages long (page 1 is header plus some details lines plus footer, page 2 is full of detail lines plus footer and page 3 is remaining detail lines plus footer then the problem is no longer seen! Where the detail lines will all fit on page 1, then the problem is not seen.

I observe this variation in behaviour since I am able to switch between different sized datasets and thus switch between the different scenarios described above. I should also note that the reports are in landscape mode.

View 3 Replies View Related

SQL Server Short-circuits WHERE Condition Evaluation

May 5, 2008

Hi I am trying to create stored proc and having some performance issue.

Here is the requirement,

Stored Proc parameters A,B
A is required.
B is optional. The default value for B is 0.

Here is the code inside stored proc:

IF (B = 0)
SET B = 1


SELECT * FROM TABLE1
INNER JOIN TABLE2 ON TABLE1.Column1 = TABLE2.Column1
WHERE A= 2008
AND (table2.ColumnB = B or 1 = B)

If I am passing B as 20 I am having performance issue to pull the data.
If I replace B with any other number the query is working fine.

View 4 Replies View Related

SQL 2005 Vs SQL 2000: Where Clause Short-Circuiting?

Oct 12, 2006

Hello all, this is my first post, and I think I've found something interesting between sql 2005 and 2000. Thanks in advance for any help you can provide.

I am attempting to migrate a system from SQL 2000(v 8.00.2039 - Enterprise Edition) to SQL 2005 (v9.0.2047 - Standard Edition ), and a few of my most complex queries are generating errors. I was able to isolate the problem to a fairly simple query that works in SQL 2000, but generates an error in SQL 2005.

I believe that the problem has something to do with how short-circuiting is handled in the WHERE clause. I'm not sure if short-circuiting is really going on when these statements are parsed, but it seems to describe the situation well.

To replicate the problem, first create the following table and fill it with values:

----------------------------------------------
create table OrgView (ORGNAME varchar(100), QID int, ANSWER varchar(100))

insert into orgview (orgname, qid, answer) values('org1', 120, '1')
insert into orgview (orgname, qid, answer) values('org2', 94, '2006-06-06')
insert into orgview (orgname, qid, answer) values('org3', 98, 'free kevin')
----------------------------------------------

The second value (org2) contains a date, but the rest do not.
Under both systems, the following contrived query works fine:

SELECT orgname FROM OrgView
WHERE (QID = 94
AND CONVERT(DATETIME, Answer) = '2006-06-06')

It would seem, that when QID = 94, the expression short circuits, and the CONVERT statement doesn't run. This is good, because the CONVERT statement would throw an error whenever 'Answer' doesn't contain a date.
However, if modify the query by adding an OR to the end like this:

SELECT orgname FROM OrgView
WHERE (QID = 94
AND CONVERT(DATETIME, Answer) = '2006-06-06')
or QID=98

then the query fails on SQL 2005 with a 'Conversion failed when converting datetime from character string.' error.
On SQL 2000, the short circuiting works fine, and the above query returns 'org2' and 'org3'.
I know shortcircuiting may not be the best way to describe the set calculus going on in the background, but can anyone explain what is going on here? Is there any way to make SQL 2005 work like SQL 2000 in this specific case?



Thanks,

-Adam



View 3 Replies View Related

Problem With A Lot Of Inserts In Short Time - SQL Server 2000

Feb 27, 2008

Hi!We have SQL Server 2000 on virtual machine (slowly). We run a pair ofstored procedures from ADO.NET 2.0 (WebService):- first procedure: insert into table A a data row with correlationID- next procedure: search for inserted record in tab A bycorrelationID and insert value into tab B with constraint to tab A.The problem is when we try to do this in short time (few minutes)thousands times (5000/6000 pairs in 5/6 minutes) in 100 parallelthreads:query in second procedure cannot find inserted record in firstprocedure.Everything is dirty read. no transactions.Have you any ideas?edi

View 1 Replies View Related

Ssis Security Short Coming, Major Issues

Jan 6, 2006

Hello,

I feel like ssis encryption model has a serious flaw. Especially when linked to SQL Agent jobs.

I have posted and others have posted messages about this. Something is plain wrong with ssis encryption keys and password protection. Also, you do not have the choice not to protect the packages. In my case, protecting packages is completely useless.

Here is the story.

After this post

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=131340&SiteID=1&mode=1

I created config files for al my packages connections passswords.

Now, by our IT Policy, I had to change again my password and of course, all packages now return multiple errors when I open them.

Hopefully, the config file did its job and the packages are ran anyways by SQL Agent, however, having to manually retype and resave all packages not to have the errors is just a plain hassle. Not to speak about people not using the config files and the correct "Run As" sql agent account.

I stress the fact that in a real world production environment all packages are driven by SQL Agent jobs and MUST run automatically.

Here is the error I get after opening a package after changing my password:

Error 1 Error loading Constants05.dtsx: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.   c:projectsssis packagesssis constantsConstants05.dtsx 1 1 

So Why is'nt this key automatically adjusted after Windows NT Domain password Change?

How can I refresh the key, not to have to reype all the packages connections passwords and rebuilding, Checkin-in again all the stuff?

I do not think the solution is "Use an application account which password never changes when you create your ssis packages" however at this time, this is the only solution I can think of.

How do you guys deal with this problem?

I still do not understand the ssis security model I feel it is diconnected from the reality and unpracticable in a production environment like mine.

Thanks

Philippe

View 17 Replies View Related

Short Circuit Evaluation In Conditional Split Expression

Jun 23, 2006

I think I know the answer to this but thought I'd ask anyway. 

I have a conditional split to check a column for null values or empty string values.  It looks like this:


(!ISNULL(Ballot)) || (LEN(TRIM(Ballot)) > 0)
My question is:  Are both sides of the expression evaluated?  My testing says yes, because a Null value causes an error.  Is there a way to short circuit the evaluation like the || operator in C# or the (less than elegant, and seemingly threatening) OrElse operator in VB?  Whats the best alternative:


A slightly more complex expression that turns a null value into an empty string

A script component

Two conditional splits

Two paths out of one condtional split

I went with the first option, here is the expression I came up with:


LEN(ISNULL(Ballot) ? "" : TRIM(Ballot)) > 0

View 3 Replies View Related

Too Short Keywords Does Not Work Under Full-Text Functionality

Nov 6, 2007

Hi, I am novice to Full-Text funcationality.

The problem I met is that SQL server seems not woring with too short keywords (with FREETEXT syntax). The following is the detailed description.

I index two columns, JobDes(ntext) and JobTitle(nvarchar), which mainly store Traditional Chinese(Big5). If I search with
select * from JobWanted where FREETEXT(JobTitle,'asp'), it goes well. But if I replace the 'asp' with 'as', nothing returns. What is the reason? Can I eliminate this limination?

Any help would be appreciated.
Ricky.



View 1 Replies View Related

Execute DTS 2000 Package Task Editor (Inner Variables Vs Outer Variables)

Sep 4, 2006

Hi,

I am not comfortable with DTS 2000 but I need to execute a encapsulated DTS 2000 package from a SSIS package. The real problem is when I need to pass SSIS variables to DTS 2000 package. The DTS 2000 package have 3 global variables that I can identify on " Execute DTS 2000 Package Task Editor - Inner Variables ". I believe the SSIS variables must be mapped on " Execute DTS 2000 Package Task Editor - OuterVariables ". How can I associate the SSIS variables(OuterVariables ) to "Inner Variables"? How can I do it? Much Thanks.

JoĂŁo





View 8 Replies View Related

How To Design A Package With Variables So That I Can Run It By Dos Command Assigning Values To Variables?

Jan 24, 2006

Hi,

I would like to design a SSIS package, which have couple of variables. It loads a xls file specified in a variable [varExcelFileFullPath] .

I will run it by commands: exec xp_cmdshell 'dtexec /SQL ....' (pls see an example below).

It seems it does not get the values passed in for those variables. I deployed the package to a sql server.

are there any grammar errors here? I copied it from dtexecui. It worked inside Dtexecui not in dos command.

exec xp_cmdshell 'dtexec /SQL "LoadExcelDB" /SERVER test /USER *** /PASSWORD ****

/MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW

/LOGGER "{6AA833A1-E4B2-4431-831B-DE695049DC61}";"Test.SuperBowl"

/Set Package.Variables[User::varExcelFileName].Properties[Value];"TestAdHocLayer"

/Set Package.Variables[User::varExcelWorkbookName].Value;"Sheet1$"

/Set Package.Variables[User::varExcelFileFullPath].Value;"D: estshareTestAdHocLayer.xls"

/Set Package.Variables[User::varDestinationTableName].Value;"FeaturesTmp"

/Set Package.Variables[User::varPreSQLAction].Value;"delete from FeaturesTmp"

'



thanks,



Guangming

View 2 Replies View Related

Isnull

Aug 22, 2000

I am trying to delete a row of data that is all nulls. Help in BOL was a little help but it is not working.

View 2 Replies View Related

ISNULL With LIKE?

Apr 30, 2008

I create a stored procedure like such:

"create procedure tmptable_query (@lname varchar(20)) as select * from #temp_table where lname like ISNULL(@lname+'%',lname)"

Unfortunately, it only returns exact matches, as if it were written as "where lname = ISNULL(@lname, lname)"

However, if I query from a static table the code works correctly.Any ideas on what could be wrong?

Thank you.

View 3 Replies View Related

Using Isnull

Feb 28, 2008

for the query i created i need zeros where ever the filed is blank. i have used count(acc) for selecting the count . can any one help me out with sample query. Thanks in advance

View 5 Replies View Related

Where To Put ISNULL

Jul 20, 2005

I have two tables, tblMTO and tblIMPORT_MTO. If I import an entire MTOinto the import table I want to create a delta from it (i.e. leave onlythe changed items). I have a view (simplified)SELECT dbo.tblIMPORT_MTO.ImportID, dbo.tblIMPORT_MTO.MTONo,dbo.tblIMPORT_MTO.Rev AS New_Rev, dbo.tblMTO.Rev AS Old_RevFROM dbo.tblIMPORT_MTO LEFT OUTER JOINdbo.tblMTO ON dbo.tblIMPORT_MTO.MTONo = dbo.tblMTO.MTONoNow to get all rows where old_rev = new_rev I also want all rows whereboth are null, is the best method to put ISNULL() in the view or toselect from the view using ISNULL in the criteria, e.g.select * from view1 where ISNULL(Old_Rev,0)=ISNULL(new_rev,0)or in the viewCREATE VIEW view1 asSELECT dbo.tblIMPORT_MTO.ImportID, dbo.tblIMPORT_MTO.MTONo,ISNULL(dbo.tblIMPORT_MTO.Rev,0) AS New_Rev, ISNULL(dbo.tblMTO.Rev ASOld_Rev,0)FROM dbo.tblIMPORT_MTO LEFT OUTER JOINdbo.tblMTO ON dbo.tblIMPORT_MTO.MTONo = dbo.tblMTO.MTONo;select * from view1 where Old_Rev=new_rev;

View 2 Replies View Related

Isnull()

Apr 24, 2006

does sql server mobile 2005 support the isnull function? I'm getting an error when I try to use it and I don't know if it is becuase of using the isnull function or not, but when I run the same query on Sql Server 2005 it works fine.

View 5 Replies View Related

Using ISNULL In WHERE Clauses

Apr 29, 2008

I've seen lots of entries recommending the use of ISNULL in SQL WHERE clauses, e.g. in a search sproc where users can enter some or all parameters to search a table. Previously I would have used something like:SELECT * FROM MyTableWHERE (FName = @fname OR @fname IS NULL) AND(MName = @mname OR @mname IS NULL) AND(LName = @lname OR @lname IS NULL)So using the neat ISNULL syntax it could be updated to:SELECT * FROM MyTableWHERE (FName = ISNULL(@fname, FName)) AND(MName = ISNULL(@mname, MName)) AND(LName = ISNULL(@lname, LName))Having played around with this I stumbled upon a problem. If one of the fields, e.g. MName, is NULL then that clause will return false since MName = NULL isn't true and you have to use MName IS NULL. Did I miss all the caveats with using ISNULL in this way on fields that can contain NULL or have I missed something else? 

View 4 Replies View Related

My ISNULL Confusion

May 1, 2008

I've got this query and it use to work or at least I thought it did. Could someone please help me with it.
Thank you
SELECT CID, CompletionDate, MarkedExport, CustomerName, EditUser, RouteID, WorkOrder FROM RouteCustomer WHERE (CompletionDate IS NOT NULL) AND (ExportDate IS NULL) AND (RouteID LIKE '%' + ISNULL(RouteID,@RouteID) + '%') AND (EditUser IS NULL OR EditUser = '' OR EditUser = @EmployeeID) AND (MONTH(CompletionDate) = ISNULL(MONTH(CompletionDate),@Month))
The problem comes with in the WHERE clause. What I wanted it to do is if the user did want to use a RouteID critera then the user would speified one else it wouldn't, and it was my belief that the ISNULL feature in SQL was the answer for that. same for the Month. I believe the EditUser is fine the way it is written.
thanks to anyone that can help me with this.
 Rex

View 9 Replies View Related

ISNull Question

Jun 7, 2005

I have to display string "not assigined" when a datefield is null in a table.
I am using like ISNULL(datefiled, "not assigned"), but I am getting following error
Syntax error converting character string to smalldatetime data type.
Is there any way, I can acheive desired result.
Please help

View 1 Replies View Related

CASE Vs ISNULL?

Feb 1, 2015

To give you a little background, there is a CRM system with SQL server as its back-end. The CRM uses a view in SQL Server to list all the communications a user has had with his client over any given interval of time. Now there is a requirement to add a new column in the view that tells a user if the communication was filed in automatically or if it happened overnight via an automated archive manager process. I have achieved this using an expression field which is based on the comm_url field in the communications table in database.

example:

create view vCommunications
as
select col1, col2,...,case when comm_url is null then 'Manually filed' else 'Automatically Filed' as Filing
from Communications

alternatively, this can also be achieved by the following:

create view vCommunications
as
select col1, col2,...,isnull(comm_url, 'Manually Filed') as Filing
from Communications

Now my question is, given that there are many rows in the communications table, which of the above two expression fields will be more efficient in performance i.e. CASE versus ISNULL. I've checked a lot on google but I haven't been able to come up with a concrete answer.

View 3 Replies View Related

IsNull Question

Apr 9, 2008

Hi All,

Quick question...

why doesn't this work....
select IsNULL(select null),'TallOne')

But this does...
select IsNull((Select null),0)

How does it know what datatype I'm trying to get?

View 16 Replies View Related







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