May 30, 2012 at 12:38 pm
Hi I need to create report where user checks by entering multiple phone numbers and checks whether those number exists or not and I need to display by saying match found and not found along with phone numbers he enters...
For example I have these phone numbers in my table
2018087621
2018267812
2018464973
2018562444
2018661915
2018660946
2018369037
2018563128
2018552019
2018562796
If I pass ‘2018087621’ in the parameter I get results like this
Phone NumberMatch Found
2018087621Match Found
If I enter wrong phone number '2018087620’ I get results like this
Phone NumberMatch Found
2018087620Match not Found
But If I wanted to enter multiple values like ‘2010087620’ and 2010087621’ I need to get results like this
Phone NumberMatch Found
2018087621Match Found
2018087620Match not Found
I am able to do first two scenarios and not sure how to get the third one..
any help would be appreciated !
Thanks
SG
May 30, 2012 at 1:09 pm
How are you getting this parameter? Is it like a comma separated list?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 30, 2012 at 1:14 pm
I cretaed a stored procedure like this..
select
case when @Phonenumber in (select
cast(cast(PhoneNumber as bigint) as varchar(10))PhoneNumber
from dbo.DoNotCallList where PhoneNumber in ( select param from dbo.fn_MVParam(@Phonenumber,',')))
Then @Phonenumber
else @Phonenumber end Phonenumber,
case when @Phonenumber in (select
cast(cast(PhoneNumber as bigint) as varchar(10))PhoneNumber
from dbo.DoNotCallList where PhoneNumber in ( select param from dbo.fn_MVParam(@Phonenumber,',')))
Then 'Match Found'
else 'No Match Found' end Match
May 30, 2012 at 1:32 pm
From what you posted I can't even begin to help. You have a number of tables and functions that I have no idea what they look like. Remember I can't see your screen from here. If you want some real help you need to post ddl, sample data and desired output. see the first link in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 31, 2012 at 3:42 pm
Try this out. http://msdn.microsoft.com/en-us/library/bb510489.aspx
May 31, 2012 at 11:54 pm
How about something like this?
--Creating Table
Create Table Ex
(PhoneNo bigint)
--Inserting Sample Data
Insert Into Ex
Select 2018087621
Union ALL
Select 2018267812
Union ALL
Select 2018464973
Union ALL
Select 2018562444
Union ALL
Select 2018661915
Union ALL
Select 2018660946
Union ALL
Select 2018369037
Union ALL
Select 2018563128
Union ALL
Select 2018552019
Union ALL
Select 2018562796
--Creating Procedure
Create Procedure dbo.Proc_CheckPhNo
@temp bigint,
@temp1 bigint
As
Begin
Declare @temptable Table(PhoneNo bigint)
Insert Into @temptable
Select @temp
Union ALL
Select @temp1
Select PhoneNo,
(Case When PhoneNo IN (Select * From @temptable Except Select * From Ex) Then 'Match Not Found' Else 'Match Found' End) As Status
From @temptable
End
--Executing Procedure
Execute dbo.Proc_CheckPhNo 2018087620, 2018087621
June 1, 2012 at 8:37 am
Thanks Vinu, Actually I got it worked...I kind of did the same thing you suggested !
But thanks for the reponse !
June 1, 2012 at 9:40 am
looks like you could use a TVP (table value parameter)
it's bets not to pass delimeted strings in as parameters
try the following - (example code only)
CREATE TYPE xtype as TABLE(id int)
GO
create proc myproc @param1 xtype READONLY
as
select * from sysobjects o inner join @param1 x on x.id=o.id
go
declare @var xtype
insert into @var select 1
insert into @var select 2
select * from @var
exec myproc @var
MVDBA
June 1, 2012 at 10:08 pm
michael vessey (6/1/2012)
looks like you could use a TVP (table value parameter)it's bets not to pass delimeted strings in as parameters
try the following - (example code only)
CREATE TYPE xtype as TABLE(id int)
GO
create proc myproc @param1 xtype READONLY
as
select * from sysobjects o inner join @param1 x on x.id=o.id
go
declare @var xtype
insert into @var select 1
insert into @var select 2
select * from @var
exec myproc @var
Yes, Micheal is right. A table valued parameter is better. You coul implement it to your procedure very easily.
Thanks Vinu, Actually I got it worked...I kind of did the same thing you suggested !
But thanks for the reponse !
You're welcome mate....I'm glad it worked out.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply