Stored Procedure...

babloo81 · May 1st, 2004, 08:26 AM

Hi,
How do I write a stored procedure for the below SQL Query.

select UserID,UserName from [User] where UserID in (1,4,5)

Meaning: I would like to have a way for my Stored Procedure to pass 1,4,5 as a parameter. The number of IDs is going to be dynamic... Can I have a text as param and a mechanism in which i can do a split and ...
Please Help!

Advice,
Babloo

jemacc · May 1st, 2004, 07:15 PM

You may want to try something like this, hopefully it will give you and idea.

CREATE PROCEDURE get_User_info
@UserName varchar (20)
@userID Int output
as
SELECT @UserName= UserName
FROM [user]
WHERE UserID = @UserID
return
go

melvik · May 1st, 2004, 11:25 PM

sure u can! i guess u need something like this:

Code:

CREATE PROCEDURE dbo.ProcName
@Users varchar (50)
AS
 DECLARE @SelectText varchar(500)
  SET @SelectText = 'SELECT UserID,UserName FROM [User] WHERE UserID in('+@Users+')'
  EXECUTE (@SelectText)
GO

HTH.

Always:),
Hovik Melkomian.