July 16, 2009 at 6:26 am
Hi All
I am writing a SP, i got stuck with a problem, i am using a where clause to search a varchar field, SP is as below
Create Procedure Test (@Name varchar(100))
As
Declare @DupName varchar(100)
Declare @TempName Varchar(200)
Set @TempName=@Name
If( Select Count(*) from NameTable Where Name=@DupName)>1
Begin
Select @DupName =DupName from Nametable where MainName=@Name
Set@TempName=@Name+','+@DupName
End
Select * from ArchiveNames (nolock) where Name in (@TempName)
On the above procedure if there is no DupName then the procedure returns rows from ArchiveNames table that relates to the Name passed,
But if there is a DupName Present then i need to get all the records from ArchiveNames which relates both names, in this case the above SP is not working.
can you please shed some light for me on this
Thanks on advance for your help
Cheers
🙂
July 16, 2009 at 6:31 am
It's hard to say from the code you've posted, since there's no example data or table structure, but this seems off to me:
Create Procedure Test (@Name varchar(100))
As
Declare @DupName varchar(100)
Declare @TempName Varchar(200)
Set @TempName=@Name
If( Select Count(*) from NameTable Where Name=@DupName)>1
You're doing a query against NameTable, searching for Name = @DupName, but you haven't assigned anything to @DupName at this point, so it will never return a number > 1.
July 16, 2009 at 7:08 am
Hi Thanks for the reply, thats my mistake
If( Select Count(*) from NameTable Where Name=@DupName)>1
The above is wrong
If( Select Count(*) from NameTable Where Name=@Name)>1
I got clear from this point, if i print the variable i can see the parameter as "Name1,Name2"
But when this parameter is passed i am not getting any result.
On other words, how can i assign this 2 names to the Search condition
Cheers
July 16, 2009 at 7:19 am
There's two problems you have. First off, you're trying to make a comma-delimited list to use IN with, but you're using strings, not numbers. What you end up having is something like
X IN (User1, User2)
instead of
X IN ('User1', 'User2')
That's your first problem. The second, is that you're trying to execute a dynamic SQL statement combined with a non-dynamic one. Try this, it should work:
Create Procedure Test (@Name varchar(100))
As
Declare @DupName varchar(100)
Declare @TempName Varchar(200)
Set @TempName=@Name
If( Select Count(*) from NameTable Where Name=@Name)>1
Begin
Select @DupName =DupName from Nametable where MainName=@Name
Set @TempName=@Name+''','''+@DupName
End
EXEC('Select * from ArchiveNames (nolock) where Name IN (''' + @TempName + ''')')
July 16, 2009 at 8:36 am
Thanks mate, it worked 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply