openrowset stored proc parameters

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

openrowset stored proc parameters

Author

Topic

ThePrisoner
Starting Member

18 Posts

Posted - 2007-07-02 : 06:12:15

hello

I am having trouble joining an openrowset which calls a stored procedure which accepts parameters.

select * from foo
inner join
(SELECT *
FROM OPENROWSET('SQLNCLI', 'Server=myserver;Trusted_Connection=yes;',
'''SET FMTONLY OFF;EXEC foo.dbo.usp_GetProductCrossTab '''
+ @param1+ ''' , ''' + @param2 + ''' , '''
+ @param3 + ''' , ''' + @param4
+ ''' , ''' + @param5 + '''')) as y
on x.foobar= y.foobar

it gives me Incorrect syntax near '+'.

any ideas ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-02 : 06:41:32

can be a number of problems. NEVER EVER build a SQL statement like this for at least these two reasons

1) You are subject to SQL injection, which means people can steal yuor data
2) You can't control all possible combinations of single quotes and brackets.

For debugging your code, build a string first and do a print of the statement you want to run.

Peter Larsson
Helsingborg, Sweden

ThePrisoner
Starting Member

18 Posts

Posted - 2007-07-02 : 06:51:18

thanks for the reply.

so how should it be done if this is not safe ?

I found the problem :

the parameter values must contain quotes :

declare @param1 = '''foobar'''
instead of @param1 = 'foobar'

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-02 : 09:12:35

as far as i know OPENROWSET doens't support variable contencation.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-02 : 10:28:22

quote:
Originally posted by spirit1

as far as i know OPENROWSET doens't support variable contencation.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Until dynamic sql is used

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources