Stored Procedure - Variable verification

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

General SQL Server Forums

New to SQL Server Programming

Stored Procedure - Variable verification

Author

Topic

Wobeca
Starting Member

12 Posts

Posted - 2011-05-25 : 13:44:45

Hi,

I have a stored procedure, using an input variable (which works just fine), but I want to put some extra functionality to it.

When I execute the stored procedure, I want it to verify if the input variable matches another condition in the row.

So, when I enter '2' as an EmployeeNmbr (variable), I want it to verify if the given employee in the row contains the value 'Sales' under the column 'Department' of that same table.

If it does, execute the 'select statement' (which I already have working).
If it doesn't, show message ('print statement').

I can do this with the 'if - else' keywords, but I don't know what criteria to enter to verify this.

For now, I have:

@EmployeeNmbr_Variable =
(
SELECT EmployeeNmbr
FROM TblEmployees
WHERE Department = 'Sales'
)

This would work, if the query resulted in a single record, but it doesn't...

Any advice on this?

Thanks in advance!

Wobeca

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-25 : 14:11:36

If you are trying to get information about EmployeeNmbr 2 who is in Sales department, then you could do this:


select
   col1,col2, col3 -- etc. replace with actual column names
from
   TblEmployees
where
   Department = 'Sales'
   and EmployeeNmbr = @EmployeeNmbr_Variable;

Then, you can check if it returned any rows at all using @@ROWCOUNT as in


if (@@ROWCOUNT = 0) print 'No such employee in Sales!';

Now, I may not have understood your requirement exactly - especially since you say: "This would work, if the query resulted in a single record, but it doesn't...". So if this is not what you are looking for, can you give more information?

Wobeca
Starting Member

12 Posts

Posted - 2011-05-25 : 15:51:57

Thank you for your reply!

I solved it myself in the meanwhile.
What I was looking for is to verify if EmployeeNmbr (input variable) was found in selected records where the department = 'sales'.

For this I needed to use the 'IN' keyword, instead of the '=' sign.

Thank you for your input, though!

Wobeca

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources