Issues with SQL Statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Issues with SQL Statement

Author

Topic

Spetty
Starting Member

25 Posts

Posted - 2006-12-12 : 17:37:11

declare @sql nvarchar(4000)
set @sql = ''
SELECT @sql = 'SELECT client.Firstname, client.Lastname, client.Address, client.Address2, client.City, client.State, client.Zip,
Information1 AS [' + isnull(Information1,'Information1') + '],
Information2 AS [' + isnull(Information2,'Information2') + '],
Information3 AS [' + isnull(Information3,'Information3') + '],
Information4 AS [' + isnull(Information4,'Information4') + ']
FROM clientextended inner join client on clientextended.client = client.number'
FROM clientextendedtemplate
exec sp_executesql @sql

This is a query I am running. It works great thanks to the help from snsql. However we needed to add trapping in for null values, which works with using isnull. However, now when the query is run, isnull() is taken care of, but if clientextendedtemplate.information1 is empty (NOT null) it will not work.

Any ideas how to trap for both Empty and Null Values?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-12 : 17:43:25

This?

declare @sql nvarchar(4000)
set @sql = ''
SELECT @sql = 'SELECT client.Firstname, client.Lastname, client.Address, client.Address2, client.City, client.State, client.Zip, 
Information1 AS [' + case when isnull(Information1, '') = '' then 'Information1' else Information1 end + '], 
Information2 AS [' + case when isnull(Information1, '') = '' then 'Information2' else Information2 end + '], 
Information3 AS [' + case when isnull(Information1, '') = '' then 'Information3' else Information3 end + '], 
Information4 AS [' + case when isnull(Information1, '') = '' then 'Information4' else Information4 end + '] 
FROM clientextended inner join client on clientextended.client = client.number'
FROM clientextendedtemplate
exec sp_executesql @sql

Peter Larsson
Helsingborg, Sweden

Spetty
Starting Member

25 Posts

Posted - 2006-12-12 : 17:59:45

Thanks Peso. Initial testing is yielding weird results. Some it's running for, others it's showing command successful, and another DB it just wasn't running. I will have to play with what you're giving me and see what I can find out.

Finally got a chance to sit down and play with your updated statement. Saw that you listed Information1 in the isnull part. Fixed it and it seems to be working like a charm.

Thanks.
Shawn

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources