April 4, 2010 at 9:44 pm
OPENROWSET to return an integer with parameters
Inside a function, I need to return an Integer. But if I use parameters I have to wrap the whole Openrowset in Dynamic SQL and It becomes a string and I get the following error when I call the function:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value '
SELECT MAID
FROM OPENROWSET('SQLOLEDB','Server=(local);Trusted_Connection=Yes;Database=CDR;',
'Select Top 1 MAID From MastersAgentsRangeList Where ParentID In ( Select MAID From MastersAgentsRangeList
Where RangeFrom = ''1000'' AND RangeTo = ''5000'') ORDER BY NEWID()' )' to data type int.
Statement used to call function:
Select [dbo].[fn_GetRandomT2Value](1000 ,5000 ) as MAID
This is the function:
ALTER FUNCTION [dbo].[fn_GetRandomT2Value](@RangeFrom Int, @RangeTo Int)
RETURNS Int
AS
BEGIN
DECLARE @SQLString NVARCHAR(500)
DECLARE @MAID Int
Set @MAID =(
N'
SELECT MAID
FROM OPENROWSET(''SQLOLEDB'',''Server=(local);Trusted_Connection=Yes;Database=CDR;'',
''Select Top 1 MAID From MastersAgentsRangeList Where ParentID In ( Select MAID From MastersAgentsRangeList
Where RangeFrom = ''''' + Cast(@RangeFrom as nvarchar) + ''''' AND RangeTo = ''''' + Cast(@RangeTo as nvarchar) + ''''') ORDER BY NEWID()'' )'
)
RETURN @MAID
END
April 6, 2010 at 10:09 am
My first question ... why are you using OPENROWSET to query a database on the local server? Even if you're hitting a remote server, the function is really designed for ad hoc queries. Otherwise it is better to use linked servers.
April 6, 2010 at 11:13 am
Seems you cant use Order By new() in a function unless it is in Dynamic sql. And the only way I could find to use it was in openrowset...Any other idea?
April 6, 2010 at 7:15 pm
NEWID() is to create a new unique identifier ... ordering by that function makes no sense to me. I would be curious to understand exactly what you're trying to accomplish in terms of the data you're working with and the results you're after.
April 7, 2010 at 6:13 pm
bteraberry (4/6/2010)
NEWID() is to create a new unique identifier ... ordering by that function makes no sense to me. I would be curious to understand exactly what you're trying to accomplish in terms of the data you're working with and the results you're after.
It produces a result set in random order.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 7, 2010 at 6:18 pm
robert-695083 (4/6/2010)
Seems you cant use Order By new() in a function unless it is in Dynamic sql. And the only way I could find to use it was in openrowset...Any other idea?
You can trick it, though. Create a view with nothing in it but SELECT NEWID() AS SomeNewID and you can call the view from the function.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply