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-2 TABLE (sales_ID INT IDENTITY, sales_date datetime, category VARCHAR(10));
INSERT INTO @test-2
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-2 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 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply