I have a stored procedure like this:
CREATE PROCEDURE Resume
@UserID nvarchar(100)
AS
BEGIN
SET NOCOUNT ON;
update Candidate set Approved = 'y' where UserId_I in (@UserID)
END
GO
In this the @UserID is in string format. I send the value of parameter @UserID is 1,2,3 from code as string.
I got error as
Conversion failed when converting the nvarchar value '1,2,3' to data type int.
Please solve this
You can do it in multiple ways. You can either define a table type and pass the list of user IDs via that table type (See this question: INSERT using LIST into Stored Procedure).
You can also split up the string at the commas in a WHILE loop and add the IDs to a temporary table. Then you can use ... IN (SELECT UID FROM @tempTable). Rough algorithm:
DECLARE @tempTable TABLE (UID INT)
WHILE ([Length of @UserID > 0])
BEGIN
IF ([@UserID contains comma?])
BEGIN
[GET STRING UNTIL COMMA]
[CONVERT TO INT AND INSERT INTO @tempTable]
[REMOVE STRING AND COMMA FROM @UserID]
END ELSE
BEGIN
[CONVERT @UserID TO INT AND INSERT INTO @tempTable]
SET @UserID = ''
END
END
You can also use the EXEC approach, but I tend to not use EXEC with parameters passed from the outside world - you open your code to SQL injections. The minimum check I'd perform is whether the @UserID parameter contains only numbers, commas and spaces.
Try using temporary table which has better performance than sub query
DECLARE @userIds NVARCHAR(MAX)
SET @userIds ='1,2,3,4,5,6'
CREATE table #TempUser
(
userId Int
)
DECLARE @SplitOn VARCHAR(1)
SET @SplitOn = ','
While (Charindex(@SplitOn,@userIds)>0)
Begin
Insert Into #TempUser (userId)
Select
userId = ltrim(rtrim(Substring(@userIds ,1,Charindex(@SplitOn,@userIds )-1)))
Set @userIds = Substring(@userIds,Charindex(@SplitOn,@userIds)+1,len(@userIds))
End
Insert Into #TempUser (userId)
Select Data = ltrim(rtrim(@userIds))
SELECT * FROM #TempUser
Now you can update your records using this temporary table.
Please update using EXECUTE (Transact-SQL) command.
CREATE PROCEDURE Resume
-- Add the parameters for the stored procedure here
@UserID nvarchar(100)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
exec ('update Candidate set Approved=''y'' where UserId_I in ('+@UserID+')')
END
GO
For a splitting @UserID parameter you can use the XQuery value() method
CREATE PROCEDURE Resume
@UserID nvarchar(100)
AS
BEGIN
SET NOCOUNT ON;
UPDATE Candidate
SET Approved = 'y'
WHERE UserId_I IN
(SELECT Split.a.value('.', 'int')
FROM (SELECT CAST ('<M>' + REPLACE(@UserID , ',', '</M><M>') + '</M>' AS XML) AS val
) AS A CROSS APPLY val.nodes('/M') AS Split(a)
)
END
GO
See demo on SQLFiddle
The error occurs because you cannot use a string (@UserID) as the right-hand operator of the IN-operator. The IN-operator requires a SET as the right-hand operator - in this case you'll need a set of ints to tell whether UserId_I is part of that set.
There are two things you can do here: either use a table-value parameter for the @UserID-parameter, or write a function that converts/splits the @UserID-string into a table/set of ints, in which case you'll get something like:
...WHERE @UserId_I IN dbo.ParseIntSet(@UserId);
You can try a demo given below...
CREATE PROCEDURE [dbo].[Proc_Name] @CSVConnectivityIds NVARCHAR(MAX)
WHILE len(@CSVConnectivityIds) > 0
BEGIN
INSERT INTO tblName
VALUES (left(@CSVConnectivityIds, charindex(',', @CSVConnectivityIds + ',') - 1))
SET @CSVConnectivityIds = stuff(@CSVConnectivityIds, 1, charindex(',', @CSVConnectivityIds + ','), '')
END
I hope this will help you
inare logically different from a single string argument that happens to contain commas. SQL, in common with practically every other programming language, won't magically decide to inspect the string and change this. The proper solution to this is to not pass the string in the first place - pass (ideally) a Table-Valued parameter or an XML document - things designed to contain multiple values. No-doubt you'll get answers (or close votes) pointing towards creating a split function on the server, but it's really the wrong approach.