April 18, 2011 at 9:20 am
This doesn't return anything:
EXEC dbo.GetSales GETDATE(), 'Outer Wear'', ''Mens'', ''Foot Wear'', ''N/A';
GO
Here is my stored proc:
Create procedure [dbo].[GetSales] (@SaleDate datetime = NULL,
@SaleCategory varchar(250)= NULL)
as
BEGIN
Select distinct a.sales_ID From
sales a
WHERE
a.sales_date <= @SaleDate
and a.category in --('Outer Wear',
'Mens', 'Foot Wear', 'N/A')
(@SaleCategory)
Order By 1
end
April 18, 2011 at 9:55 am
removed for post below
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
April 18, 2011 at 10:00 am
sorry looked at it wrong althought there is still an issue with the string being passed. Try
EXEC dbo.GetSales GETDATE(), '''Outer Wear'', ''Mens'', ''Foot Wear'', ''N/A''';
GO
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
April 18, 2011 at 10:15 am
I tried this, not working.
DECLARE @SaleDate datetime;
SET @SaleDate = GETDATE();
EXEC dbo.GetSales @SaleDate, '''Outer Wear'', ''Mens'', ''Foot Wear'', ''N/A''';
GO
DB table has data.
If hard code it like this:
a.
category in ('Outer Wear', 'Mens', 'Foot Wear', 'N/A')
I get records. Not sure what issue in passing string parameters. Do I need to break it up or I think I am missing something.
April 18, 2011 at 10:18 am
I would alter the SP and add a line like
print or select @SaleCategory
This would allow you to see what is actually making it in to the variable.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
April 18, 2011 at 10:24 am
applebutton (4/18/2011)
I tried this, not working.DECLARE @SaleDate datetime;
SET @SaleDate = GETDATE();
EXEC dbo.GetSales @SaleDate, '''Outer Wear'', ''Mens'', ''Foot Wear'', ''N/A''';
GO
DB table has data.
If hard code it like this:
a.
category in ('Outer Wear', 'Mens', 'Foot Wear', 'N/A')
I get records. Not sure what issue in passing string parameters. Do I need to break it up or I think I am missing something.
You need to break it up - the variable will be interpreted as a single value. A great place to start is this article [/url]by Jeff Moden, the section you should read is near the end - Splitting Strings. Have a read. If you get stuck, post back for help.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
April 18, 2011 at 11:45 am
This is the problem. I have a stored proc that is called by an application with parameters as a list. I need to have this stored proc create this tally table function:
CREATE FUNCTION TVF_TallySplit(
@Delim CHAR(1), -- List Delimiter
@String VARCHAR(8000))
RETURNS TABLEASRETURN( SELECT SUBSTRING(@Delim + @String + @Delim,N+1,CHARINDEX(@Delim,@Delim + @String + @Delim,N+1)-N-1) ListValue FROM Tally WHERE N < LEN(@Delim + @String + @Delim) AND SUBSTRING(@Delim + @String + @Delim,N,1) = @Delim )
then within this stored proc I need to call similar looking sql from stored proc to return result set:
SELECT * FROM Fruits where Name IN (SELECT * FROM Util.dbo.TVF_TallySplit(',',@YFFruits))
Question now is, can I do function declaration within a stored proc or function declaration outside of stored proc? I am not an expert in SQL Server 2008 stored proc.
April 18, 2011 at 11:50 am
Jeff's posted an updated version of the string splitter here.
I usually stream the results of a string-splitter into a #temp table then use that in the main query.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
April 18, 2011 at 1:11 pm
ChrisM@home (4/18/2011)
Jeff's posted an updated version of the string splitter here.I usually stream the results of a string-splitter into a #temp table then use that in the main query.
I am using Jeff's new function. But encountered a problem.
If I run this function as is, I get this error:
Cannot resolve the collation conflict between "Latin1_General_BIN" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
If the change the collate from Latin1_General_BIN to SQL_Latin1_General_CP1_CI_AS, then I don't get any records.
I am kind of stuck.
Here is what I have so far:
Declare @SaleDate as DateTime Set @SaleDate = GetDate()
Declare @SaleCategory as varchar(255) Set @SaleCategory = 'Outer Wear'', ''Mens'', ''Foot Wear'', ''N/A'
IF OBJECT_ID('tempdb..#mytemp') is Not NULL Drop Table #mytemp
(SELECT * into #mytemp FROM dbo.ParameterSplitter(@SaleCategory, ','));
select * from #mytemp as ts
select Item from #mytemp as ts
Select distinct a.sales_ID From
sales a
WHERE
a.sales_date <= @SaleDate
and a.category in (SELECT Item FROM #mytemp)
--('Outer Wear', 'Mens', 'Foot Wear', 'N/A')
--(@SaleCategory)
Order By 1
end
April 18, 2011 at 1:44 pm
Put the COLLATE into the SELECT from the #temp table:
Declare @SaleCategory as varchar(255)
Set @SaleCategory = 'Outer Wear, Mens, Foot Wear, N/A'
select Item
INTO #mytemp
from [dbo].[DelimitedSplitN4K] (@SaleCategory, ',')
Select distinct a.sales_ID
From sales a
WHERE
a.sales_date <= @SaleDate
and a.category in (SELECT Item COLLATE SQL_Latin1_General_CP1_CI_AS FROM #mytemp)
Order By 1
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
April 18, 2011 at 3:32 pm
When I put the collate in front as you suggested, I don't get any sales_ID. No records. Nothing, nata.
ChrisM@home (4/18/2011)
Put the COLLATE into the SELECT from the #temp table:
Declare @SaleCategory as varchar(255)Set @SaleCategory = 'Outer Wear, Mens, Foot Wear, N/A'
select Item
INTO #mytemp
from [dbo].[DelimitedSplitN4K] (@SaleCategory, ',')
Select distinct a.sales_ID
From sales a
WHERE
a.sales_date <= @SaleDate
and a.category in (SELECT Item COLLATE SQL_Latin1_General_CP1_CI_AS FROM #mytemp)
Order By 1
April 18, 2011 at 4:00 pm
See my signature for the latest version of the Delimited Split function.
DECLARE @SaleCategory VARCHAR(500),
@SaleDate datetime;
SET @SaleCategory = 'Outer Wear,Mens,Foot Wear,N/A';
SET @SaleDate = '20110418';
DECLARE @test TABLE (sales_ID INT IDENTITY, sales_date datetime, category VARCHAR(10));
INSERT INTO @Test
SELECT '20110418', 'Outer Wear' UNION ALL
SELECT '20110417', 'Mens' UNION ALL
SELECT '20110416', 'Foot Wear' UNION ALL
SELECT '20110415', 'Womens' UNION ALL
SELECT '20110414', 'Kids' UNION ALL
SELECT '20110413', 'N/A' ;
SELECT DISTINCT a.sales_ID
--FROM sales a
FROM @test a
JOIN dbo.DelimitedSplit8K(@SaleCategory,',') ds
ON a.category = ds.Item COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE a.sales_date <= @SaleDate
ORDER BY sales_ID;
Edit: did you notice that the new one from Jeff is DelimitedSplitN4K? It uses a NVarchar(4000) for the input. The other one that I'm linking to uses the varchar(8000) - or you can just change that.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 19, 2011 at 8:27 am
awesome...It works now. Thanks.
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply