SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Stored Procedure - Variable verification
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Wobeca
Starting Member

Belgium
12 Posts

Posted - 05/25/2011 :  13:44:45  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 05/25/2011 :  14:11:36  Show Profile  Reply with Quote
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?

Edited by - sunitabeck on 05/25/2011 14:13:01
Go to Top of Page

Wobeca
Starting Member

Belgium
12 Posts

Posted - 05/25/2011 :  15:51:57  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000