Re: Stored procedure with parameter varchar

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Re: Stored procedure with parameter varchar

Author

Topic

micnie_2020
Posting Yak Master

232 Posts

Posted - 2008-08-29 : 01:59:10

Dear All,

I having problem in passing a string value
it's sometimes contain 8
or 8,9,10 or 8,0,... etc

Below is my SP:

How can i get it work:-

Alter Proc SP_CountBinaryTree
--exec SP_CountBinaryTree 8
--exec SP_CountBinaryTree 0,9
--exec SP_CountBinaryTree 0,9,10

@MID AS Varchar(200)
AS
DECLARE @count AS Int
Declare @sSql AS NVarChar(4000)

Set @sSql = N'SET ''''+@count+''''='
Set @sSql = @sSql +'(select count(*) as Counts '
Set @sSql = @sSql +'from Memberships where CAST(UplineID as varchar) in ('''+ @MID +''') and MembershipID<>UplineID)'
exec sp_executesql @sSql

if (@count=1)
BEGIN
Set @sSql = N'Select MembershipID, ms.MemberCode, UplineID,legIndex '
Set @sSql = @sSql +' From Memberships ms '
Set @sSql = @sSql +' left join membershiptree mt on mt.membercode=ms.membercode '
Set @sSql = @sSql +' where CAST(UplineID as varchar) in ('''+ @MID +''') '
Set @sSql = @sSql +' UNION ALL '
Set @sSql = @sSql +' Select 0 as MID, ''Join New'' as MemberCode, 0 as UplineID , '
Set @sSql = @sSql +' case when (legIndex=1) then 2 when (legIndex=2) then 1 else 0 end '
Set @sSql = @sSql +' From Memberships ms '
Set @sSql = @sSql +' inner join membershiptree mt on mt.membercode=ms.membercode '
Set @sSql = @sSql +' where CAST(UplineID as varchar) in ('''+ @MID +''') '
Set @sSql = @sSql +' and MembershipID<>UplineID order by MembershipID '
print @sSQL
exec sp_executesql @sSql
END
else
BEGIN
Set @sSql = N'Select MembershipID, ms.MemberCode, UplineID,legIndex '
Set @sSql = @sSql +' From Memberships ms '
Set @sSql = @sSql +' inner join membershiptree mt on mt.membercode=ms.membercode '
Set @sSql = @sSql +' where CAST(UplineID as varchar) in ('''+ @MID +''') '
Set @sSql = @sSql +' and MembershipID<>UplineID '
print @sSQL
exec sp_executesql @sSql
END

Please advise

Thank you.

Regards,
Micheale

I have try simple statement like this:-
Method Illustration A:
Select MembershipID, ms.MemberCode, UplineID,legIndex
From Memberships ms inner join membershiptree mt on mt.membercode=ms.membercode
where cast(UplineID as varchar) in ((replace('0,9','''','')))
and MembershipID<>UplineID
Also no record.

MehodIllustration B (Just Testing):
Select MembershipID, ms.MemberCode, UplineID,legIndex
From Memberships ms inner join membershiptree mt on mt.membercode=ms.membercode
where UplineID in (0,9)
and MembershipID<>UplineID
This working fine, but i manuaaly key in and test in the sql Query analyzer.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-29 : 02:13:18

i dont think you reuire dynamic sql here. i guess this is what you're looking for:-

alter Proc SP_CountBinaryTree
--exec SP_CountBinaryTree 8 
--exec SP_CountBinaryTree '0,9'
--exec SP_CountBinaryTree 0,9,10

@MID AS Varchar(200) 
AS 
DECLARE @count AS Int 
Declare @sSql AS NVarChar(4000) 



select @count=count(*) as Counts from Memberships 
where '%,' + CAST(UplineID as varchar)+ ',%' LIKE  ',' + @MID + ','
 and MembershipID<>UplineID

if (@count=1)
BEGIN
Select MembershipID, ms.MemberCode, UplineID,legIndex  
From Memberships ms  
left join membershiptree mt 
on mt.membercode=ms.membercode  
where  '%,' + CAST(UplineID as varchar)+ ',%' LIKE  ',' + @MID + ',' 
UNION ALL  
Select 0 as MID, 'Join New' as MemberCode, 0 as UplineID ,  case when (legIndex=1) then 2 when (legIndex=2) then 1 else 0 end  
From Memberships ms  
inner join membershiptree mt 
on mt.membercode=ms.membercode  
where  '%,' + CAST(UplineID as varchar)+ ',%' LIKE  ',' + @MID + ','  
and MembershipID<>UplineID 
order by MembershipID 




