sql server programming: Function that check value of column -- sql server programming

Re: Function that check value of column

Ajay
6/21/2005 11:00:02 PM

In stored procedures, I am having the following Select statements: There are
similar selects on other tables as well.

.....
.....
Select @v_recordcount = count(*)
From employee
where emp_name = 'Ajay'

If @v_recordcount >0 -- employee exists
begin

end
.....
.....

Now, I want to convert these selects to something like

....
.....
If dbo.check_val('employee','emp_name','Ajay') = 1 --employee exists
begin

end
.....
.....

[quoted text, click to view]

Re: Function that check value of column

David Portas
6/22/2005 12:00:00 AM

[quoted text, click to view]

This is not a good idea. Parameterized metadata cannot be optimized or
validated easily and both performance and reliability will suffer.

Use EXISTS rather than COUNT(*) to verify the presence or absence of a row:

IF EXISTS
(SELECT *
FROM employee
WHERE emp_name = 'Ajay')
BEGIN
...
END

--
David Portas
SQL Server MVP
--

Re: Function that check value of column

Jonathan Chong
6/22/2005 12:00:00 AM

Isn't that this is much straightforward and explicitly explains what it
intend to do:

IF EXISTS(SELECT emp_name FROM employee WHERE emp_name = 'Ajay')

THEN

If dbo.check_val('employee','emp_name','Ajay') = 1

[quoted text, click to view]

Re: Function that check value of column

Uri Dimant
6/22/2005 12:00:00 AM

Ajay
Stored Procedure or CHECK Constraints

What are you really trying to do?

[quoted text, click to view]

Re: Function that check value of column

--CELKO--
6/22/2005 10:35:23 AM

[quoted text, click to view]

This is a violation of the basic principles of software engineering.
It is a more fundamental mistake than merely bad SQL programming. Look
up the word cohesion in any beginning software engineering book.

Re: Function that check value of column

Raymond D'Anjou
6/22/2005 2:08:37 PM

IF @table_name = 'employees'
BEGIN
IF @col_name = 'emp_last_name'
BEGIN
IF EXISTS(SELECT * FROM employees WHERE emp_last_name =
@val)
RETURN 1
ELSE
RETURN 0
END
... repeat for each column that can be searched
END
....repeat for each table that can be searched

As you can imagine, this could be one monster of a UDF.
Do-able, but I'd never touch this.

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.