CODEWITHSUNDEEP
sqlsql-serversql-server-2008
SterlinkArcher
SterlinkArcher

Reputation: 731

Stored procedure setting a value if row is empty

How would I go about setting a value if a row is empty ('')?

I was thinking something like,

Got var with default value called @defaultValue to set it where the row in a table is ''.

if (select col1 from table1 where col1 = '')
set (select col1 from table1 where col1 = '') = @DefaultValue

is there a better way?

code is just a draft its not even tested..

Upvotes: 1

Views: 78

Answers (2)

vhadalgi
vhadalgi

Reputation: 7189

select case when col1 ='' then @DefaultValues else col1 end from table

DEMO

declare @default int
set @default=1

declare @tbl table(col1 int)
insert into @tbl values(1),(''),(2)

select case when col1='' or col1 is null then @default else col1 end from @tbl

Upvotes: 0

Raging Bull
Raging Bull

Reputation: 18747

If you want to update the table with @DefaultValue, you can use WHERE clause in the UPDATE query:

UPDATE table1
SET col1=@DefaultValue
WHERE col1=''
   OR col1 IS NULL

OR

If you are trying to select @DefaultValue if the column is empty or null, you can do this:

SELECT CASE WHEN (col1 IS NULL OR col1='') 
              THEN @DefaultValue
              ELSE col1
            END AS Col1
FROM table1

Upvotes: 1

Related Questions