END
else 
BEGIN

Select MembershipID, ms.MemberCode, UplineID,legIndex  
From Memberships ms  
inner join membershiptree mt 
on mt.membercode=ms.membercode  
where '%,' + CAST(UplineID as varchar)+ ',%' LIKE  ',' + @MID + ','  
and MembershipID<>UplineID 

END

micnie_2020
Posting Yak Master

232 Posts

Posted - 2008-08-29 : 02:25:34

Hi,

Thank you for your reply.

No result return. Suppose have 2 record.

P/s: Ammend on your code-remove @count=count(*) ~~as counts~~

alter Proc SP_CountBinaryTree
--exec SP_CountBinaryTree '0,9'

@MID AS Varchar(200)
AS
DECLARE @count AS Int
Declare @sSql AS NVarChar(4000)

select @count=count(*) from Memberships
where '%,' + CAST(UplineID as varchar)+ ',%' LIKE ',' + @MID + ','
and MembershipID<>UplineID

if (@count=1)
BEGIN
Select MembershipID, ms.MemberCode, UplineID,legIndex
From Memberships ms
left join membershiptree mt
on mt.membercode=ms.membercode
where '%,' + CAST(UplineID as varchar)+ ',%' LIKE ',' + @MID + ','
UNION ALL
Select 0 as MID, 'Join New' as MemberCode, 0 as UplineID , case when (legIndex=1) then 2 when (legIndex=2) then 1 else 0 end
From Memberships ms
inner join membershiptree mt
on mt.membercode=ms.membercode
where '%,' + CAST(UplineID as varchar)+ ',%' LIKE ',' + @MID + ','
and MembershipID<>UplineID
order by MembershipID

END
else
BEGIN

Select MembershipID, ms.MemberCode, UplineID,legIndex
From Memberships ms
inner join membershiptree mt
on mt.membercode=ms.membercode
where '%,' + CAST(UplineID as varchar)+ ',%' LIKE ',' + @MID + ','
and MembershipID<>UplineID

END

Please advise.

Thank you.

Regards,
Micheale

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-29 : 02:27:42

how will be your table data?

micnie_2020
Posting Yak Master

232 Posts

Posted - 2008-08-29 : 02:40:47

Hi,

Below is my table s'ture:

CREATE TABLE [Memberships] (
[MembershipID] [int] IDENTITY (1, 1) NOT NULL ,
[MemberID] [int] NULL ,
[UplineID] [int] NULL ,
[SponsorID] [int] NULL ,
[MemberCode] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DISTTREE] [int] NULL CONSTRAINT [DF__MEMBERSHI__DISTT__5F74D762] DEFAULT (0),
CONSTRAINT [PK_Memberships] PRIMARY KEY NONCLUSTERED
(
[MembershipID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [MembershipTree] (
[MembershipTreeID] [int] IDENTITY (1, 1) NOT NULL ,
[MemberCode] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF__Membershi__Membe__552C5319] DEFAULT (''),
[SponsorCode] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF__Membershi__Spons__56207752] DEFAULT (''),
[UplineCode] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF__Membershi__Uplin__57149B8B] DEFAULT (''),
[LegIndex] [int] NULL CONSTRAINT [DF__Membershi__LegIn__59F10836] DEFAULT (0),
[LegCount] [int] NULL CONSTRAINT [DF__Membershi__LegCo__5AE52C6F] DEFAULT (0),
[dateCreate] [datetime] NULL CONSTRAINT [DF_MembershipTree_dateCreate] DEFAULT (getdate()),
CONSTRAINT [MembershipTree_MembershipTreeID] PRIMARY KEY CLUSTERED
(
[MembershipTreeID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

Result suppose be:-
MID MCode UplineID LegIndex
0 Join New 0 2 (Right Leg)
9 1000000 8 1 (Left Leg)

That's all,

Thank you

Micheale

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-29 : 04:13:01

whats the purpose of below?
MembershipID<>UplineID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources