CREATE PROCEDURE [dbo].[FetchUserWisePins]
(@FirstDate datetime,@SecondDate datetime,@mTag int)
AS
BEGIN	
 
--Parameters @mTag = 0 -All, 1 - Paid Users, 2 - Unpaid Users
--JoinType = 0 - Unpaid Users, 1 - Paid user, 2 - Paid user

IF @mTag =0  --All Users
BEGIN
  Select mName,Salary from CustMaster Where JoinType in (0,1,2) -- JoinType is Int Field
END

IF @mTag =1  --Paid users
BEGIN
  Select mName,Salary from CustMaster Where JoinType in (1,2) -- JoinType is Int Field
END


IF @mTag =2  --UnPaid users
BEGIN
  Select mName,Salary from CustMaster Where JoinType in (0) -- JoinType is Int Field
END

END

quote:

---

Originally posted by chadmat

You need to parse out the Ints from the string if you want to pass it in as a string.

-Chad

---

quote:

---

Originally posted by chadmat

What is your requirement? I see a stored proc, and something you were trying that didn't work.

You could build the query string and do an sp_executesql or just an exec.


-Chad

---

CREATE PROCEDURE [dbo].[FetchUserWisePins]
(@FirstDate datetime,@SecondDate datetime,@mTag VARCHAR(32))
AS

Select mName,Salary from CustMaster Where 
	','+@mTag+',' LIKE '%,'+CAST(JoinType AS VARCHAR(32))+',%'

GO

Declare @MyType nvarchar (10)
Set @MyType = Case When @mTag = 0 then '0,1,2'
When @mTag = 1 then '1,2'
When @mTag = 2 then '0' ELSE '0' END

quote:

---

Originally posted by sunitabeck

If you have only these three cases, I would stick with the query you already have. An alternative - which is simple to do, but probably not great from a performance perspective is as follows:

CREATE PROCEDURE [dbo].[FetchUserWisePins]
(@FirstDate datetime,@SecondDate datetime,@mTag VARCHAR(32))
AS

Select mName,Salary from CustMaster Where 
	','+@mTag+',' LIKE '%,'+CAST(JoinType AS VARCHAR(32))+',%'

GO

You would then set the parameter like this:

Declare @MyType nvarchar (10)
Set @MyType = Case When @mTag = 0 then '0,1,2'
When @mTag = 1 then '1,2'
When @mTag = 2 then '0' ELSE '0' END

Alternatively, you can use dynamic SQL (sp_executesql) but will need to be careful to reduce the risk of SQL injection attacks.

---

quote:

---

But what I wish to try like :

Select mName,Salary from CustMaster Where JoinType in (@MyType)

is this possible without exec or sp_executesql

---