I am passing one password value for either 2 or 3 or 4 or 'n' number of usernames.
How to pass the user_id dynamically to the update query ?
update user_table
set column_password = 'password value'
where user_id in ( )
-
Is this in a stored procedure, or something else?Joe– Joe2013-12-17 20:07:15 +00:00Commented Dec 17, 2013 at 20:07
-
its in the stored proceduregoofyui– goofyui2013-12-17 20:07:26 +00:00Commented Dec 17, 2013 at 20:07
-
And how is this value coming across in the parameter to the stored procedure? What's an example value of @param ?Joe– Joe2013-12-17 20:08:18 +00:00Commented Dec 17, 2013 at 20:08
-
Have not decided, should be xml.goofyui– goofyui2013-12-17 20:09:01 +00:00Commented Dec 17, 2013 at 20:09
-
@param will have a xml format of value passed from c#.net applicationgoofyui– goofyui2013-12-17 20:09:24 +00:00Commented Dec 17, 2013 at 20:09
|
Show 3 more comments
3 Answers
First create the function using this code:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[SplitIDs]
(
@List varchar(5000)
)
RETURNS
@ParsedList table
(
ID int
)
AS
BEGIN
DECLARE @ID varchar(10), @Pos int
SET @List = LTRIM(RTRIM(@List))+ ','
SET @Pos = CHARINDEX(',', @List, 1)
IF REPLACE(@List, ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @ID = LTRIM(RTRIM(LEFT(@List, @Pos - 1)))
IF @ID <> ''
BEGIN
INSERT INTO @ParsedList (ID)
VALUES (CAST(@ID AS int)) --Use Appropriate conversion
END
SET @List = RIGHT(@List, LEN(@List) - @Pos)
SET @Pos = CHARINDEX(',', @List, 1)
END
END
RETURN
END
GO
then in your stored procedure declate @UserIDs varchar(max). You will pass comma separated ID list into this param.
Then in your Stored proc you can have:
update U
set U.column_password = 'password value'
FROM dbo.SplitIDs(@UserIDs) I
INNER JOIN user_table U ON I.ID=U.user_id
CREATE A Function
CREATE FUNCTION [dbo].[FnSplit]
(
@List nvarchar(2000),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table (Id int identity(1,1), Value nvarchar(100))
AS
BEGIN
While(Charindex(@SplitOn,@List)>0)
Begin
Insert Into @RtnValue (value)
Select Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))
Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
End
Insert Into @RtnValue (Value)
Select Value = ltrim(rtrim(@List))
Return
END
Store Procedure
CREATE Procedure usp_Multipleparameter (@Users VARCHAR(1000)= NULL)
AS
BEGIN
update user_table
set column_password = 'password value'
where user_id collate database_default IN (SELECT Value FROM dbo.FnSplit(@Users,','))
END
GO
Calling Store Procedure
EXEC usp_Multipleparameter 'User1,User2'
Comments
Just a thought. If this is already in a stored procedure it is likely you already have the user_id available to you through a select statement. I would do something like this.
update user_table
set column_password = 'password value'
where user_id in ( select user_id from table
where criteria = '' )
2 Comments
goofyui
in the subquery you are mentioning the user_id. in my scenario, user_id is passed dynamically. number of user_id's will change
SQL_Matthew
Hi goofyui, the statement would handle any number of user_ids whether it was one to one million or any number for that matter based on what the sub query picks up. I believe from what I see from you above comments though that you may be looking for something else.