I have this table :
Name | Date | Para1|
N1 | 01/10| 1 |
N2 | 02/10| 8 |
N2 | 04/10| 7 |
N3 | 05/10| 6 |
I would like to select some name as parameter : @Name = 'N1,N2' and only the sooner parameter para 1 so as output i will have :
N1 | 1|
N2 | 8|
I tried that :
SELECT TOP 1 Para1 From MyTable where Name IN ( @Name) ORDER BY Date ASC
Put the part IN (@Name) does see Name as a whole word when I ask for Name = 'N1,N2'
Any idea how I can do that ?
Thanks
SELECT TOP 1 Para1 From MyTable
where ','+@Name+',' like '%,'+cast(Name as varchar(100))+',%'
ORDER BY Date ASC
If you are using SQL-Server 2008 or later then you should consider using table value parameters, rather than a comma separated list. Below is an example of how to create the type required, create the procedure and execute it.
CREATE TYPE dbo.NameList AS TABLE (Name VARCHAR(50) NOT NULL)
GO
CREATE PROCEDURE dbo.GetNames @NameList dbo.NameList READONLY
AS
BEGIN
SELECT TOP 1 Para1
FROM MyTable
INNER JOIN @NameList n
ON MyTable.Name = n.Name
ORDER BY Date ASC
END
GO
DECLARE @Names dbo.NameList
INSERT @Names VALUES ('N1'), ('N2')
EXECUTE dbo.GetNames @Names
@name is seen as a whole word because it is part of the string. you should sperate them by comma like this. 'N1', N2'.
i don't know which databse you are using. but for SQLServer. you can easily split @name string based on the the comma delimiter. and in oracle. this is possible too.
if you are using SQlServer andyou can't do it tell me. in short the problem above is that you did not split N1 and N2 using comma you just put it inside string. it should look like this 'N1' , 'N2'
Yes if you are using SQL Server 2008+ then as GarethD pointed out you could use Table Valued Parameter.
If you are using SQL Server 2005 may be you could try this out:
--SPLIT Function
CREATE FUNCTION [dbo].[SplitUsingXML]
(
@String VARCHAR(MAX),
--If your delimiter is multi character
--then change it as VARCHAR with appropriate length
@Delimiter CHAR(1)
)
RETURNS @Results TABLE
(
parsedValue VARCHAR(MAX)
)
AS
BEGIN
DECLARE @xml XML
--Below line seems to get screwed up once i post it in Blogger :(
--please use the line as shown in the attached image. Sorry about that.
SET @XML = N'<Content><row>' + REPLACE(@String, @Delimiter, '</row><row>') + '</row></Content>'
--If your individual value length within the CSV can be more than 25 characters
--then you might want to increase it in the below statement
--pls note it won't throw an error if its more than 25 characters
--just that it would truncate and show only the first 25 character :)
INSERT INTO @Results(parsedValue)
SELECT row.value('.','VARCHAR(25)') as parsedValue
FROM @xml.nodes('//Content/row') AS RECORDS(row)
RETURN
END
GO
SELECT [name], date, para1
FROM
(
SELECT
[name], date, para1, ROW_NUMBER() OVER(PARTITION BY [date] ORDER BY [date] DESC) AS RowNum
FROM #test a
WHERE a.[Name] IN (SELECT parsedValue FROM dbo.SplitUsingXML('N1,N2', ','))
) AS WorkTable
WHERE RowNum = 1
Seperate each name with it's own apostrophes (').
SELECT TOP 1 Para1 From MyTable where Name IN ('N1', 'N2') ORDER BY Date ASC
This query will return consider 'N1' or 'N2' as part of the WHERE clause:
SELECT Name, Para1 FROM <table name> WHERE Name IN('N1','N2') ORDER BY Date ASC
