SELECT QUERY SQL WITH MANY ARGUMENT

Question

0.00/5 (No votes)

See more:

I have data list like below.
'TED00000,TED00001,TED00002,TED00003'

i want to execute through query sql to filter the data, so the identity of the data is shown like below:
=============================
NO | Identity | Description |
-----------------------------
1 | TED00000 | Book |
-----------------------------
2 | TED00001 | Bag |
-----------------------------
3 | TED00002 | Eraser |
-----------------------------
4 | TED00003 | Pencil |
=============================
and i use this Stored Procedure like below :

SQL

USE [NewInSys]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
ALTER PROCEDURE [dbo].[AdvanceSearch]
	@sIdentity VARCHAR(MAX)=NULL, @sDatabaseId VARCHAR(MAX)=NULL
 
AS
SELECT Identity From table Where Identity=@sIdentity AND DatabaseID = @sDatabaseId

then, i try to execute Stored Procedure like below :

SQL

exec spAdvanceSearch @sTerminalID=N'TED00000,TED00001,TED00002,TED00003'@sDatabaseID=N'101'

SQL

but i didn't get the result as i want.
is there any trouble with this step in the execution of stored procedure?

Posted 24-Jan-15 20:59pm

Member 11057420

Add a Solution

Comments

Zoltán Zörgő 25-Jan-15 3:00am

1) Why have you deleted the original one? I have provided answer to the original question. I don1t want to rewrite it! Sorry.
2) Don't SHOUT!
3) You don't try to pass many arguments, you pass multivalued argument.

Member 11057420 25-Jan-15 3:21am

@zolta Zorgo,
im sory, because of my mistake when i ask question. so i edit it :-), wold u answer the question again ? by given sample code

2 solutions

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)

Kornfeld Eliyahu Peter · Accepted Answer · 2015-01-24T21:02:00

Solution 1

What your query does is check if Identity IS N'TED00000,TED00001,TED00002,TED00003', which obviously not...
What you probably meant to do is check if Identity IN N'TED00000,TED00001,TED00002,TED00003'...
Please check how to use the SQL IN operator: http://www.w3schools.com/sql/sql_in.asp[^]

Posted 24-Jan-15 21:02pm

Kornfeld Eliyahu Peter

Comments

Member 11057420 25-Jan-15 3:26am

Thanks u Kornfeld,this work

Kornfeld Eliyahu Peter 25-Jan-15 3:27am

You are welcome!

**Zoltán Zörgő** · Accepted Answer · 2015-01-24T21:02:00

Take a look here: http://sqlmag.com/t-sql/passing-multivalued-variables-stored-procedure[^].
This is an other option: http://www.techrepublic.com/blog/the-enterprise-cloud/passing-table-valued-parameters-in-sql-server-2008/[^]
You can also use a construct with LIKE operator. IN won't work with parameters. You can use dynamic sql... and so on. It looks you have the most complete list here: http://stackoverflow.com/questions/43249/t-sql-stored-procedure-that-accepts-multiple-id-values[^]