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)
 Dynamic SQL statement and NULLS

Author  Topic 

Nate
Starting Member

17 Posts
Posted - 2002-04-05 : 13:07:05
I am having a dilemna with an archiving program. I am moving data from one database to another and the integrity of the data is of crucial importance. The origin database contains some columns which have NULLS. When I create a dynamic insert statement the NULL blows the statement apart. The statement looks like:

-- turn the identity increment on so rows can be inserted
Select @SQL = 'Set IDENTITY_INSERT '
Select @SQL = @SQL + @DestinationDatabase
Select @SQL = @SQL + '.t_ResourceContacts ON;'
--Exec (@SQL)

Select @SQL = @SQL + 'Insert '
Select @SQL = @SQL + @DestinationDatabase
Select @SQL = @SQL + '.t_ResourceContacts (rc_ContactID, rc_ContactNumber,rc_ResourceID,
rc_MainContactFlag,rc_AttachResourceID,rc_LastName,
rc_FirstName,rc_Title,rc_LocationID,
rc_StartDate, rc_EndDate, rc_RecDate) Values ("'
Select @SQL = @SQL + LTrim(RTrim(Str(@CurContactID))) + '", "' +
LTrim(RTrim(Str(@CurContactNumber))) + '", "' +
LTrim(RTrim(Str(@CurResourceID))) + '", '
+ QuoteName(RTrim(LTrim(@CurMainContactFlag)), '"') + ', "' +
@BlankCheck1 + '", ' +
QuoteName(RTrim(LTrim(@CurLastName)), '"') + ', ' +
QuoteName(RTrim(LTrim(@CurFirstName)), '"') + ', ' +
QuoteName(RTrim(LTrim(@CurTitle)), '"') + ', "' +
@BlankCheck2 + '", "' +
RTrim(LTrim(Cast(@CurStartDate As VarChar))) + '", "' +
RTrim(LTrim(Cast(@CurEndDate As VarChar))) + '", "' +
RTrim(LTrim(Cast(@CurRecDate As VarChar))) + '")'
Print (@SQL)
Exec (@SQL)

I can't insert a blank as an alternative.

Any ideas?

Nate

Jay99

468 Posts
Posted - 2002-04-05 : 13:18:30
as an example . . 

...isnull('''+RTrim(LTrim(Cast(@CurEndDate As VarChar)))+''',NULL)...



Have you tried something like that?

Jay
<O>
Go to Top of Page

yakoo
Constraint Violating Yak Guru

312 Posts
Posted - 2002-04-05 : 13:19:53
since you cant use NULL values like ',' + NULL + ',' which will be ',,' I think you will have to do checks for NULL values and then build @SQL with many different if statements


if IsNull(@CurmainContactFlag, 1) = 1
Select @SQL = @SQL + ',NULL'
Else
Select @SQL = QuoteName(RTrim(LTrim(@CurMainContactFlag)), '"')



Edited by - yakoo on 04/05/2002 13:20:42

Edited by - yakoo on 04/05/2002 13:28:05
Go to Top of Page

Jay99

468 Posts
Posted - 2002-04-05 : 13:20:12
or you may have to do the ISNULL check twice per field and replace it once with 'NU' the first time and the second time with 'LL' so you don't blow out @sql. Catch my meaning?

Jay
<O>
Go to Top of Page

Nate
Starting Member

17 Posts
Posted - 2002-04-05 : 13:24:20
I will try and get back to you
Go to Top of Page

MichaelP
Jedi Yak

2489 Posts
Posted - 2002-04-05 : 13:35:55
I'm testing out a few solutions to this problem on my end now (during lunch! I haev no life :)). Could you insert the blanks, and then come back through the table and update all blanks to null?

Michael
Go to Top of Page

yakoo
Constraint Violating Yak Guru

312 Posts
Posted - 2002-04-05 : 13:42:21
You might not want to insert a blank value and update all blanks to NULLs. What if the initial value was blank?

I like Jay's approach to the solution.
For each column, check for NULL twice and append 'NU', then 'LL' to the string. But couldn't you check for NULL once and append the string 'NULL' so you would get ',' + 'NULL' + ',' which would generate ',NULL,'?

IsNull(RTrim(LTrim(Cast(@CurEndDate As VarChar))),'NULL')
Go to Top of Page

Nate
Starting Member

17 Posts
Posted - 2002-04-05 : 13:47:26
Yakoo, you are absolutely right about MichaelP's solution. Thanks MichaelP but we do have blanks that have been inserted in the database. I know, so much for data integrity.

The ISNULL statement that you suggested yakoo, I have actually tried before and it blows the dynamic SQL away as well. I am testing your previous suggestion but am actually having problems with other parts of the statement right now. Will get back to you.

Thanks
Go to Top of Page

ToddV
Posting Yak Master

218 Posts
Posted - 2002-04-05 : 13:49:35
Nate,

How about using sp_executeSQL

SET @SQL = 'Insert ' + @DestinationDatabase + 't_ResourceContacts (
		rc_ContactID, 
		rc_ContactNumber,
		rc_ResourceID, 
		rc_MainContactFlag,
		rc_AttachResourceID,
		rc_LastName, 
		rc_FirstName,
		rc_Title,
		rc_LocationID, 
		rc_StartDate, 
		rc_EndDate, 
		rc_RecDate) 
Values (@CurContactID,
	@CurContactNumber,
	@CurResourceID,
	@CurMainContactFlag,
	@BlankCheck1,
	@CurLastName,
	@CurFirstName,
	@CurTitle,
	@BlankCheck2,
	@CurStartDate,
	@CurEndDate,
	@CurRecDate)'

EXEC Sp_Executesql = @SQL,
	'@CurContactID INT, @CurContactNumber INT, @CurResourceID INT, @CurMainContactFlag INT,
		@BlankCheck1 INT, @CurLastName Varchar(100), @CurFirstName Varchar(100),@CurTitle Varchar(100),
		@CurTitle Varchar(100), @BlankCheck2 INT, @CurStartDate Datetime, @CurEndDate Datetime, @CurRecDate Datetime' ,@CurContactID,
	@CurContactNumber, @CurResourceID, @CurMainContactFlag, @BlankCheck1, @CurLastName, @CurFirstName, @CurTitle,
		@BlankCheck2, @CurStartDate, @CurEndDate, @CurRecDate


I am not positive I have all the syntax right but you can check that in BOL.

If not this, Jay99 has you on the right track with replacing a null variable with the Word NULL.
Go to Top of Page

MichaelP
Jedi Yak

2489 Posts
Posted - 2002-04-05 : 13:51:03
I think I found it.
Try this at the top of your SQL.

SET CONCAT_NULL_YIELDS_NULL OFF

Use this with Jay's solution.

I THINK that will work.

I'm not sure, but you may need to do
SET CONCAT_NULL_YIELDS_NULL ON
at the end of your statement.

Jay, am I right or wrong here?
Go to Top of Page

ToddV
Posting Yak Master

218 Posts
Posted - 2002-04-05 : 13:53:05
quote:

as an example . . 

...isnull('''+RTrim(LTrim(Cast(@CurEndDate As VarChar)))+''',NULL)...


Except We would want the word null here

...isnull('''+RTrim(LTrim(Cast(@CurEndDate As VarChar)))+''','NULL')...
Go to Top of Page

Jay99

468 Posts
Posted - 2002-04-05 : 13:59:53 

declare @NULLVALUE varchar(8000)
--doesn't work
select 'insert tablename values(''' + @nullvalue + ''')'
SET CONCAT_NULL_YIELDS_NULL OFF
--doesn't work
select 'insert tablename values(''' + @nullvalue + ''')'
SET CONCAT_NULL_YIELDS_NULL on

--doesn't work
select 'insert tablename values(''' + isnull(@nullvalue,NULL) + ''')'

--WORKS!!!!
select 'insert tablename values(' + isnull( '''' +@nullvalue+'''' ,'NU') + isnull('''' +@nullvalue+'''' ,'LL') + ')'
set @nullvalue = 'not null anymore'
select 'insert tablename values(' + isnull( '''' +@nullvalue+'''' ,'NU') + isnull('''' +@nullvalue+'''' ,'LL') + ')'




Jay
<O>


Edited by - Jay99 on 04/05/2002 14:02:09
Go to Top of Page

Nate
Starting Member

17 Posts
Posted - 2002-04-05 : 14:07:49
I tried the statement:

SET CONCAT_NULL_YIELDS_NULL OFF

and it didn't work on the insert.

Will try the concatenation Jay99 and get back to you

Thanks for all your input here guys, keeping me running to keep up with the flow of ideas.
Go to Top of Page

Nate
Starting Member

17 Posts
Posted - 2002-04-05 : 14:16:18
Jay99, thanks for the input. that works. learning lots about dynamic SQL here.

thanks to all of you for helping me out here.
Go to Top of Page

ToddV
Posting Yak Master

218 Posts
Posted - 2002-04-05 : 14:20:50
quote:


--WORKS!!!!
select 'insert tablename values(' + isnull( '''' +@nullvalue+'''' ,'NU') + isnull('''' +@nullvalue+'''' ,'LL') + ')'
set @nullvalue = 'not null anymore'
select 'insert tablename values(' + isnull( '''' +@nullvalue+'''' ,'NU') + isnull('''' +@nullvalue+'''' ,'LL') + ')'




Please do not do this. See my posts above.
Go to Top of Page

yakoo
Constraint Violating Yak Guru

312 Posts
Posted - 2002-04-05 : 14:32:29
good catch there Todd. If you did the IsNull twice and there wasn't a null value in the column than you would have duplicates.

Ie: ',' + IsNull(101, 'NU') + IsNull(101, 'LL') + ','
would return ,101101, when all we want is ,101,

Anyone know why ',' + IsNull(@value, 'NULL') + ',' didn't work?
If @value was NULL than wouldn't the string value be ',NULL,'?
Go to Top of Page

ToddV
Posting Yak Master

218 Posts
Posted - 2002-04-05 : 14:49:36
quote:

good catch there Todd.

Thank you

quote:

Anyone know why ',' + IsNull(@value, 'NULL') + ',' didn't work?
If @value was NULL than wouldn't the string value be ',NULL,'?

I don't think it has been tried
Go to Top of Page

yakoo
Constraint Violating Yak Guru

312 Posts
Posted - 2002-04-05 : 14:54:03
In a previous post I mentioned
quote:

You might not want to insert a blank value and update all blanks to NULLs. What if the initial value was blank?

I like Jay's approach to the solution.
For each column, check for NULL twice and append 'NU', then 'LL' to the string. But couldn't you check for NULL once and append the string 'NULL' so you would get ',' + 'NULL' + ',' which would generate ',NULL,'?

IsNull(RTrim(LTrim(Cast(@CurEndDate As VarChar))),'NULL')


and then Nate said
quote:
The ISNULL statement that you suggested yakoo, I have actually tried before and it blows the dynamic SQL away as well. I am testing your previous suggestion but am actually having problems with other parts of the statement right now. Will get back to you.


So I think it has been tried unless Nate did it wrong. I have a feeling that it should work though.
Go to Top of Page

Jay99

468 Posts
Posted - 2002-04-05 : 15:02:33
ok one more try here . . .


declare @NULLVALUE varchar(8000)
--doesn't work
select 'insert tablename values(''' + @nullvalue + ''')'
SET CONCAT_NULL_YIELDS_NULL OFF
--doesn't work
select 'insert tablename values(''' + @nullvalue + ''')'
SET CONCAT_NULL_YIELDS_NULL on

--doesn't work
select 'insert tablename values(''' + isnull(@nullvalue,NULL) + ''')'

--WORKS!!!!
select 'insert tablename values(' + isnull( '''' +@nullvalue+'''' ,'NULL') + ')'
set @nullvalue = 'not null anymore'
select 'insert tablename values(' + isnull( '''' +@nullvalue+'''' ,'NULL') + ')'


The key is having the escapted-quotes included on the front side of the is null, that way if it is null on the NULL will come back . . . without the quotes . . .

(Sorry about my first post with the incorrect code . . . I must be really falling apart here since I lost my job, my lady left me, my dog got run over by a car, I was arrested and I owe some big guys lots of money. But excuses are excuses; I need to concentrate on SQLTeam!!! )

Jay
<O>
Go to Top of Page

ToddV
Posting Yak Master

218 Posts
Posted - 2002-04-05 : 15:08:07
Nate,
Have you looked at the sp_executesql options I presented earlier yet. It really seems like the way to go here.

quote:

(Sorry about my first post with the incorrect code . . . I must be really falling apart here since I lost my job, my lady left me, my dog got run over by a car, I was arrested and I owe some big guys lots of money. But excuses are excuses; I need to concentrate on SQLTeam!!! )


Jay,
I did not mean to give you additude. Just trying to help.

BTW - At least you don't have any STDs yet.
Go to Top of Page

yakoo
Constraint Violating Yak Guru

312 Posts
Posted - 2002-04-05 : 15:09:30
Thats it!

Just do

IsNull(RTrim(LTrim(Cast(@CurEndDate As VarChar))),'''NULL''')

Or try
declare @nullvalue char(6)
select @nullvalue = '''NULL''

then you could used
IsNull(RTrim(LTrim(Cast(@CurEndDate As VarChar))),@nullvalue)
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site