Problem with Stored Procedure

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

I have created a stored procedure in SQL server 2008:-

SQL

CREATE PROCEDURE usp_select
@ProfcenCd NVARCHAR(3) 
AS
SELECT * FROM Profcen  WHERE PROFCEN_CD IN (@ProfcenCd)
GO

When i execute sp with a single value then it works correct and display record. E.g.:-
EXEC sp_select '1'

but when i pass multiple values then it doesn't display any record. 
EXEC sp_select '1,8'

Need Suggestion

Posted 20-May-13 23:22pm

Mayank Topiwala

Updated 21-May-13 3:37am

gvprabu

v2

Add a Solution

Comments

Prasaad SJ 21-May-13 5:26am

May I know what is question in Detail?? You are using only one arguments then you will be allowed to use the PROC only with one arguments.

Mayank Topiwala 21-May-13 5:46am

Hi Prasad
Please look into select query. It contains IN keyword.
For this I need to pass multiples value in a single argument.

gvprabu 21-May-13 9:53am

you can go for Dynamic Query.... I think it will easy for u in this case.

4 solutions

Solution 1

Of course not, it thinkt that you're going to get record 1.8/1,8.

You need to alter the procedure so that you pass it a list of ids - or simply call the SP twice!

Posted 20-May-13 23:24pm

Johnny J.

Updated 20-May-13 23:30pm

v2

Solution 3

Hi,

You can use Dynamic Query as follows...

SQL

CREATE PROCEDURE usp_select
      @ProfcenCd NVARCHAR(300)  -- 3 size is not enof for multiple values
AS
BEGIN
	DECLARE @SqlSQuery VARCHAR(max)
	SELECT @SqlSQuery = 'SELECT * FROM Profcen WHERE PROFCEN_CD IN ('+@ProfcenCd+')'
	PRINT @SqlSQuery 
	EXEC(@SqlSQuery)
END
GO

Check the link also....
Execute Dynamic SQL commands in SQL Server[^]

Posted 21-May-13 3:41am

gvprabu

Solution 4

have a look here: Using comma separated value parameter strings in SQL IN clauses[^]

Posted 21-May-13 3:43am

Maciej Los

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)

Aarti Meswania · Accepted Answer · 2013-05-20T23:33:00

create this function

SQL

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[ParseValues]
(@String varchar(8000), @Delimiter varchar(10) )
RETURNS @RESULTS TABLE (ID int identity(1,1), Val varchar(50))
AS
BEGIN
    DECLARE @Value varchar(100)
    WHILE @String is not null
    BEGIN
        SELECT @Value=CASE WHEN PATINDEX('%'+@Delimiter+'%',@String) >0 THEN LEFT(@String,PATINDEX('%'+@Delimiter+'%',@String)-1) ELSE @String END, @String=CASE WHEN PATINDEX('%'+@Delimiter+'%',@String) >0 THEN SUBSTRING(@String,PATINDEX('%'+@Delimiter+'%',@String)+LEN(@Delimiter),LEN(@String)) ELSE NULL END
        INSERT INTO @RESULTS (Val)
        SELECT @Value
    END
RETURN
END

Now edit your sp

SQL

ALTER PROCEDURE usp_select
 @ProfcenCd NVARCHAR(3) 
AS
 SELECT * FROM Profcen WHERE PROFCEN_CD IN (select val from dbo.ParseValues(@ProfcenCd,',') where val <> '')
 GO

Happy Coding!
:)