Sp_executesql Bug? - @@error = 0 When @statement Is Null

Aug 22, 2007

Example code:

declare @somesql nvarchar(20)
execute sp_executesql @somesql, N'@lnCurrRow int', @lnCurrRow = 1

@@Error returns 0, which is seems incorrect since @somesql is null. If you substitute @somesql for the constant NULL then an error is fired. The above also executes successfully with @@error = 0 if you set @somesql = '' (empty string).

EXEC behaves the same way.

It makes sense that executing an empty string should be fine, but NULL? Is there a SET option available that will cause an error to be raised if sp_executesql attempts to execute a nvarchar variable that's set to NULL?


View 4 Replies


Error Executing A Sql Statement Against A Table With Null Date Values

Apr 10, 2008

We are just upgrading a server from 2000 to 2005 and we are getting the message below when we execute a sql statement against a table with a date field with null values:

"Error converting data type DBTYPE_DBTIMESTAMP to datetime."

View 3 Replies View Related

Error Handling When Using SP_EXECUTESQL

Oct 30, 2000

I am using dynamica SQL in one of my stored procs That performs BULKINSERT
on few tables.
If there is a PK constraint violation, I wanted to write an error routine.
But once SP_EXECUTESQL gives an error, the stored proc just abends
not allowing me to ado anything else.
Can anyone help me with some information on how to perform error
handling when I am using Dynamic SQL?
I am also sending the the piece of code and the error message.

CODE: Assume that all variables are declared appropriately.

SET @string = 'BULK INSERT Tb_Manfg ' + char(13) +
'FROM '+ '''' + @path_tb_manfg + '''' + char(13) +
'WITH (FIELDTERMINATOR = ' + '''' + '|=|'+ '''' + ',' + char(13) +
'ROWTERMINATOR = ' + '''' + '
' + '''' + ')'

IF @error = 0
This is where the stored proce abends.
The subsequent code is not executed at all.
PRINT 'ERROR IS.........'
IF @@ERROR = 2627
SET @error = @@ERROR
SET @error = @@ERROR

Server: Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK__TB_Manfg__1FCDBCEB'. Cannot insert duplicate key in object 'TB_Manfg'.
The statement has been terminated.

Thanks a lot.

View 1 Replies View Related

Sp_executesql And Error Handling

May 22, 2007


I need to capture the error code while using sp_executesql

insert into #temp1
exec @status=sp_executesql @actn_id1

This @actn_id1 contains the proc name and the parameters. The problem now is when I get an error itís not captured in the @status column, it always 0 and also tried @@error and was also 0.

Server: Msg 55118, Level 16, State 1, Procedure S_START_LUW, Line 383
info_upd_id =530129179546!, is using current subj_id.

This is a user defined error captured using raiserror inside a sub proc.

Can I capture this error number in the status column?

Thanks in advance for the help

View 5 Replies View Related

Sp_executesql Silly Error

Jul 23, 2005

Gurus,Here is what I ma trying to do. I have numeric expression stored in atable column. for e.g. @a + @b + @c. I supply values to the variablesat run time and want them to be computed at run time as per theexpression in the column.the stored procedure works fine but it gives a silly error.Any help greatly appreciated. Below is the code.--drop procedure proc_bkrcreate procedure proc_bkr ASdeclare @expr nvarchar(2000)declare @sql nvarchar(2000)declare @temp_exp nvarchar(3000)declare @ans integerdeclare @QFAAPAC02_1 integerdeclare @QFAAPAC02_2 integerdeclare @QFAAPAC02_3 integerdeclare @QFAAPAC02_4 integer-- Assigning values to variables -- Startset @QFAAPAC02_1 = (Select QFAAPAC02_1 from fa_ap_stage where recordid= 3)set @QFAAPAC02_2 = (Select QFAAPAC02_2 from fa_ap_stage where recordid= 3)set @QFAAPAC02_3 = (Select QFAAPAC02_3 from fa_ap_stage where recordid= 3)set @QFAAPAC02_4 = (Select QFAAPAC02_4 from fa_ap_stage where recordid= 3)-- Assigning values to variables -- Endset @temp_exp = (select num from translation where processid = 'AP' andlabel = 'C1')-- This is how num looks: @QFAAPAC02_1 + @QFAAPAC02_2 + @QFAAPAC02_3 +@QFAAPAC02_4--select @expr = '@QFAAPAC02_1 + @QFAAPAC02_2 + @QFAAPAC02_3 +@QFAAPAC02_4'-- Above line works fine but below one does not. though both are same.select @expr = @temp_expselect @sql = 'select @ans = ' + @exprexec sp_executesql @sql, N'@QFAAPAC02_1 integer, @QFAAPAC02_2 integer,@QFAAPAC02_3 integer, @QFAAPAC02_4 integer, @ans integer OUTPUT',@QFAAPAC02_1,@QFAAPAC02_2,@QFAAPAC02_3,@QFAAPAC02_ 4,@ans OUTPUTset @cc = @ansError Message: Server: Msg 137, Level 15, State 2, Line 1[Microsoft][ODBC SQL Server Driver][SQL Server]Must declare thevariable '@QFAAPAC02_'.Thanks in Advance!Bkr

View 1 Replies View Related

Invalid Column Error With Sp_executesql

Jun 13, 2005

Hey all,

Having some trouble with a Database email system I created. The system consists of two tables, DATA_ELEMENT and EMAIL_MESSAGE. So the email message body and recipient fields may contain substitution macros such as {![CUST_EMAIL]!}. The CUST_EMAIL data element row then stores the SELECT, FROM and WHERE clauses separately. There is a stored proc to search the message body and recipients for these substitution macros and replace them with the appropriate values from the DB.

The system is working well except I have one particular substitution macro called VENUE_NAME_BY_PPPID which is causing a problem.

Quote: Server: Msg 207, Level 16, State 3, Line 3
Invalid column name 'PARTNER_PRODUCT_PRIZE_ID'.

And here's the query which is creates this error (without the escaped single quotes):



And just after this print statement, the query is executed with sp_executesql()

Any advice is greatly appreciated as this query runs fine when I execute from the query window. However, if I escape all the necessary quotes, I can't get it to run when I put the string inside of sp_executesql().


View 1 Replies View Related

Sp_executesql Error From SSIS OLE DB Command

Jul 20, 2006


I've got an SSIS package, I'm in a Data Flow step that has a OLE DB Command Data Flow Transformation.

The SQLCommand in the OLD DB Command is:
UPDATE Employment_Episode_Dim
SET Commence_Serv_Date = ?
WHERE AGS_Number = ?

The package will run through successfully, but I don't see any updates
in Employment_Episode_Dim. I ran a trace, and this is a sample of the
SQL that is being executed:

WHERE (AGS_NUMBER = @P2)',N'@P1 datetime,@P2 int',''2005-01-27

If I take that SQL and execute it in a query window, it fails due to
two single quotation marks placed around the date parameter being used
as @P1.

Why does SQL/SSIS put two singles around the date? It's outside of the
string to be executed, so it doesn't seem to need to have the double.

Can anyone please help?


View 2 Replies View Related

Exec Sp_executesql Vs. Sp_executesql And Performance

Jul 23, 2005

This is a odd problem where a bad plan was chosen again and again, butthen not.Using the profiler, I identified an application-issued statement thatperformed poorly. It took this form:exec sp_executesql N'SELECT col1, col2 FROM t1 WHERE (t2= @Parm1)',N'@Parm1 int', @Parm1 = 8609t2 is a foreign key column, and is indexed.I took the statement into query analyzer and executed it there. Thequery plan showed that it was doing a scan of the primary key index,which is clustered. That's a bad choice.I then fiddled with it to see what would result in a good plan.1) I changed it to hard code the query value (but with the parmdefinition still in place. )It performed well, using the correct index.Here's how it looked.exec sp_executesql N'SELECT cbord.cbo1013p_AZItemElement.AZEl_Intid AS[Oid], cbord.cbo1013p_AZItemElement.incomplete_flag AS [IsIncomplete],cbord.cbo1013p_AZItemElement.traceflag AS [IsTraceAmount],cbord.cbo1013p_AZItemElement.standardqty AS [StandardAmount],cbord.cbo1013p_AZItemElement.Uitem_intid AS [NutritionItemOid],cbord.cbo1013p_AZItemElement.AZeldef_intid AS [AnalysisElementOid] FROMcbord.cbo1013p_AZItemElement WHERE (Uitem_intid= 8609)', N'@Parm1 int',@Parm1 = 8609After doing this, re-executing the original form still gave badresults.2) I restored the use of the parm, but removed the 'exec' from thestart.It performed well.After that (surprise!) it also performed well in the original form.What's going on here?

View 3 Replies View Related

Report Error Using Stored Procedure With Exec Sp_executesql

Feb 13, 2008


Our report is working fine with data loaded from a stored procedure (#1) that contains a fairly simple Select statement. We need the same report to work with a dataset loaded from a stored procedure (#2) that uses 'Exec sp_executesql @queryString'. Unfortunately, attempts to call the latter cause an error in the report. From everything that I've read, there should be no difference between datasets created using either method. Any ideas what could be getting in the way of the latter?

I have doublechecked that the dynamic query is returning a valid dataset and that all the columns are in the same format as sp #1. The designer shows the dataset and the report with the data loaded, but the live system produces an error.

Any help is much appreciated.

View 4 Replies View Related

'Syntax Error Converting Datetime From Character String' With Sp_executesql

Jul 20, 2005

CREATE PROCEDURE dbo.Synchronization_GetNewRecords(@item varchar(50),@last datetime)ASSET NOCOUNT ONDECLARE @sql nvarchar(4000)SET @sql = 'SELECT * FROM ' + @item + ' WHERE LastUpdated >' + @lastEXEC sp_executesql @sql, N'@Type varchar(50), @Last datetime', @item, @lastThis is my SP. Very simple. But it is throwing the error in the subject line.Any help would be greatly appreciated.

View 1 Replies View Related

Sp_executesql - Error Converting Data Type Varchar To Datetime

Oct 13, 2006


I am havin problems with the following giving a message

Msg 8114, Level 16, State 4, Line 0

Error converting data type varchar to datetime.

exec sp_executesql


SET [REQUEST] = @19, [DISC_EXPRY] = @28, [GROUP1] = @29, [GROUP2] = @31, [PR_LEVEL] = @48, [MOD_DATE] = @55, [MEM_CODE] = @63, [MEM_DATE] = @64, [HO_MOD] = @66, [LASTUPDATE] = @78

WHERE [ID] = @Old_1'

, @params=N'@19 nvarchar(4),@28 DateTime,@29 nvarchar(5),@31 nvarchar(5),@48 nvarchar(1),@55 DateTime,@63 nvarchar(7),@64 DateTime,@66 Integer,@78 Float,@Old_1 Integer',

@19= 'NRMA',@28= '1752-09-14',@29= 'ALBUM',@31= 'FRAME',@48= 'A',@55= '2006-10-10',@63= '1003.50',@64= '2006-10-10',@66= 3,@78= 39000.190633,@Old_1= 454636

however, as soon as I remove the datetime fields it works

exec sp_executesql


SET [REQUEST] = @19, [GROUP1] = @29, [GROUP2] = @31, [PR_LEVEL] = @48, [MEM_CODE] = @63, [HO_MOD] = @66, [LASTUPDATE] = @78

WHERE [ID] = @Old_1'

, @params=N'@19 nvarchar(4),@29 nvarchar(5),@31 nvarchar(5),@48 nvarchar(1),@63 nvarchar(7),@66 Integer,@78 Float,@Old_1 Integer',

@19= 'NRMA',@29= 'ALBUM',@31= 'FRAME',@48= 'A',@63= '1003.50',@66= 3,@78= 39000.190633,@Old_1= 454636

what am I doing wrong with the datetime parameters?


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

Checking For Null Statement In Sql

Feb 16, 2006

Hi Everyone,
  I have a query
select name, address, city from table1
I want to append address +'-' + city in this query . I want to show hyphen only if both address and city are not null. If name is null then I don't want to show hyphen. How can I get around this problem.
Any help will be appreciated.

View 3 Replies View Related

Problems With 'like Statement' And NULL

Mar 29, 2007

I searched through the forums thinking this may have been brought up before, but I cannot find a post the directs to my problem.

I have a lookup table (lookup.os) which defines various operating systems. It has a "OSID" field which is just a primary key numbering, a "OSInstr" field which is used in a 'like statement' in a cursor used later on and "OSName" which is the full name of the OS.

1 | Windows XP | Windows+NT+5.1
2 | Windows 2003 | Windows+NT+5.2
3 | Windows Vists | Windows+NT+6.0
19 | Unknown Operating System | <blank>

I have a cursor loop that goes through another table (tblIISLog_ALL_OS) and searches through the instr of the (lookup.os) table to add to a stats of a third table (statistics.os). It works fine going through the loop, except there is a problem with the last value in the (lookup.os) table. If the value does not match the rest, it will select value "19 - Unknown Operating System" which is correct, but if the value is null it will not be selected. I take it 'NULL' >< a blank like statement. Here is what I have now. I am open to suggestions of what I should do. I was thinking maybe removing the "19 - Unknown Operating System" from the (lookup.os) table and after the first cursor loop has run, run it again and everything remaining it will just put in as "19 - Unknown Operating System". What do you think?

DECLARE @TempID int,
@TempOSInStr varchar(50),
@TempOSName varchar(100),
@TempCount int
FROM [IISLOG_REPORTS].[dbo].[lookup.os]

OPEN loopc


SET @TempID = 0
SET @TempCount = 0
WHILE @@fetch_status = 0

SELECT [date], @TempOSName, count(1)
WHERE '%' + [csUserAgent] + '%' like '%' + @TempOSInStr + '%'
GROUP BY [date]

INSERT INTO [IISLOG_REPORTS].[statistics].[os]
SELECT [date], @TempID, count(1)
WHERE '%' + [csUserAgent] + '%' like '%' + @TempOSInStr + '%'
GROUP BY [date]

WHERE '%' + [csUserAgent] + '%' like '%' + @TempOSInStr + '%'

CLOSE loopc

Thanks for the assistance. I hope I provided all the necessary information and explained it ok.

View 3 Replies View Related

Prepared Statement Where Value IS Null Or Value = :x

Jul 20, 2005

Hello,I have a table which contains some nullable columns. I want to write asingle query, which can be prepared (ie. prepared statement), that canhandle null or non-null values for the where clause. Is this possiblein a standard-conforming manner?The simple for of the query is this:SELECT * FROM <table> WHERE <column> = <value>But when the value to be matched on is NULL, the syntax of the querymust change to be:SELECT * FROM <table> WHERE <column> IS <value>In the second case <value> is NULL.I know one option might be to change the ANSI NULL handling option,but I am loathe to do this (I have five RDBMS's to support, not justMSSSQL).I thought I might have been able to cheat using an IN clause to makethe SQL consistent, but no luck.ThanksKevin

View 6 Replies View Related

Select Statement When Values Are Not Null

Sep 4, 2007

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

CASE Statement And NULL Values

Oct 22, 2013

Code below:


If I am switching the NULL values to hyphens (-), why am I not getting any records when I look for hyphens in the WHERE clause? The script is not as simple as this or else I would just check for NULL values. I really need to switch NULL values to something I can pass on to a variable and query on it from Reporting Services.

View 6 Replies View Related

SQL Statement Count NULL Values

Jul 20, 2005

Hello,Thanks for helping me with this... I really appreciate it.I have a table called tblPatientDemographics with a number of columns.I would like to count the number of NULL values per record within mytable.tblPatientDemographicsPatientID Age Weight Height Race1234567 20 155 <NULL> Caucasian8912345 21 <NULL> <NULL> <NULL>In the first example above I want to display '1'In the second example above I want to display '3'Any help would be very much appreciated.Thanks !Chad*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 3 Replies View Related

Reporting Services :: IIF Statement And Null Value

Aug 12, 2015

In ssrs, I want to display a column "Actual Value", which is a currency field.

If the field is null, I want to display a "0.00" value, otherwise I want to display the actual value with 2 decimals as well

Not sure how to do this. Below is what I have so far....


View 4 Replies View Related

Need Help With Insert Statement On Table With Several Non-null Fields

Apr 30, 2007

On my aspx page I have a basic sqldatasource and gridview - both were set up using the wizards. The SQL Server 2000 database table that the sqldatasource is querying has some fields in it that are set to not allow nulls, however, the gridview control is not displaying all of the fields in the table - including some of the non-null fields.
My problem is, when I run an insert on the table from my aspx page, I get an error that says: "Cannot insert the value NULL into column 'ColumnName', table 'TableName'; column does not allow nulls. INSERT fails. The statement has been terminated."
The 'ColumnName', as you may have guessed, is one of the aforementioned columns that doesn't allow nulls, but isn't in the GridView.
How can I do an insert on this table without messing with the non-null fields. Those fields are relevant to the purposes of the gridview, so I don't want to display them, let alone allow editing of them.
Any suggestions on my options would be greatly appreciated!

View 2 Replies View Related

Problem With Null Fields And Case Statement

Jul 21, 2004

Can someone help me with this :

Case WHEN ISNULL (dbo.BLH.n1name,'NOTHING')= 'NOTHING' then dbo.BLH.coname else dbo.BLH.n1name End as CustName

as the strored proc is not returning value when the fileds is blank


View 2 Replies View Related

UPDATE Statement Creating NULL Fields?

Oct 23, 2014

I am attempting to update a table that drives a report. The report is at the order level. An order can have several types of demand (revenue $): Ship and/or Backordered. When I update one of these demand fields on the report table, I would expect those orders that have that type of demand to update, and those orders that don't have that type of demand to remain at their current value ($0 in this example which is the table default). But what is happening is that orders that don't have that type of demand are changing to NULL. What am i doing wrong with my update statement that is causing this?

OrdNo int,
StatusGrp varchar(10),
Demand decimal(10,2)
VALUES (1,'Ship',100.00)

[Code] .....

View 6 Replies View Related

Case Statement On Where Clause If Parameter =NULL

Jun 3, 2008

I am working on a Function that takes multiple parameters. I have a query that populates a temporary table, and then it processes some logic. My question is, if the parameter is passed as null, I dont want the query to be affected by this null value. Rather, I would like to not pass it at all to the query. So if the parameter is NULL, dont pass it through the query. I have the following but its not compiling right:

SELECT bom.pEngr_BOM_ID , bom.fEngr_Item_ID, det.pEngr_BOM_Detail_ID, 1, bom.Bill_Type, bom.Rev_Ltr, bom.Series_Ltr
FROM dbo.Engr_BOM_Control bom WITH (nolock)
INNER JOIN dbo.Engr_BOM_Detail det WITH (nolock)
ON det.fEngr_BOM_ID=bom.pEngr_BOM_ID
WHERE bom.pEngr_BOM_ID=@v_pEngr_BOM_ID
AND det.fEngr_BOM_ID=@v_pEngr_BOM_ID
AND bom.Bill_Type=@v_Bill_Type

View 3 Replies View Related

Change NULL Values To Default In SELECT Statement

Dec 22, 2006

I have a stored procedure with a SELECT statement, that retrieves 1 row.
SELECT name FROM tblNames WHERE nameID = "1"
I want all the NULL values in that row to be change in some default values.
How do I do this?

View 4 Replies View Related

Comparing To DateTimes In SQL-Select-Statement When One Date Can Be Null

Aug 25, 2007

Hello! I have a field "End" in my database that is mapped as DateTime and allows nulls. Now I want to do a SQL-Select (in a SqlDataSource) like SELECT * FROM My_Table Where (([End] = @EndDate) OR ([End] = null))  @EndDate is a valid DateTime, but the second OR condition doesn't work. What is the best way to check if the [End]-field is empty or null? Thank you very much! 

View 1 Replies View Related

Return NULL Values In SELECT Statement With INNER JOIN ?

May 16, 2005

If I try to run the code below, and even one of the values in the INNER
JOIN statements is NULL, the DataReader ends up with zero rows. 
What I need is to see the results even if one or more of INNER JOIN
statements has a NULL value.  For example, if I want info on
asset# 2104, and there's no value in the DriverID field, I need the
rest of the data to display and just have the lblDriverName by
blank.  Is that possible?

    Sub BindSearchGrid()
        Dim searchUnitID As String
        Dim searchQuery As String
        searchUnitID = tbSearchUnitID.Text
        lblIDNum.Text = searchUnitID
        searchQuery = "SELECT * FROM Assets " & _
        "INNER JOIN Condition ON Condition.ConditionID = Assets.ConditionID " & _
        "INNER JOIN Drivers ON Drivers.DriverID = Assets.DriverID " & _
        "INNER JOIN Departments ON Departments.DepartmentID = Assets.DepartmentID " & _
        "INNER JOIN AssetCategories
ON AssetCategories.AssetCategoryID = Assets.AssetCategoryID " & _
        "INNER JOIN Store ON
Store.[Store ID] = Assets.StoreID WHERE RTRIM(Assets.[Unit ID]) = '"
& searchUnitID & "'"

        Dim myReader As SqlDataReader
        myReader = Data.queryDB(searchQuery)
        While myReader.Read
Not IsDBNull(myReader("Store Name")) Then lblStrID.Text =
myReader("Store Name")
Not IsDBNull(myReader("AssetCategory")) Then lblAsstCat.Text =
Not IsDBNull(myReader("Condition Description")) Then lblCondID.Text =
myReader("Condition Description")
Not IsDBNull(myReader("DepartmentName")) Then lblDepID.Text =
Not IsDBNull(myReader("Unit ID")) Then lblUnID.Text = myReader("Unit
Not IsDBNull(myReader("Year")) Then lblYr.Text = myReader("Year")
Not IsDBNull(myReader("Make")) Then lblMk.Text = myReader("Make")
Not IsDBNull(myReader("Model")) Then lblMod.Text = myReader("Model")
Not IsDBNull(myReader("Mileage")) Then lblMile.Text =
Not IsDBNull(myReader("Vin Number")) Then lblVinNum.Text =
myReader("Vin Number")
Not IsDBNull(myReader("License Number")) Then lblLicNum.Text =
myReader("License Number")
Not IsDBNull(myReader("Name")) Then lblDriverName.Text =
Not IsDBNull(myReader("DateAcquired")) Then lblDateAcq.Text =
Not IsDBNull(myReader("DateSold")) Then lblDtSld.Text =
Not IsDBNull(myReader("PurchasePrice")) Then lblPrPrice.Text =
Not IsDBNull(myReader("NextSchedMaint")) Then lblNSM.Text =
Not IsDBNull(myReader("GVWR")) Then lblGrVWR.Text = myReader("GVWR")
Not IsDBNull(myReader("GVW")) Then lblGrVW.Text = myReader("GVW")
Not IsDBNull(myReader("Crane Capacity")) Then lblCrCap.Text =
myReader("Crane Capacity")
Not IsDBNull(myReader("Crane Certification")) Then lblCrCert.Text =
myReader("Crane Certification")
Not IsDBNull(myReader("Repair Cost")) Then lblRepCost.Text =
myReader("Repair Cost")
Not IsDBNull(myReader("Estimate Replacement")) Then lblEstRep.Text =
myReader("Estimate Replacement")
Not IsDBNull(myReader("SalvageValue")) Then lblSalVal.Text =
Not IsDBNull(myReader("CurrentValue")) Then lblCurVal.Text =
Not IsDBNull(myReader("Comments")) Then lblCom.Text =
Not IsDBNull(myReader("Description")) Then lblDesc.Text =

        End While
    End Sub</code>

View 1 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
Student.GradeLevel <> esis.GradeLevel
Student.LegalName <> esis.LegalName
Student.WithdrawDate <> esis.WithdrawDate
Student.SPEDFlag <> esis.SPEDFlag
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

SQL Server 2014 :: Finding NULL In CASE Statement

Sep 15, 2015

The below code returns a NULL for all the Differential backups. I am trying to return the word Differential when there is a NULL because I can tell from when the NULLs occur that those are my differential backups. How can I get the result I want?

CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,

WHEN msdb..backupset.type = 'D' THEN 'Database'

[Code] .....

View 9 Replies View Related

Capture Null/blank Values In An Update Statement

Sep 13, 2007

I have built a SSIS package that bascially updates two columns in table A...the update statement reads;

update Table A
set Colum 1 = ?,('?' is a variable)
Column 2 = ?

In my SSIS package, I would like to be notified/capture, if one or both variables are null....How do I do that ?..error log ?

The package runs fine both ways (if variables are null or not)

Thank you

View 1 Replies View Related

Select Statement Returns Null In Stored Proc

Feb 22, 2006

If I run this statement in Query Analyzer, it properly returns 1for my testing table. But if I put the statement into a storedprocedure, the stored procedure returns NULL. What am I doingwrong? I suspect it may be related to how I defined the parametersfor the stored procedure. Perhaps my definition of TableName andColumnName don't match what COLUMNPROPERTY and OBJECT_ID expect toreceive, but I don't know where to look for the function declarationsfor those. Any pointers would be appreciated.Select statement:SELECT COLUMNPROPERTY(OBJECT_ID('Table1'), 'TestID', 'IsIdentity') ASIsIdentityTable definition:CREATE TABLE [dbo].[Table1] ([TestID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL) ON [PRIMARY]Stored Procedure definition:CREATE PROCEDURE spTest(@TableName varchar,@ColumnName varchar)AS SELECT COLUMNPROPERTY(OBJECT_ID(@TableName), @ColumnName,'IsIdentity') AS IsIdentity

View 2 Replies View Related

SQL Statement: Group By And Where Clause Do Not Display Null Rows

Jun 17, 2007

The following is a simplified version of my SQL statement. I am attempting to do a simple count(*) with two groupings and a where clause. This is Select command for a GridView. However, I am unable to display zeros. The rows are completely missing and I would greatly appreciate someone's help. I have already tried Group By All, but that, unfortunately, does not work. Here is my SQL statement:


Thanks for the help in advance!

View 1 Replies View Related

SQL Server 2012 :: Exclude NULL Values From Select Statement

Feb 4, 2014

I wrote a select statement, I only want to see orders with max lastUpdatedOn date of 14 days and older. Now my results show dates with all orders of 14 days and older (which is OK), but all others are displayed in the "Uitgifte" column as "NULL". But those orders should not be displayed at all.

selectdistinct ProductionHeader.ProdHeaderOrdNr,
(select max (ProdStatusLog.ProdStatusCode)


View 8 Replies View Related

How To Return Null Values For Non-exist Record On A Left Join Statement

Oct 16, 2004

I have table Products and Orders that has the following columns:

table Products: ProductID, ProductName
table Orders: OrderID, ProductID, OrderDate, Quantity, Price

The Orders table contains orders placed on all the dates. I want to obtain a list of orders for a particular date, if there is no order for a product on the requested date, I want to return null values for the Quantity and Price fields.

I tried the following select statement:

select Products.ProductName, Orders.Quantity, Orders.Price from Products left join Orders on Products.ProductID = Orders.ProductID where Orders.OrderDate = '10/16/2004'

Where, there are a total of three products (A,B,C) in table Products. Product-C has no order on 10/16/2004, but I want it to return :

ProductName / Quantity / Price
Product-A 5 1.89
Product-B 6 2.43
Product-C null null

Obviously, my sql statement won't work becaue the where clause will filter out Product-C.

Could anyone help me figure out how to modify my sql code to get the resultset I want?

Thanks in advance.

View 2 Replies View Related

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