November 4, 2011 at 10:52 am
Hi, i have one of the parameter of my procedure as @Stores as varchar(2000) = null,
which gets values like 325,785,5698 as inputs..
now i need to query it against STORES table of STORE_ID (which is INT)
i am doinf like and rh.STORE_ID in (@Stores)
which is failing and giving me Conversion failed when converting the varchar value '325,785,5698' to data type int. error..
could you please help me how to solve this...
November 4, 2011 at 11:00 am
Have you looked into using cast and or convert? http://msdn.microsoft.com/en-us/library/ms187928.aspx
November 4, 2011 at 11:02 am
whole (11/4/2011)
Hi, i have one of the parameter of my procedure as @Stores as varchar(2000) = null,which gets values like 325,785,5698 as inputs..
now i need to query it against STORES table of STORE_ID (which is INT)
i am doinf like and rh.STORE_ID in (@Stores)
which is failing and giving me Conversion failed when converting the varchar value '325,785,5698' to data type int. error..
could you please help me how to solve this...
I think this is what you're after -
--Standard TestEnvironment of 1,000,000 rows of random-ish data
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN
DROP TABLE #testEnvironment
END
--1,000,000 Random rows of data
SELECT TOP 1000000 IDENTITY(INT,1,1) AS ID,
RAND(CHECKSUM(NEWID())) * 30000 + CAST('1945' AS DATETIME) AS randomDate,
ABS(CHECKSUM(NEWID())) AS randomBigInt,
(ABS(CHECKSUM(NEWID())) % 10000) + 1 AS randomMediumInt,
(ABS(CHECKSUM(NEWID())) % 100) + 1 AS randomSmallInt,
RAND(CHECKSUM(NEWID())) * 100 AS randomSmallDec,
RAND(CHECKSUM(NEWID())) AS randomTinyDec,
RAND(CHECKSUM(NEWID())) * 100000 AS randomBigDec,
CONVERT(VARCHAR(6),CONVERT(MONEY,RAND(CHECKSUM(NEWID())) * 100),0) AS randomMoney
INTO #testEnvironment
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3
--Declare and set your "stores" variable
DECLARE @Stores AS VARCHAR(2000)
SET @Stores = '325,785,5698'
--Build query
DECLARE @query AS VARCHAR(MAX)
SET @query = 'SELECT * FROM #testEnvironment
WHERE randomMediumInt IN ('+@Stores+')'
--Execute query
EXEC(@query)
November 4, 2011 at 11:11 am
Is there any way without using inline SQL.
November 4, 2011 at 11:12 am
Another method: -
--Standard TestEnvironment of 1,000,000 rows of random-ish data
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN
DROP TABLE #testEnvironment
END
--1,000,000 Random rows of data
SELECT TOP 1000000 IDENTITY(INT,1,1) AS ID,
RAND(CHECKSUM(NEWID())) * 30000 + CAST('1945' AS DATETIME) AS randomDate,
ABS(CHECKSUM(NEWID())) AS randomBigInt,
(ABS(CHECKSUM(NEWID())) % 10000) + 1 AS randomMediumInt,
(ABS(CHECKSUM(NEWID())) % 100) + 1 AS randomSmallInt,
RAND(CHECKSUM(NEWID())) * 100 AS randomSmallDec,
RAND(CHECKSUM(NEWID())) AS randomTinyDec,
RAND(CHECKSUM(NEWID())) * 100000 AS randomBigDec,
CONVERT(VARCHAR(6),CONVERT(MONEY,RAND(CHECKSUM(NEWID())) * 100),0) AS randomMoney
INTO #testEnvironment
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3
--Declare and set your "stores" variable
DECLARE @Stores AS VARCHAR(2000)
SET @Stores = '325,785,5698';
--Query
WITH Pieces(pn, start, stop) AS (
SELECT 1, 1, CHARINDEX(',', @Stores)
UNION ALL
SELECT pn + 1, stop + 1, CHARINDEX(',', @Stores, stop + 1)
FROM Pieces
WHERE stop > 0)
SELECT *
FROM #testEnvironment
WHERE randomMediumInt IN (SELECT SUBSTRING(@Stores, start, CASE WHEN stop > 0 THEN stop-start ELSE 2000 END) AS stores
FROM Pieces)
November 4, 2011 at 11:16 am
Wow.. thats somthing i dont get it..
i might go with inline..
so how do i set the
and rh.CREATED_DATE between @StartDate and @endDate in the same inline query ?
November 4, 2011 at 11:51 am
What about using Jeff Moden's Tally table to parse the comma-delimited @Stores parameter. Store the parsed values into a temp table to directly join to the dbo.Stores table.
DISCLAIMER: The sample below uses the "Tally" table. But, if you do not have one you can also do an inline tally. You can just search for Tally and you will see a lot of examples and forum posts on it.
Here is one article: http://www.sqlservercentral.com/articles/Tally+Table/72993/
--Declare and set your "stores" variable
DECLARE @Stores AS VARCHAR(2000)
SET @Stores = '325,785,5698';
-- add preceeding and succeeding delimiters (i.e. comma) if they do not exist
SET @Stores = CASE WHEN LEFT(@Stores,1)=',' THEN '' ELSE ',' END + @Stores + CASE WHEN RIGHT(@Stores,1)=',' THEN '' ELSE ',' END;
create table #storeNbrFromParam (STORE_ID int);
-- extract the store numbers into individual items
-- !!!! this assumes you have a tally table !!!!!
insert into #storeNbrFromParam (STORE_ID )
select SUBSTRING(@Stores, t.N+1, CHARINDEX(',', @Stores, t.N+1)-t.N-1) as StoreNbr
from master.[dbo].[Tally] t
where t.N < LEN(@Stores)
AND SUBSTRING(@Stores,t.N,1) = ','
;
--pseudo
select rh.*
from dbo.Stores rh join #storeNbrFromParam snfp on snfp.STORE_ID = rh.STORE_ID
November 4, 2011 at 12:37 pm
John Michael Robertson (11/4/2011)
What about using Jeff Moden's Tally table to parse the comma-delimited @Stores parameter. Store the parsed values into a temp table to directly join to the dbo.Stores table.DISCLAIMER: The sample below uses the "Tally" table. But, if you do not have one you can also do an inline tally. You can just search for Tally and you will see a lot of examples and forum posts on it.
Here is one article: http://www.sqlservercentral.com/articles/Tally+Table/72993/
--Declare and set your "stores" variable
DECLARE @Stores AS VARCHAR(2000)
SET @Stores = '325,785,5698';
-- add preceeding and succeeding delimiters (i.e. comma) if they do not exist
SET @Stores = CASE WHEN LEFT(@Stores,1)=',' THEN '' ELSE ',' END + @Stores + CASE WHEN RIGHT(@Stores,1)=',' THEN '' ELSE ',' END;
create table #storeNbrFromParam (STORE_ID int);
-- extract the store numbers into individual items
-- !!!! this assumes you have a tally table !!!!!
insert into #storeNbrFromParam (STORE_ID )
select SUBSTRING(@Stores, t.N+1, CHARINDEX(',', @Stores, t.N+1)-t.N-1) as StoreNbr
from master.[dbo].[Tally] t
where t.N < LEN(@Stores)
AND SUBSTRING(@Stores,t.N,1) = ','
;
--pseudo
select rh.*
from dbo.Stores rh join #storeNbrFromParam snfp on snfp.STORE_ID = rh.STORE_ID
This is definitely the right direction. Read further along in the article you link about parsing strings. You will find a function called DelimitedSplit8K. It is exactly what you need for this. No complicated sql outside of the function. You just join to it and off you go.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 4, 2011 at 12:38 pm
Cadavre (11/4/2011)
whole (11/4/2011)
Hi, i have one of the parameter of my procedure as @Stores as varchar(2000) = null,which gets values like 325,785,5698 as inputs..
now i need to query it against STORES table of STORE_ID (which is INT)
i am doinf like and rh.STORE_ID in (@Stores)
which is failing and giving me Conversion failed when converting the varchar value '325,785,5698' to data type int. error..
could you please help me how to solve this...
I think this is what you're after -
--Standard TestEnvironment of 1,000,000 rows of random-ish dataIF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN
DROP TABLE #testEnvironment
END
--1,000,000 Random rows of data
SELECT TOP 1000000 IDENTITY(INT,1,1) AS ID,
RAND(CHECKSUM(NEWID())) * 30000 + CAST('1945' AS DATETIME) AS randomDate,
ABS(CHECKSUM(NEWID())) AS randomBigInt,
(ABS(CHECKSUM(NEWID())) % 10000) + 1 AS randomMediumInt,
(ABS(CHECKSUM(NEWID())) % 100) + 1 AS randomSmallInt,
RAND(CHECKSUM(NEWID())) * 100 AS randomSmallDec,
RAND(CHECKSUM(NEWID())) AS randomTinyDec,
RAND(CHECKSUM(NEWID())) * 100000 AS randomBigDec,
CONVERT(VARCHAR(6),CONVERT(MONEY,RAND(CHECKSUM(NEWID())) * 100),0) AS randomMoney
INTO #testEnvironment
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3
--Declare and set your "stores" variable
DECLARE @Stores AS VARCHAR(2000)
SET @Stores = '325,785,5698'
--Build query
DECLARE @query AS VARCHAR(MAX)
SET @query = 'SELECT * FROM #testEnvironment
WHERE randomMediumInt IN ('+@Stores+')'
--Execute query
EXEC(@query)
Be VERY careful if you use this type of approach. Make sure you read up on sql injection because this is very vulnerable.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 4, 2011 at 12:45 pm
Here is the query using Jeff's splitter.
declare @Stores varchar(2000)
set @Stores = '325,785,5698'
--create test data
;with Stores(StoreID, StoreName)
as (
select 1, 'some store' union all
select 325, 'Should be found' union all
select 785, 'also found' union all
select 434, 'Not found' union all
select 6868, 'nope' union all
select 5698, 'last one to find'
)
--here is the actual sored proc body now
select * from Stores
join dbo.DelimitedSplit8K(@Stores, ',') s on Stores.StoreID = s.Item
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 4, 2011 at 5:03 pm
Cadavre (11/4/2011)
Another method: -
--Standard TestEnvironment of 1,000,000 rows of random-ish dataIF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN
DROP TABLE #testEnvironment
END
--1,000,000 Random rows of data
SELECT TOP 1000000 IDENTITY(INT,1,1) AS ID,
RAND(CHECKSUM(NEWID())) * 30000 + CAST('1945' AS DATETIME) AS randomDate,
ABS(CHECKSUM(NEWID())) AS randomBigInt,
(ABS(CHECKSUM(NEWID())) % 10000) + 1 AS randomMediumInt,
(ABS(CHECKSUM(NEWID())) % 100) + 1 AS randomSmallInt,
RAND(CHECKSUM(NEWID())) * 100 AS randomSmallDec,
RAND(CHECKSUM(NEWID())) AS randomTinyDec,
RAND(CHECKSUM(NEWID())) * 100000 AS randomBigDec,
CONVERT(VARCHAR(6),CONVERT(MONEY,RAND(CHECKSUM(NEWID())) * 100),0) AS randomMoney
INTO #testEnvironment
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3
--Declare and set your "stores" variable
DECLARE @Stores AS VARCHAR(2000)
SET @Stores = '325,785,5698';
--Query
WITH Pieces(pn, start, stop) AS (
SELECT 1, 1, CHARINDEX(',', @Stores)
UNION ALL
SELECT pn + 1, stop + 1, CHARINDEX(',', @Stores, stop + 1)
FROM Pieces
WHERE stop > 0)
SELECT *
FROM #testEnvironment
WHERE randomMediumInt IN (SELECT SUBSTRING(@Stores, start, CASE WHEN stop > 0 THEN stop-start ELSE 2000 END) AS stores
FROM Pieces)
Oh, be careful now. You're asking for a snootfull of resource usage because you're using a recursive CTE to count. Even small numbers take a very large toll very quickly. Please see the following article for why you shouldn't use recursive CTE's for such a thing.
http://www.sqlservercentral.com/articles/T-SQL/74118/
Instead of resorting to the temptation of using an rCTE for such a thing, use a good splitter instead. There's one in the "resources" links at the bottom of the following article...
http://www.sqlservercentral.com/articles/Tally+Table/72993/
--Jeff Moden
Change is inevitable... Change for the better is not.
November 7, 2011 at 2:23 pm
hi I found this function somewhere on web and it have been very helpful 🙂
just run it on your DB and you can always reference it from anywhere
/*
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_Split]
(
@RowData NVARCHAR(MAX),
@Delimeter NVARCHAR(MAX)
)
RETURNS @RtnValue TABLE
(
ID INT IDENTITY(1,1),
Data NVARCHAR(MAX)
)
AS
BEGIN
DECLARE @Iterator INT
SET @Iterator = 1
DECLARE @FoundIndex INT
SET @FoundIndex = CHARINDEX(@Delimeter,@RowData)
WHILE (@FoundIndex>0)
BEGIN
INSERT INTO @RtnValue (data)
SELECT
Data = LTRIM(RTRIM(SUBSTRING(@RowData, 1, @FoundIndex - 1)))
SET @RowData = SUBSTRING(@RowData,
@FoundIndex + DATALENGTH(@Delimeter) / 2,
LEN(@RowData))
SET @Iterator = @Iterator + 1
SET @FoundIndex = CHARINDEX(@Delimeter, @RowData)
END
INSERT INTO @RtnValue (Data)
SELECT Data = LTRIM(RTRIM(@RowData))
RETURN
END
*/
November 7, 2011 at 2:30 pm
ryvlad (11/7/2011)
hi I found this function somewhere on web and it have been very helpful 🙂just run it on your DB and you can always reference it from anywhere
You should look at the article by Jeff that I linked several posts ago. Or you can find it in my signature. A loop is not the best approach for splitting strings. It is slow and inefficient. Read that article and look at the performance differences he discusses. He talks about a number of ways of splitting strings and other than parsing xml that type of splitter is the worst performer of them all.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 7, 2011 at 2:31 pm
will do , thanks
November 7, 2011 at 3:22 pm
Hey Sean, does Jeff send you some sort of royalty checks in the mail from time-to-time? 😉
(heh, heh)
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply