Dynamic SQL -Howto set a col to NULL

Dynamic SQL -Howto set a col to NULL

Post by Anders Stolp » Tue, 12 Jun 2001 23:25:13



Hello!

Does anybody have a clever piece of code that shows how you
set a column to NULL. I am having a tough time to (pseudocode):




This will not work! How to do it?

TIA

Anders

 
 
 

Dynamic SQL -Howto set a col to NULL

Post by Keith Kratochvi » Tue, 12 Jun 2001 23:41:32


I do not see the need for dynamic SQL here.

--
Keith
==============
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


> Hello!

> Does anybody have a clever piece of code that shows how you
> set a column to NULL. I am having a tough time to (pseudocode):




> This will not work! How to do it?

> TIA

> Anders


 
 
 

Dynamic SQL -Howto set a col to NULL

Post by Keith Kratochvi » Tue, 12 Jun 2001 23:42:46


Here is code that will work:

UPDATE YourTable SET SomeColumn = NULL

--
Keith
==============
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


> Hello!

> Does anybody have a clever piece of code that shows how you
> set a column to NULL. I am having a tough time to (pseudocode):




> This will not work! How to do it?

> TIA

> Anders

 
 
 

Dynamic SQL -Howto set a col to NULL

Post by Andrew J. Kell » Tue, 12 Jun 2001 23:38:05


Can you post the real code so we can see if there is an error in the code.

--
Andrew J. Kelly,  SQL Server MVP
Targitmail


> Hello!

> Does anybody have a clever piece of code that shows how you
> set a column to NULL. I am having a tough time to (pseudocode):




> This will not work! How to do it?

> TIA

> Anders

 
 
 

Dynamic SQL -Howto set a col to NULL

Post by Zachary Well » Tue, 12 Jun 2001 23:40:45


Try this:


Zach


> Hello!

> Does anybody have a clever piece of code that shows how you
> set a column to NULL. I am having a tough time to (pseudocode):




> This will not work! How to do it?

> TIA

> Anders

 
 
 

Dynamic SQL -Howto set a col to NULL

Post by Tibor Karasz » Tue, 12 Jun 2001 23:37:24


Anders,

Why not below?

Update table_x set col_1 = NULL

Or, (rephrased question):
Why use dynamic SQL in this code. (Btw, that should also work...)

--
Tibor Karaszi, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com


> Hello!

> Does anybody have a clever piece of code that shows how you
> set a column to NULL. I am having a tough time to (pseudocode):




> This will not work! How to do it?

> TIA

> Anders

 
 
 

Dynamic SQL -Howto set a col to NULL

Post by Zachary Well » Wed, 13 Jun 2001 02:17:04



Hello Again!

1st:Thanx a bunch for the replies, didn't think my little question
would get all the attention.

You're welcome.

2st:I don't appreciate replies questioning my intentions i e 'why
would you wanna do that for?' Let's just say I want to do it for the
heck of it! :-)

Don't take it personal. Many times when a user posts a question and the
"why" is asked, it makes the poster realize that the flaw was in their
design/premise. Changing the premise would solve the problem. Just had one
of those today in fact.

Zach

[other stuff snipped]

 
 
 

Dynamic SQL -Howto set a col to NULL

Post by Keith Kratochvi » Wed, 13 Jun 2001 03:15:51


(1) no problem
(2) Many of us (myself included) do not like the use of dynamic sql.  We
question "why" because we want to help you make the correct decision.

Have you considered the security and performance impact of dynamic sql?

Run this example:
use pubs
go
create table example (col1 varchar(20), col2 varchar(20))
go
insert into example values ('test', 'test1')
insert into example values ('more', 'test2')
GO

as

go

exec TheExample 'select * from example'
go
exec TheExample 'update Example SET Col1 = NULL where col2 = ''test1'''
go
exec TheExample 'select * from example'
go
exec TheExample 'select * from example;truncate table example'
go
exec TheExample 'select * from example'
go
drop table example
go

Can you see the problems that this migiht cause?
Also, SQL Server is not able to optimize (and store) the query plan....so
you might have reduced performance.

Read more here (thanks to Erland Sommarskog for posting this)
http://www.algonet.se/~sommar/dynamic_sql.html

Remember, we are VOLUNTEERS that are here to help.
We are not always right
Each of us has a particular style
But the main goal is HELPING OTHERS.

--
Keith
==============
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Hello Again!

1st:Thanx a bunch for the replies, didn't think my little question
would get all the attention.

2st:I don't appreciate replies questioning my intentions i e 'why
would you wanna do that for?' Let's just say I want to do it for the
heck of it! :-)

<snip>

 
 
 

Dynamic SQL -Howto set a col to NULL

Post by Tibor Karasz » Thu, 14 Jun 2001 16:13:49


Quote:> The question remains...Is there a way to set a column to the value
> of NULL using dynamic SQL? If so please show me how...

EXEC('UPDATE tblname SET colname = NULL')
--
Tibor Karaszi, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com


Hello Again!

1st:Thanx a bunch for the replies, didn't think my little question
would get all the attention.

2st:I don't appreciate replies questioning my intentions i e 'why
would you wanna do that for?' Let's just say I want to do it for the
heck of it! :-)

3st: Included is a little general multi-purpose sp I devised mainly
for experimental reasons, that is: What if you did everything that
you're not supposed to do, what impact on response time would you get?
(Guessing the query optimizer is having a hell of a time to come up
with an execution plan for this one...)

The general idea is to have the basic operations of 'SELECT', INSERT, UPDATE,
DELETE in one sp. Depending on what you're feeding it with you could
probably do about 90% of the usual tedious chores with it.

Thus the little problem...In this concept everything works fine until
you want to set a column to the value of NULL dynamically, through a
variable or otherwise. Maybe I'm barking up the wrong tree, and should
consider a different solution altogheter, but...

The question remains...Is there a way to set a column to the value
of NULL using dynamic SQL? If so please show me how...

Once again,

TIA

Anders

 
 
 

1. HOWTO: Set a Parameter to NULL?

In VB 6/MSSQL 6.5, I am inserting a record into a table using an ADO command
object with an insert statement (see example below). I set the parameter
value using the CreateParameter method. This works fine for columns that
have valid values, but how do I set a column to NULL? Help!

Thanks in advance!

Jamie Schatte
Millennia Systems

www.millenniasystems.com

EXAMPLE: How do I set the EndDate column to NULL?

    Set adoCommand = New ADODB.Command
    With adoCommand
        .ActiveConnection = ConnectionString
        .CommandType = adCmdText
        .CommandText = "INSERT INTO MeterInformation (MeterID, StartDate,
EndDate) VALUES (?, ?, ?)"
        .Parameters.Append .CreateParameter(, adInteger, , , MyMeterID)
        .Parameters.Append .CreateParameter(, adVarChar, , 30, MyStartDate)
        If MyEndDate <> "1/1/9999 12:00:00 AM" Then
            .Parameters.Append .CreateParameter(, adVarChar, , 30,
MyEndDate)
        Else
            .Parameters.Append .CreateParameter(, adVarChar, , 30, ?????)
' EndDate should be NULL here!
        End If
        .Execute
    End With

2. How to run an Access module from VB6 program using ADO?

3. nulls in a not-null col?

4. dateformat mmddyyyy ddmmyyyy

5. SET NULL / SET NOT NULL

6. Available Parttime PB/Sybase Programmer

7. comparing date in SQL

8. HOWTO: Declare table field having Default Value - NULL or NOT NULL

9. Howto Get Result set (at least one please) from SP using T-SQL

10. HowTo: set cursor size with Transact-SQL

11. Updating a text col with NULL corrupts row...