if ( SELECT statement with variable) how to ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

if ( SELECT statement with variable) how to ?

Author

Topic

marconi8
Yak Posting Veteran

73 Posts

Posted - 2003-05-01 : 09:15:19

i'am trying to make if ( select statement ) in which i can get a variable

here is my wrong code....

IF ( SELECT @CITY_ID = ID
FROM MAIN_CITY
WHERE UPPER(CITY_NAME)=UPPER(@CITY)
) IS NULL

in declared variable @CITY_ID i'am hope to get values null or integer

how to correctly to make this

thanks :)

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-05-01 : 09:29:17

DECLARE @MAIN_CITY TABLE ([ID] INTEGER, CITY_NAME NVARCHAR(25))
DECLARE @CITY_ID INTEGER
DECLARE @CITY NVARCHAR(25)

SELECT @CITY = 'New York'

INSERT INTO @MAIN_CITY([ID],CITY_NAME)
SELECT 1,'New York'

SELECT @CITY_ID = [ID]
FROM @MAIN_CITY
WHERE CITY_NAME = @CITY

SELECT @CITY_ID

SELECT @CITY_ID = NULL

SELECT @CITY = 'Boston'

SELECT @CITY_ID = [ID]
FROM @MAIN_CITY
WHERE CITY_NAME = @CITY

SELECT @CITY_ID

marconi8
Yak Posting Veteran

73 Posts

Posted - 2003-05-01 : 09:55:28

thanks for reply, i have one more question ....

i am newbie to mssql and i cannot understand it is your post realy that i need or not...

i only must to make this

create procedure ex_1
@city varchar(20)
as

declare @CITY_ID INT
begin
/*
at this place i cannot make any inserts, only after IF
conditions
*/
IF ( SELECT @CITY_ID = ID
FROM MAIN_CITY
WHERE UPPER(CITY_NAME)=UPPER(@city)
) IS NULL
BEGIN
INSERT INTO table_name (CITY_NAME) VALUES(@city)
SET @CITY_ID = IDENT_CURRENT(table_name)
END

RETURN @CITY_ID

this simple code i cannot write in mssql i stuck with syntax with this block of code

IF ( SELECT @CITY_ID = ID
FROM MAIN_CITY
WHERE UPPER(CITY_NAME)=UPPER(@city)
) IS NULL

one word said i simply want to place into if conditions this
statement

SELECT @CITY_ID = ID
FROM MAIN_CITY
WHERE UPPER(CITY_NAME)=UPPER(@city)

end

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-05-01 : 11:59:57

Here are two ways of doing it

DECLARE @MAIN_CITY TABLE ([ID] INTEGER, CITY_NAME NVARCHAR(25))
DECLARE @CITY_ID INTEGER
DECLARE @CITY NVARCHAR(25)

SELECT @CITY = 'New York'

INSERT INTO @MAIN_CITY([ID],CITY_NAME)
SELECT 1,'New York'

/*Method 1*/
IF EXISTS(SELECT [ID]
FROM @MAIN_CITY
WHERE CITY_NAME = @CITY )
PRINT 'Exists'

/*Method 2*/
SELECT @CITY_ID = [ID]
FROM @MAIN_CITY
WHERE CITY_NAME = @CITY

IF @CITY_ID IS NULL
PRINT 'IS NULL'

You can even do an insert from a join but I don't want to confuse you. If your interested read these articles
http://www.sqlteam.com/item.asp?ItemID=448
http://sqlmag.com/Articles/Index.cfm?ArticleID=8808
http://www.sqlteam.com/item.asp?ItemID=3876

Edited by - ValterBorges on 05/01/2003 12:00:41

Edited by - ValterBorges on 05/01/2003 12:01:29

Edited by - ValterBorges on 05/01/2003 12:05:41

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources