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

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!
:)

Problem with Stored Procedure

4 solutions

Solution 2

Solution 1

Solution 3

Solution 4

Add your solution here

Preview 0

Problem with Stored Procedure

4 solutions

Solution 2

Solution 1

Solution 3

Solution 4

Add your solution here

Preview 0

Existing Members

...or Join us