OPENROWSET to return an integer with parameters

Question

OPENROWSET to return an integer with parameters

robert-695083

SSC Rookie

Points: 26
More actions
April 4, 2010 at 9:44 pm

#217381

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ben Teraberry SSChampion Points: 11845 More actions · Answer 1

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.

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

robert-695083 SSC Rookie Points: 26 More actions · Answer 2

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?

Ben Teraberry SSChampion Points: 11845 More actions · Answer 3

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.

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 4

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 5

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)