Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

T-SQL how to use IF ELSE

Posted on 2009-04-16
10
Medium Priority
?
1,072 Views
Last Modified: 2012-05-06
Dear Experts,

How to you make the following store procedure work?

Also whats a good book to buy for t-sql I don't know much tsql syntax. Thanks :)

Basically, I don't know how to use IF and ELSE correctly in SP and also how do you convert the "answer" from unixtimestamp into this format: yy/mm/dd


THANK YOU :)
CREATE PROCEDURE [dbo].[spTest1] 
 
AS
BEGIN
 
	SET NOCOUNT ON;
 
SELECT red,  
    
IF red IS NOT NULL AND red != '' AND red != 'ReD' THEN 
blue AS answer,  -- need to convert this from unixtimestamp into yy/mm/dd
ELSE 
yellow AS answer,
END
 
pink
 
FROM colors
 
END

Open in new window

0
Comment
Question by:sqlnoob
  • 7
  • 2
10 Comments
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24155255
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24155274
you can do something like



CREATE PROCEDURE [dbo].[spTest1] 
 
AS
BEGIN
 
        SET NOCOUNT ON;
 
SELECT red,CASE 
WHEN red IS NOT NULL AND RED <>'' AND RED != 'ReD' 
THEN blue  
else yellow
END AS answer,
pink
from colors    
 
END

Open in new window

0
 
LVL 31

Accepted Solution

by:
RiteshShah earned 2000 total points
ID: 24155311
I created one sample table for named color and checked above SP which is working fine. have a look.



create table colors
(
red varchar(10),
blue varchar(10),
yellow varchar(10),
pink varchar(10),
)
 
 
insert into colors
select 'r','b','y','b' union all
select NULL,'b','y','b' union all
select '','b','y','b' union all
select 'ReD','b','y','b' 
 
CREATE PROCEDURE [dbo].[spTest1] 
AS
BEGIN
SET NOCOUNT ON;
 
SELECT red,CASE 
WHEN red IS NOT NULL AND RED <>'' AND RED != 'ReD' 
THEN blue  
else yellow
END AS answer,
pink
from colors    
END
 
 
exec spTest1

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 8

Expert Comment

by:vinurajr
ID: 24156048
Please try this...
declare @Colors Table(sno int identity,colorV varchar(max),colorI varchar(max),colorB varchar(max),colorG varchar(max))
 
insert into @Colors
select 'VIOLET','INDIGO','BLUE','GREEN' 
union all
select NULL,'INDIGO','BLUE','' 
union all
select '',NULL,'BLUE','GREEN' 
union all
select 'VIOLET','','BLUE',NULL
 
SELECT * FROM @Colors
 
SELECT sno,colorV,  
case when colorV IS NOT NULL AND colorV != '' AND colorV != 'VIOLET' THEN colorI ELSE colorB end AS answer,colorG
 
FROM @colors
 

Open in new window

0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24156094
WHile providing the solution to you, I forgot your another question. There are many good books for SQL Server
"SQL Server 2005 Bible." and "SQL Server black book" to name a few.

BTW, I was googling something and found below given link which will help you to download books.

http://knigaonline.net//BooksCatalog.aspx?category=Data%20Bases

0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24156097
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24156169
hi vinurajr,

have you given anything different than I have explained?
0
 

Author Comment

by:sqlnoob
ID: 24163796
hi,

thanks for that. how do you convert the "answer" from a unixtimestamp into a dd/mm/yy format?

thank you :)
0
 

Author Comment

by:sqlnoob
ID: 24163867
hi,

added:

(SELECT DATEADD(second, CAST blue AS int), '19700101'))

how do i convert the above into mm/dd/yy format?

thanks
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24164491
here you are.

declare @unixdate varchar(10)
set @unixdate='19700101'
select convert(datetime, substring(@unixdate,5,2) + '/'+right(@unixdate,2) +'/'+ left(@unixdate,4) )
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question