How to pass multisearch parameter for SQL select query..?

Question

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 15-Jun-16 21:51pm

venkatesh (chennai)

Updated 15-Jun-16 23:27pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

CHill60 · Accepted Answer · 2016-06-15T22:47:00

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