Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 207
  • Last Modified:

defualt values

I need to create a stored procedure in the following format:
INSERT INTO dbo.Table(
       Row1,
      Row2,
      Row3,
etc


Select Row1,
      Row2,
      Row3,
etc

FROM SomeTable

--- But ---

Row1 for example in the select might be null --- how would i for example insert a 0 in that case in the insert. (row one is a numeric field).
0
vbnetcoder
Asked:
vbnetcoder
  • 5
  • 4
  • 2
  • +1
2 Solutions
 
sammySeltzerCommented:
insert into INSERT INTO dbo.Table(
       Row1,
      Row2,
      Row3,
...
...
)

Select CASE WHEN Row1 is null then 0 else end as Row1,
      Row2,
      Row3,

...
...
From sometable
0
 
eridanixCommented:
INSERT INTO dbo.Table(
       Row1,
      Row2,
      Row3,
etc.


SELECT CASE WHEN Row1 is null THEN 0 ELSE Row1 END AS Row1,
      Row2,
      Row3,
etc.

FROM SomeTable
0
 
sammySeltzerCommented:
insert into INSERT INTO dbo.Table(
       Row1,
      Row2,
      Row3,
...
...
)

Select CASE WHEN Row1 is null then 0 else Row1 end as Row1,
      Row2,
      Row3,

...
...
From sometable

Open in new window


just a slight modification.

You can perform same case statement on all of them.

You can also use COALESCE

Select COALESCE (Row1, NULL, 0) FROM SOMETABLE, ETC


0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
Ryan McCauleyCommented:
Can you just use ISNULL?

ISNULL(Row1, 0) as Row1

Open in new window


It will return Row1 unless it's null, then it will return 0
0
 
vbnetcoderAuthor Commented:
Select COALESCE (Row1, NULL, 0) FROM SOMETABLE, ETC worked for me.

Is it better or worse then using select case?

0
 
vbnetcoderAuthor Commented:
I am doing this to a date

COALESCE (CONVERT(VARCHAR(10), MC.MyDate, 101, NULL, 0)

How would i update to make empty string for null?
0
 
Ryan McCauleyCommented:
Your second value in COALESCE (the NULL one) is unnecessary - you could achieve the same thing by removing it:

COALESCE (Row1, 0) 

Open in new window


COALESCE is the exact same thing as ISNULL, but with more parameters - it just goes from parameter 1 to 2 to 3 and so on, until it finds one that's not NULL, and then it returns that. To return an empty string instead of zero, you'd change it to this:

COALESCE (Row1, '') 

Open in new window

0
 
vbnetcoderAuthor Commented:
Sorry

I am doing this currently

CONVERT(VARCHAR(10), MC.CoverageEffectiveDt, 101)

Now, i want to use COALESCE if null is returned
0
 
sammySeltzerCommented:
select coalesce(CONVERT(VARCHAR(10), MC.CoverageEffectiveDt, 101),null,'') from yourtable
0
 
sammySeltzerCommented:

@ryanmccauley is right though. It doesn't hurt anything at all but doesn't need to be there.

either above or this will suffice

select coalesce(CONVERT(VARCHAR(10), MC.CoverageEffectiveDt, 101),'') from yourtable
0
 
vbnetcoderAuthor Commented:
ty
0
 
sammySeltzerCommented:
you are very welcome
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now