required stored procedure

Jak

Member

858 Points

132 Posts

i want stored procedure using in operator

create procedure SP_GetDistrictNames
@stateId nvarchar(50)
as 
begin
select*from tblDistrictMaster whereState_ID in 
(+@stateId)
end

i am getting error

Conversion failed when converting the nvarchar value 1,2,3,4 to data type int

Views: 8666
Total Answered: 2
Total Marked As Answer: 1
Posted On: 08-Sep-2014 00:51

Share:

Answers

Rahul M...

Teacher

4822 Points

23 Posts

Hi,

First create a function with following command.

create

FUNCTION [dbo].[SplitString]

(

@SplitStr

nvarchar(1000),

@SplitChar

nvarchar(5)

)

RETURNS

@RtnValue table

(

Data

nvarchar(50)

)

BEGIN

Declare

@Count int

Set

@Count = 1

While

(Charindex(@SplitChar,@SplitStr)>0)

Begin

Insert

Into @RtnValue(Data)

Select

Data

=ltrim(rtrim(Substring(@SplitStr,1,Charindex(@SplitChar,@SplitStr)-1)))

Set

@SplitStr =Substring(@SplitStr,Charindex(@SplitChar,@SplitStr)+1,len(@SplitStr))

Set

@Count = @Count + 1

End

Insert

Into @RtnValue(Data)

Select

Data =ltrim(rtrim(@SplitStr))

Return

END

And

thenuseas:

create

procedure SP_GetDistrictNames

@stateId

nvarchar(50)

begin

select

*from tblDistrictMaster where State_ID in

(

SELECT DATA FROM dbo.SplitString(@stateId,",")

end

Posted On: 08-Sep-2014 00:57

Rahul M...

Teacher

4822 Points

23 Posts

Hi,

You can use SP_ExcuteSQL procedure as:

alter

procedure SP_GetDistrictNames

(

@stateId nvarchar(50))as

begin

declare

@sql asnvarchar(1000)

set

@sql=select*from tblDistrictMaster where State_ID in(+@stateId+)

exec

sp_executesql@sql

end

Posted On: 08-Sep-2014 03:17

Discussion

SignalR for real-time web functionality

MVC Design pattern

What is the current status of artif...

Modern Web Development using ASP.NE...

Is Programming an Art or a Science?

Blazor is a UI Web framework for bu...

Blog

Steps to Manage SharePoint User Profiles Using Admin Centre

How to Show/Hide i.e. Toggle Password visibility using jQuery and JavaScript?

How do I add consent cookie component in Blazor?

Adding Azure Key Vault secrets in Azure Pipelines

required stored procedure

Discussion

Blog

Active User (4)