June 15, 2012 at 4:09 am
Hi All,
The follow code works fine on a 2005 instance but it appears 2008 & 2008 R2 throw a conversion error. Even if you cast the "item" to a decimal the code will fail.
Does anyone have any idea on how to fix this or work around. I'd rather not go down the route of casting the id to varchar as the table is large in our production environment and the query would grind.
CREATE FUNCTION [dbo].[FN_DELIMITED_STRING_TO_TABLE]
(
@pString VARCHAR(MAX),
@pDelimiter CHAR(1)
)
RETURNS TABLE
AS RETURN
WITH A1
AS ( SELECT 1 AS N
UNION ALL
SELECT 1
UNION ALL
SELECT 1
UNION ALL
SELECT 1
UNION ALL
SELECT 1
UNION ALL
SELECT 1
UNION ALL
SELECT 1
UNION ALL
SELECT 1
UNION ALL
SELECT 1
UNION ALL
SELECT 1
) ,
A2
AS ( SELECT 1 AS N
FROM A1 AS a
CROSS JOIN A1 AS b
) ,
A3
AS ( SELECT 1 AS N
FROM A2 AS A
CROSS JOIN A2 AS b
) ,
A4
AS ( SELECT 1 AS N
FROM A3 AS A
CROSS JOIN A2 AS B
) ,
Tally
AS ( SELECT TOP ( LEN(@pString) )
ROW_NUMBER() OVER ( ORDER BY N ) AS N
FROM A4
) ,
ItemSplit ( ItemOrder, Item )
AS ( SELECT N,
SUBSTRING(@pDelimiter + @pString + @pDelimiter,
N + 1,
CHARINDEX(@pDelimiter,
@pDelimiter + @pString
+ @pDelimiter, N + 1) - N - 1)
FROM Tally
WHERE N < LEN(@pDelimiter + @pString + @pDelimiter)
AND SUBSTRING(@pDelimiter + @pString + @pDelimiter,
N, 1) = @pDelimiter
)
SELECT ROW_NUMBER() OVER ( ORDER BY ItemOrder ) AS ItemID,
Item
FROM ItemSplit
GO
CREATE TABLE [dbo].[test](
[id] [decimal](18, 0) NOT NULL
) ON [PRIMARY]
GO
INSERT INTO [dbo].[test]
( [id] )
VALUES ( 1000
)
INSERT INTO [dbo].[test]
( [id] )
VALUES ( 2000
)
INSERT INTO [dbo].[test]
( [id] )
VALUES ( 3000
)
INSERT INTO [dbo].[test]
( [id] )
VALUES ( 4000
)
INSERT INTO [dbo].[test]
( [id] )
VALUES ( 5000
)
GO
EXEC sp_executesql N'SELECT [id]
FROM [dbo].[test] WITH (NOLOCK) WHERE id IN (select Item from dbo.FN_DELIMITED_STRING_TO_TABLE(@1,'',''))
ORDER BY id',
N'@1 varchar(MAX)',
'1000,2000,3000'
Many Thanks
June 15, 2012 at 5:15 am
EXEC sp_executesql N'SELECT [id]
FROM [dbo].[test] WITH (NOLOCK) WHERE id IN (select Item from dbo.FN_DELIMITED_STRING_TO_TABLE(@1,'',''))
ORDER BY id',
N'@1 varchar(MAX)',
'1000,2000,3000'
try this
declare @sql varchar(max)
set @sql = 'your select query'
print @sql -- to see if the query is correct or not.
--exec(@sql) -- if query is correct, you can uncomment this.
----------
Ashish
June 15, 2012 at 5:30 am
I've tried this
set @sql = 'SELECT [id]
FROM [dbo].[test] WITH (NOLOCK) WHERE id IN (select Item from dbo.FN_DELIMITED_STRING_TO_TABLE(''1000,2000,3000'','',''))
ORDER BY id'
as you suggested. The query when executed looks fine on the print statement but the error remains.
June 15, 2012 at 6:40 am
then I guess its breaking here
WHERE id IN (select Item from dbo.FN_DELIMITED_STRING_TO_TABLE(''1000,2000,3000'','',''))
what kind of table is this?
dbo.FN_DELIMITED_STRING_TO_TABLE(''1000,2000,3000'','','')
----------
Ashish
June 15, 2012 at 7:25 am
I included the definition of the function in my original post. The purpose of it is to take a delimeted string and return it as a table you are then able to query.
June 15, 2012 at 7:50 am
You can use APPLY instead and it will work just fine.
EXEC sp_executesql N'SELECT [id]
FROM [dbo].[test]
CROSS APPLY dbo.FN_DELIMITED_STRING_TO_TABLE(@1,'','')
ORDER BY id',
N'@1 varchar(MAX)',
'1000,2000,3000'
From what you posted I don't understand why you need this in dynamic sql but of course this is just a skeleton. Why the NOLOCK hint?
_______________________________________________________________
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/
June 15, 2012 at 8:05 am
The CROSS APPLY seems to return the results in triplicate. I can't use DISTINCT as i've other columns in my select.
The NOLOCK hint is for speed, the query is over a reporting DB and shouldn't have updates applied once the records are inserted
June 15, 2012 at 8:26 am
mark.sayer (6/15/2012)
The CROSS APPLY seems to return the results in triplicate. I can't use DISTINCT as i've other columns in my select.The NOLOCK hint is for speed, the query is over a reporting DB and shouldn't have updates applied once the records are inserted
Oh silly me...that's what I get for posting before enough coffee. :blush:
How about just joining to your function?
EXEC sp_executesql N'SELECT [id]
FROM [dbo].[test] t
JOIN dbo.FN_DELIMITED_STRING_TO_TABLE(@1,'','') s on s.Item = t.id
ORDER BY id',
N'@1 varchar(MAX)',
'1000,2000,3000'
As for the NOLOCK hint, it is not just a magic go fast pill. If you understand the caveats of it and accuracy is not highly critical then you may be ok.
_______________________________________________________________
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/
June 15, 2012 at 8:46 am
If I may, I am wondering why you are even using sp_executesql to run your select statement. From what I am seeing, it isn't necessary.
June 15, 2012 at 8:47 am
Lynn Pettis (6/15/2012)
If I may, I am wondering why you are even using sp_executesql to run your select statement. From what I am seeing, it isn't necessary.
I asked that too Lynn, of course we are only seeing a small piece of the larger piece I suspect.
_______________________________________________________________
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/
June 15, 2012 at 9:21 am
Sean you are quite correct. I've a web application that users can pick fields from dropdowns & operators etc and enter selection criteria. This data is then used to dynamically build a select statement for a dataset for a report. Hence I need to use parameters and the sp_executesql as I cannot trust the user input. I use stored procedures with pre-definded statements & parameters everywhere else but for this the SQL needs to be dynamic.
I understand the NOLOCK is in effect a dirty read but it shouldn't matter in my scenario.
The JOIN seems to work perfectly but an OUTER JOIN which I assume would be be NOT IN scenario doesn't work.
Any ideas??
June 18, 2012 at 3:30 am
Thank you everyone for your help, especially Sean.
The underlying problem was casting a varchar(max) into a decimal. If I change this to cast to a float the problem goes away.
Many Thanks
June 18, 2012 at 7:13 am
mark.sayer (6/18/2012)
Thank you everyone for your help, especially Sean.The underlying problem was casting a varchar(max) into a decimal. If I change this to cast to a float the problem goes away.
Many Thanks
You are quite welcome. Glad you figured out how to make it work.
I would be very wary of executing code that contains user input as you are likely to be vulnerable to sql injection. This sounds like you are doing a type of "catch all" query. You might take a look at Gail's blog post here. http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]. She explains a great way of handling this type of query and eliminates the sql injection vulnerability when using dynamic sql.
_______________________________________________________________
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/
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply