sp with input parameters
-
I have a stored procedure which has a parameter @user_id
If i eneter @user_id the stored procedure should return data for that user_id only,But if i wont enter any user_id it should calculate data for all the users.
CASE1:
EXEC sp_abc 45
It should calculate for user_id 45
CASE2: EXEC sp_abc
IT should calculate for all the users in the system
how can i implement this.
Thanks.
-
Try this:
CREATE Procedure sp_abc
(
@Userid int =0
 
As set nocount on
if @userid=0
Begin
Calculate data for all users
End
Else
Begin
Calculate data for user_id=@userid
End
-
Within the stored procedure set the @user_id to have a default value, check for this default value, if it's the default run one query, it it's the other run the other with the @user_id parameter within the query.
-
thanks for ur help
-
If your procedure is doing a single select statement, you can do it without an IF block:
create procedute sp_abc
@userid int null
AS
SELECT * FROM users WHERE users.userid = @userid OR @userid IS NULL
This will keep your code nice and clean and save having to maintain two select statements. Of course, if you wanted to do more intensive work than a select, you can always use the IF/ELSE blocks like Bavineni suggested.
Julian Kuiters
juliankuiters.id.au
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply