0

I'm fairly new to SQL. I'm trying to write a stored procedure that will check if the 'alias' is in the table, and if so return the details; if it doesn't exist it will add it. At the moment I've got it 50% working but it won't return the alias details if it does exist

DECLARE @Alias VARCHAR(MAX)

IF NOT EXISTS(SELECT * FROM [Users] where [Alias] = @Alias)
   INSERT INTO [Users] ([Alias], [Country], [Role]) 
   VALUES (@Alias, 'UK', 'User')

Can anyone tell me where I'm going wrong?

Thanks in advance

Tom

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Tom
  • 9,725
  • 3
  • 31
  • 48
  • Where is the code that is supposed to return this value? – Martin Smith Jul 31 '14 at 22:50
  • You are missing the `else` part of the condition, where you must return the details – David Zhou Jul 31 '14 at 22:51
  • Would you explain better what is going wrong? – Nizam Jul 31 '14 at 22:51
  • It isn't returning the values. I'm guessing as Martin has stated - I haven't written that part of the code. By putting an 'ELSE SELECT * FROM [Users] Where [Alias]=@Alias' sort my problem? – Tom Jul 31 '14 at 22:54
  • Do you actually only want to return it in the case it does not already exist? Or should it return the row anyway? Also you should put a unique constraint on this probably as otherwise this is not thread safe and you can get dupes. – Martin Smith Jul 31 '14 at 22:57
  • I want it to be returned either way. Ok how do I do that? – Tom Jul 31 '14 at 23:02
  • @Tom Well, we won't know if you don't try it – David Zhou Jul 31 '14 at 23:02
  • Have you at least initialise the value of `@Alias`? Syntax seems good, just that maybe there should be no line space between the `IF` condition and the `Insert` one, it should be on next line if you don't wish to use `BEGIN..END` – Nadeem_MK Aug 01 '14 at 05:35
  • @Nadeem_MK '@Alias' was initialised and is all working. Thanks a lot! – Tom Aug 01 '14 at 14:03

1 Answers1

0

It seems you want aliases to be unique - why not simply create such a constraint:

create unique index users_alias on users(alias);

Or if you really must do it via a query, use a select-into:

INSERT INTO [Users] ([Alias], [Country], [Role])
SELECT @Alias, 'UK', 'User'
FROM (SELECT 1) DUAL
WHERE NOT EXISTS(SELECT * FROM [Users] where [Alias] = @Alias)

The SELECT part of the query return 1 row if there's not already a user with the alias, or no rows otherwise.

Something to consider: You don't need a stored procedure to do this.
Something else to consider: You may not need stored procedures at all.
Something further to consider: Stored procedures might actually be a bad idea

Community
  • 1
  • 1
Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • This doesn't answer the question about returning the row. – Martin Smith Jul 31 '14 at 22:57
  • This is part of a much bigger VBA code and is to do with permissions settings. It doesn't create Alias' but stores users windows environmental logins so it can look up user details from the outlook address book at a later date. As I said - I'm new to SQL if this is a better way and more suitable then please say – Tom Jul 31 '14 at 22:59