INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!

Talk With Other Members
Be Notified Of Responses
To Your Posts
Keyword Search
One-Click Access To Your
Favorite Forums
Automated Signatures
On Your Posts
Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

LINK TO THIS FORUM!

Tek-Tips LinkedIn^® Group

Join our
LinkedIn^® Group!

Hook up with past and present colleagues and classmates quickly.

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site

(Download This Button Today!)

Feedback

"...I think this forum rocks it has saved my bacon many many times..."

More...

Geography

Where in the world do Tek-Tips members come from?

Click Here To Find Out!

Tek-Tips Shirts!

Get your
Tek-Tips Forums
SWAG here!

Home > Forums > Programmers > DBMS Packages > Microsoft SQL Server: Programming Forum

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

CODE

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 (Programmer)

6 Jul 12 5:47

@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 (Programmer)

6 Jul 12 6:01

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?

Rhys666 (Programmer)

6 Jul 12 6:11

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;

CODE

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

1DMF (Programmer)

6 Jul 12 6:30

Hi Rhys,

Been looking at that myself!

I found this..

CODE

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

CODE

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?

Rhys666 (Programmer)

6 Jul 12 6:39

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...?

1DMF (Programmer)

6 Jul 12 6:52

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...

CODE

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]");

1DMF (Programmer)

6 Jul 12 8:52

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

Quote:
[dbo].[CommaSeparatedStringToTable]

is underlined in red and when I hover intelisense says

Quote:
invalid object name 'dbo.CommaSeparatedStringToTable'

why won't my SQL accept the function names?

Rhys666 (Programmer)

6 Jul 12 8:58

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?

gmmastros (Programmer)

6 Jul 12 9:03

What happens when you run this?

CODE

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 (Programmer)

6 Jul 12 9:05

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

if i try to use in the T-SQL

CODE

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?

CODE

SELECT * FROM dbo.CommaSeparatedStringToTable AS #MEM

would that be the correct usage?

Rhys666 (Programmer)

6 Jul 12 9:08

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

CODE

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

Rhys666 (Programmer)

6 Jul 12 9:09

...or obviously;

CODE

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

1DMF (Programmer)

6 Jul 12 9:12

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.

gmmastros (Programmer)

6 Jul 12 9:14

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 (Programmer)

6 Jul 12 9:23

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

CODE

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

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

CODE

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

1DMF (Programmer)

6 Jul 12 10:42

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?

Rhys666 (Programmer)

6 Jul 12 10:49

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

CODE

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

...if that makes sense

1DMF (Programmer)

9 Jul 12 4:37

Quote:
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?

1DMF (Programmer)

9 Jul 12 5:15

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!

1DMF (Programmer)

9 Jul 12 8:26

Thought it worth mentioning that the SplitCSV function works with a Join against the items column regardless of the join column's data type, which was handy as I needed the function for an 'IN' clause against a list of numbers and it worked without any modification.

Thanks for everyones help with this, it has been most helpful and enlightening 2thumbsup

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Back To Microsoft SQL Server: Programming

Member Login

Come Join Us!

LINK TO THIS FORUM!

Tek-Tips LinkedIn® Group

Partner With Us!

Feedback

Geography

Tek-Tips Shirts!

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

CODE

CODE

CODE

CODE

CODE

Quote:

Quote:

CODE

CODE

CODE

CODE

CODE

CODE

CODE

CODE

Quote:

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Back To Forum

Join Tek-Tips® Today!

Tek-Tips LinkedIn^® Group

Join Tek-Tips^® Today!