Click here to Skip to main content
15,902,921 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
DECLARE @EmpIDtte AS varchar(50)
--SET @EmpIDtte = null
SET @EmpIDtte = '42,40'
print @EmpIDtte
select * from MulitSearch_Portal where (@EmpIDtte IS NULL OR ( LegalTypeID IN (@EmpIDtte)))

What I have tried:

I m using above this query they working with passing parameter "@EmpIDtte = null" and "@EmpIDtte = '42' " ....and the same time i m passing "@EmpIDtte = '42,40' " mulit parameter., they getting error plz help..
Error: Conversion failed when converting the varchar value '42,40' to data type int
Posted
Updated 15-Jun-16 23:27pm

1 solution

There are a couple of ways of doing this. As you are already passing in the parameter as a comma-separated list ('42,40') then you can use Dynamic SQL e.g.
SQL
DECLARE @EmpIDtte AS varchar(50)
 
SET @EmpIDtte = '42,40'
 
DECLARE @sql nvarchar(max)
SET @sql  = 'select * from MulitSearch_Portal '
IF @EmpIDtte IS NOT NULL 
	SET @sql = @sql + 'WHERE LegalTypeID IN (' + @EmpIDtte + ')'

EXEC sp_sqlexec @sql

Alternatively you could pass the IDs required in a table variable e.g.
SQL
CREATE TYPE [dbo].[ItemList] AS TABLE(
 [ItemID] [int] NOT NULL
)
go
DECLARE @ids ItemList 
INSERT INTO @ids (ItemID) VALUES (42), (40)

select * from MulitSearch_Portal where LegalTypeID in (SELECT ItemID FROM @ids)

More information - Table Value Parameters in SQL Server 2008 and .NET (C#)[^]

[EDIT - Option 1 with additional parameters]
DECLARE @sql nvarchar(max)
SET @sql  = 'select * from MulitSearch_Portal'
IF @EmpIDtte IS NOT NULL
	SET @sql = @sql + ' WHERE LegalTypeID IN (' + @EmpIDtte + ')'
IF @CompanyCategoryID IS NOT NULL
	SET @sql = @sql + CASE WHEN @EmpIDtte IS NULL THEN ' WHERE ' ELSE ' and ' END + ' CompanyCategoryID IN (' + @CompanyCategoryID + ')'
 
Share this answer
 
v2
Comments
venkatesh (chennai) 16-Jun-16 5:27am    
How to pass muilti parrameter values.. ?

DECLARE @EmpIDtte AS varchar(50)
SET @EmpIDtte = '41,42'
DECLARE @CompanyCategoryID AS varchar(50)
SET @CompanyCategoryID = '51'
DECLARE @sql nvarchar(max)
SET @sql = 'select * from MulitSearch_Portal '
IF @EmpIDtte IS NOT NULL
IF @CompanyCategoryID IS NOT NULL
SET @sql = @sql + 'WHERE ((LegalTypeID IN (' + @EmpIDtte + ')) and (CompanyCategoryID IN (' + @CompanyCategoryID + ')) )'

EXEC sp_sqlexec @sql
CHill60 16-Jun-16 5:37am    
I've added some information to the solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900