CODEWITHSUNDEEP
sqlsql-serversql-server-2008
goofyui
goofyui

Reputation: 3492

Dynamically updating column value in SQL

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 ( )

Upvotes: 1

Views: 1695

Answers (3)

M.Ali
M.Ali

Reputation: 69504

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'

Upvotes: 1

SQL_Matthew
SQL_Matthew

Reputation: 1

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 = '' )

Upvotes: 0

Dimitri
Dimitri

Reputation: 7013

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

Upvotes: 2

Related Questions