Stored Procedure Input value - SQL Server Forums

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 Input value

Author

Topic

Wobeca
Starting Member

12 Posts

Posted - 2011-05-24 : 16:53:07

Hi there,

I want to create a stored procedure, but upon executing it, I want it to ask for an input which is then used as a variable to define my query in this stored procedure.

Some code to make it clearer:

CREATE PROCEDURE [dbo].[sp_TEST]
AS
BEGIN

DECLARE @EmployeeID_Input int

SELECT EmployeeID
FROM dbo.TblEmployees
WHERE EmployeeID = 5

END
GO

How can I make the stored procedure ask for an input value (int), so that the query isn't defined by the condition '5', but with an integer value that I can put in at the moment of executing the stored procedure?

Is this possible?

Thanks for any feedback!

Greetz!

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2011-05-24 : 16:54:43

you declare an input variable in the stored proc as:

CREATE PROCEDURE [dbo].[sp_TEST] (@Employee_ID int)
AS
BEGIN

and then use it in your query as :

SELECT EmployeeID
FROM dbo.TblEmployees
WHERE EmployeeID = @Employee_Id

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2011-05-24 : 16:56:25

by the way, do not name your stored procedures starting with "sp_xx". SQL Server assumes its a system procedure and looks in master db first, and then in the database you are execting the proc. So this is an unnecessary overhead. Use something like "usp_xx" etc.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Wobeca
Starting Member

12 Posts

Posted - 2011-05-24 : 17:26:23

Thank you very much, it worked immediatly!
I also adapted the name from the procedure from 'sp_xx' to 'usp_xx' as you advised. ;)

Thanks again!

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2011-05-24 : 20:26:30

welcome..

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-05-25 : 11:04:40

If you use "usp_" for a procedure are you going to use something like "vw_" for views and "udf_" for fucntions and "tbl_" for tables and ....?

I agree that you should not use "sp_" but adding hungarian notation to your database objects isn't the answer. Whatever you do, be consistant. But really, this is the yer 2011 and we should all be past these poor naming conventions by now.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources