Using parameters in OPENROWSET in astore dprocedure

Question

Using parameters in OPENROWSET in astore dprocedure

jfallsen

SSC Veteran

Points: 297
More actions
March 26, 2010 at 9:29 am

#213200

Hello i am having trouble using parameters in select statement when using OPENROWSET in a stored procedure in order to obtain values from a database table below is a example procedure:
SET @GenDate_V = (
SELECT * FROM OPENROWSET(
'SQLNCLI10.1',
'Server=127.0.0.1;Trusted_Connection=yes',
'SELECT GenDate_V
FROM model.dbo.message WHERE MsgId Like ''' + @MESSAGEID+ '''
'
) AS message)
''@MESSAGEID'' is the parameter i want to pass as an id to get an unique field witch is GenDate_V returned in a result set however there is no way making this work since i get the error message Msg 102, Level 15, State 1, Procedure usp_createapprec, Line 77
Incorrect syntax near '+' can you please send me an example of how i can fix this, i am also just a novise regarding SQL Server
I also want to Return all the fields but that is impossible sinvce i get the error message that it can only return one field for one variable, i am going to use tis varable constructing xml so i becames to many queries per table in order to fill XML elemnts with the data, is there any recomended technology to use for this XQuery perhaps?
I priciate any answer, thanks in advance
jfallsen

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

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

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

Hi jfallsen, please post sample data, ur table structure, and ur desired output.. this is really pain staking to create them and then attend ur request.. as i had time i had created some sample data here..

i am not sure if this is what u had asked, please tell us if this is what u expected..

Sample data and table structure

IF OBJECT_ID('TestData') IS NOT NULL

DROP TABLE TestData

CREATE TABLE TestData

(

MessageID INT IDENTITY(1,1) NOT NULL,

Messages VARCHAR(256) NOT NULL

)

INSERT INTO TestData

SELECT 'Save Water'

UNION ALL

SELECT 'Dont use Plastics'

UNION ALL

SELECT 'Switch Off Lights and Save electricty'

UNION ALL

SELECT 'Stop engines in signals to save fuels'

UNION ALL

SELECT 'Dont litter, keep world clean'

UNION ALL

SELECT 'Wash hands before touching food or children'

UNION ALL

SELECT 'Please post sample code when u ask questions, this saves our a**'

SELECT * FROM TestData

Here is the Query u had asked (or rather what i understood from ur post)

DECLARE @Query VARCHAR(1024)

DECLARE @MessageID INT

SET @MessageID = 1 -- This is where u will

-- pass the MessageID

SET @Query = '

SELECT * FROM OPENROWSET(

''SQLNCLI'',

''Server=YOUR_SERVER_NAME_HERE;Trusted_Connection=yes'',

'' SELECT MessageID ,Messages

FROM TestData WHERE MessageID = '+CAST(@MessageID AS VARCHAR(64))+ '

''

) AS Message'

--PRINT @Query

EXEC (@Query)

Please us if this is right!!

Cheers,

C'est Pras 🙂

Paul White SSC Guru Points: 150467 More actions · Answer 2

Another option is the EXECUTE AT syntax:

Example:

EXECUTE ('SELECT * FROM master.sys.tables WHERE name LIKE ?', 'spt%') AT <linked_server_name>;

The parameters are handy.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 3

Paul, i have a doubt here.. did i answer to OP's question.. i understood it in a way and i have provided the link.. do u think i have given the correct code he/she had asked for? :unsure:

Paul White SSC Guru Points: 150467 More actions · Answer 4

COldCoffee (3/29/2010)
Paul, i have a doubt here.. did i answer to OP's question.. i understood it in a way and i have provided the link.. do u think i have given the correct code he/she had asked for? :unsure:

Probably - looks fine to me - I guess we will know more if and when jfallsen responds.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi