Stored procedure won't work with IN clause parameter? 2

[1DMF]

Hi,

Is there a reason I cannot get my stored procedure to work with an input parameter that is a CSV and used for an IN clause?

PROCEDURE [dbo].[spRPT_DocsQueueHistSuper] 
	-- Add the parameters for the stored procedure here
	@startDate char(12),
	@endDate char(12),
	@members varchar(MAX)
		
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    
SELECT  Docs_Request_Hist.Case_ID, Docs_Request_Hist.Sent_By, Docs_Request_Hist.Docs_Present, Docs_Request_Hist.Date_Sent, [firstname] + ' ' + [LastName] AS Adviser, CompanyName, CNames AS ClientName, Prod_Type,Adv_MemNo,Category,Status,Rec_Type
FROM ((Docs_Request_Hist LEFT JOIN Business_Register ON Docs_Request_Hist.Case_ID = Business_Register.Rec_ID) LEFT JOIN Members ON Business_Register.Adv_MemNo = Members.ID)
GROUP BY [FirstName] + ' ' + [LastName], CompanyName,Date_Sent,Case_ID,Sent_By,Docs_Present,CNames,Prod_Type,id,Category,Status,Rec_Type,Adv_Memno
HAVING (Date_Sent Between @startDate And @endDate) AND (Adv_MemNo IN (@members))
ORDER BY [FirstName] + ' ' + [LastName];

END

It always returns zero records when I know there are records for the @members IN clause CSV list parameter?

your help is appreciated.

1DMF

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Dance Music Downloads

 

[Rhys666]

@members is just a string and you can't use it in that way. Without wanting to change any architecture, you would need to do something like parse/convert your @members variable into a table variable with a single column and join to it.

Rhys

"Technological progress is like an axe in the hands of a pathological criminal"
"Two things are infinite: the universe and human stupidity; and I'm not sure about the the universe"
Albert Einstein

 

[1DMF]

Oh, why? you could write SQL to parse a CSV string for an IN clause can't you or can you only do it with CSV numbers?

OK, so how do I parse a CSV string into a temporary table using T-SQL?

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Dance Music Downloads

 

[Rhys666]

You can't do it the way you think. IN determines whether a specified value matches any value in a subquery or a list, however @members is treated as a single varchar(max) value, not a delimited list of values. Below is the User Defined Function I use to convert a comma delimited list of integers to a table;

CREATE FUNCTION [dbo].[CommaSeparatedStringToTable]
(
	@CommaSeparatedValues VARCHAR(MAX)

) 
RETURNS @Item TABLE 
(
	Id INT NOT NULL PRIMARY KEY
)
AS
BEGIN
	DECLARE @IndexOfComma INT;
	DECLARE @Value VARCHAR(200);
	DECLARE @StartPos BIGINT = 1;
	DECLARE @EndPos BIGINT = 0;
	DECLARE @LengthOfString INT = LEN(@CommaSeparatedValues);
	DECLARE @ReachedEnd BIT = 0; 

	WHILE (@ReachedEnd <> 1)
	BEGIN
		SET @EndPos = CHARINDEX(',',@CommaSeparatedValues,@StartPos);
		IF (@EndPos > 0)
		BEGIN
			SET @Value = SUBSTRING(@CommaSeparatedValues, @StartPos,@EndPos-@StartPos);
			SET @StartPos = @EndPos + 1;
		END
		ELSE
		BEGIN
			SET @ReachedEnd = 1;
			SET @Value = SUBSTRING(@CommaSeparatedValues, @StartPos,@LengthOfString-(@StartPos-1));
		END

		IF (@Value <> '')
		BEGIN
			INSERT INTO @Item(Id) SELECT @Value
			EXCEPT
			SELECT Id FROM @Item WHERE Id = CAST(@Value AS INT);
		END
	END
	RETURN;
END

Rhys

"Technological progress is like an axe in the hands of a pathological criminal"
"Two things are infinite: the universe and human stupidity; and I'm not sure about the the universe"
Albert Einstein

 

[1DMF]

Hi Rhys,

Been looking at that myself!

I found this..

FUNCTION [dbo].[SplitCSV] (@CSVString VARCHAR(MAX), @Delimiter CHAR(1))

RETURNS @temptable TABLE (items VARCHAR(MAX))

AS

BEGIN

DECLARE @pos INT;

DECLARE @slice VARCHAR(MAX);

 

SELECT @pos = 1;

IF LEN(@CSVString) < 1 OR @CSVString IS NULL RETURN;

 

WHILE @pos!= 0

BEGIN

SET @pos = CHARINDEX(@Delimiter,@CSVString);

IF @pos != 0

SET @slice = LEFT(@CSVString, @pos - 1);

ELSE

SET @slice = @CSVString;

 

IF( LEN(@slice) > 0)

INSERT INTO @temptable(Items) VALUES (@slice);

 

SET @CSVString = RIGHT(@CSVString, LEN(@CSVString) - @pos);

IF LEN(@CSVString) = 0 BREAK;

END

RETURN

END

and then am trying to do

CREATE TABLE #Mem ( 
    Adv_MemNo varchar(MAX)      
) 
 
INSERT INTO #Mem SELECT items AS Adv_MemNo FROM dbo.SplitCSV(@members, ','))

But i have a syntax error and it also claims the dbo.SplitCSV is an invalid object, yet I have created the function?

So what am i doing wrong?

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Dance Music Downloads

 

[Rhys666]

Instead of a table variable you should be able to use the table function inline with a pseudonym.

Hmmmmm, not sure about that function but I do use the one I posted. You could just adapt that and rename it. The problem could simply be your syntax error... any hints on the line...?

Rhys

"Technological progress is like an axe in the hands of a pathological criminal"
"Two things are infinite: the universe and human stupidity; and I'm not sure about the the universe"
Albert Einstein

 

[1DMF]

ahh I give up with this stupid T-SQL, I'm too busy to be going round in circles with this.

I re-wrote my PERL code to use a SQL statement instead of a stored procedure and it works perfectly...

my @checks = &getSQL("Docs_Request_Hist LEFT JOIN Business_Register ON Docs_Request_Hist.Case_ID = Business_Register.Rec_ID LEFT JOIN Members ON Business_Register.Adv_MemNo = Members.ID", 
"Docs_Request_Hist.Case_ID, Docs_Request_Hist.Sent_By, Docs_Request_Hist.Docs_Present,Docs_Request_Hist.Date_Sent, [firstname] + ' ' + [LastName] AS Adviser, CompanyName, CNames AS ClientName,Prod_Type,Adv_MemNo,Category,Status,Rec_Type", 
"Adv_MemNo IN ($members)  GROUP BY [FirstName] + ' ' +[LastName],CompanyName,Date_Sent,Case_ID,Sent_By,Docs_Present,CNames,Prod_Type,id,Category,Status,Rec_Type,adv_memno HAVING (date_sent Between '$start' And '$end')",
"[FirstName] + ' ' + [LastName]");

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Dance Music Downloads

 

[1DMF]

I found a few minutes to try your code and it won't work?

I created the function from your code, but when i then go to modify it

[dbo].[CommaSeparatedStringToTable]

is underlined in red and when I hover intelisense says

invalid object name 'dbo.CommaSeparatedStringToTable'

why won't my SQL accept the function names?

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Dance Music Downloads

 

[Rhys666]

Sql Intellisense can be slow to refresh. Can you actually see the function in Management Studio when you expand the Programmability==>Functions==>Table-valued Functions node?

Rhys

"Technological progress is like an axe in the hands of a pathological criminal"
"Two things are infinite: the universe and human stupidity; and I'm not sure about the the universe"
Albert Einstein

 

[gmmastros]

What happens when you run this?

Select * From [dbo].[CommaSeparatedStringToTable]('a,b,c',',')

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

yes i can see the function i just can't use it?

if i try to use in the T-SQL

SELECT * FROM dbo.CommaSeparatedStringToTable

it doesn't like it, the TVF isn't showing in the intelsense either?

It exits, I just don't seem to be able to reference it or modify it?

Also how would I use the TVF to create a temp table in my SP to then use a join for the record selection?

SELECT * FROM dbo.CommaSeparatedStringToTable AS #MEM

would that be the correct usage?

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Dance Music Downloads

 

[Rhys666]

even though its a table function, its still a function, and it needs its parameters...

SELECT * FROM dbo.CommaSeparatedStringToTable('1,2,3,4,5,6,7');

Rhys

"Technological progress is like an axe in the hands of a pathological criminal"
"Two things are infinite: the universe and human stupidity; and I'm not sure about the the universe"
Albert Einstein

 

[Rhys666]

...or obviously;

DECLARE @members VARCHAR(MAX) = '1,2,3,4,5,6,7,';
SELECT * FROM dbo.CommaSeparatedStringToTable(@members);

Rhys

"Technological progress is like an axe in the hands of a pathological criminal"
"Two things are infinite: the universe and human stupidity; and I'm not sure about the the universe"
Albert Einstein

 

[1DMF]

man, i shut SQL studio manager re-opened and now it works...stupid damn GUI!

I'll have a play now I can invoke the function!

Thanks,
1DMF.

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Dance Music Downloads

 

[gmmastros]

Restarting SSMS would only affect intellisense, not the actual operations of the function.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Rhys666]

gmmastros, the table function I posted deliberately returns a table with one INT based column as that's how I use it, (for iterating and returning parent/child objects in one hit). That's why I suggested that the op play with it as I simply son't know if his comma delimited input is INT based.

Just commenting as

Select * From [dbo].[CommaSeparatedStringToTable]('a,b,c',',')

...will generate an exception and I think you may have meant;

Select * From [dbo].[SplitCSV]('a,b,c',',')

Rhys

"Technological progress is like an axe in the hands of a pathological criminal"
"Two things are infinite: the universe and human stupidity; and I'm not sure about the the universe"
Albert Einstein

 

[1DMF]

sorry yes forgot the arguments d'oh, but how do i use it in a join?

can i select it as a #MEM temp table and then use that to join?

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Dance Music Downloads

 

[Rhys666]

You just need to give the table function a pseudonym in a join;

...
    Business_Register
JOIN
    dbo.CommaSeparatedStringToTable(@members) x ON Business_Register.Adv_MemNo = x.Id

...if that makes sense

Rhys

"Technological progress is like an axe in the hands of a pathological criminal"
"Two things are infinite: the universe and human stupidity; and I'm not sure about the the universe"
Albert Einstein

 

[1DMF]

Restarting SSMS would only affect intellisense, not the actual operations of the function.

nope, I'd even clicked on the TVF and then refresh otherwise it doesn't appear in the tree.

It was only when studio was closed and opened again did it then accept the function as existing?

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Dance Music Downloads

 

[1DMF]

Rhys666, what column is x, the code you provided only has ID which is the value in the CSV from what I can tell, this I assume, assumes the values are unique? and that they are integer? yet 'Value' is defined as VARCHAR(200), but is then CAST, i'm very confused over the data types in your function so I have used the SplitCSV function I posted and it's working fine.

so all the problems I was having was because SQL Studio wasn't refreshing the functions unless I closed and re-open it!





"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Dance Music Downloads