Check if record exists

Hello,

I created the following SQL script to check if a record exists:

IF (EXISTS (SELECT LevelName FROM dbo.by27_Levels WHERE LOWER(@.LevelName) = LOWER(LevelName)))
Return (1)
ELSE
Return (0)

And I also found in a web page another solution:
IF EXISTS(SELECT 1 FROM TABLENAME WHERE LevelName=@.LevelName)
SELECT 1
ELSE
SELECT 0

- Which approach should I use?
- Why "SELECT 1 FROM"?
- And when should I use SELECT or RETURN?

All I need is to know if the record exists ... nothing else.

I will use this procedure on an ASP.NET 2.0 / C# web site.
I am not sure if this important but anyway ...

Thank You,
Miguel

select 1 from table returns a value which is basically the same as selecting a column name when evaluating from the exists function. The difference is that 1 is a constant so the column name does not need to be looked up, and since you do not need the value of the column then select 1 can be used.

using return or select depends on what you are using to call the sql statement. If you use return then you need to look into the calls returns parameters. Using a select , you need to use a scalar or dataset return call. Most people use the select call because those calls are easier to handle but not necessarily more efficient

|||

In EXISTS you can use any of them but the result will be the same (it always look for first occurrence of value selected) I do not know about time of execution but I would prefer something like this

RETURN (CASE
when EXISTS (SELECT LevelName
FROM dbo.by27_Levels
WHERE LOWER(@.LevelName) = LOWER(LevelName))) then 1
else
0
end)

If it will be executed on SQL server and you server is Case insensitive you do not have to use LOWER and it will speed up a little.

Thanks

|||

ozkary:

If you use return then you need to look into the calls returns parameters.

What do you mean to look the calls returns parameters?

Can you point to some info about it?

Thanks,.

Miguel

|||

By default SQL Server is not case sensitive so the LOWER() is not needed.

If the LevelName is a unique key for the table I would avaoid using T-SQL and use a single generic SQL query:

SELECT COUNT(*) FROM dbo.by27_Levels WHERE @.LevelName = LevelName

RETURN ends the execution of the batch T-SQL and SELECT does not. Note any select results not stored in local variables will be output.

|||

Some stored procedures could have return parameters they are defined with OUTPUT

create procedure TEST

@.tcParam1 as varchar(100) = NULL,

@.tcOutputParam as varchar(100) = NULL OUTPUT

AS

BEGIN

...

SET @.tcOutputParam= 'result'

END

and if you call it

declare @.oparam as varchar(100)

exec test 'Test valuee', @.oparam OUTPUT

you can get output value from procedure

RETURN always is returned by stored procedure and you can get it like:but it only integer, output parameter can be almost any type

EXEC @.result = test 'Test valuee', @.oparam OUTPUT

Thanks

|||

yes, one usually uses parameters to call a stored procedure. Those parameters can have the following directions: INPUT, OUTPUT, RETURN.

To hadle a return value, one needs to add a return parameter to the call:

SqlCommand cmd = new SqlCommand("myProc", myConnection)

cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters..Add("ReturnValue", SqlDbType.Int).Direction = ParameterDirection.ReturnValue

add block to make the call

if using a reader make sure to close it before trying to read the return parameter

read the return parameter value

string value = cmd.Parameters.item["ReturnValue"].Value.Tostring();

for more info search on ParameterDirection.ReturnValue

I hope this helps.