April 16, 2011 at 10:08 am
Hi, Guys
I wonder is possible to make stored procedure which will be called different stored procedures depending of parameter in caller SP.
For examle:
create procedure mySP
@fnID int,
@storedProcedureName nvarchar(50)
//here then i want to call stored procedure which i want
//i think like this:
select * from openquery(loopback,'myDatabase.dbo. ' +@storedProcedureName+''+@fnID+''')
//is that possible or is there some other way to do that
Thanks
April 16, 2011 at 10:29 am
It's certainly possible.
Using openquery with loopback will limit what you see out of the stored procedure, because openquery only return's the stored procedures's first result set, so it may not be the best way to do it; why not just call EXECUTE? Something like
declare @sql varchar(8192); set @sql = 'exec MyDatabase.dbo.'+@storedProcedureName+@fnID+';'
EXECUTE (@sql)
(or EXECUTE (@sql) AT LinkedServerName if you want to run on a server other than the one you are running on)
But of course this (and also the version using OPENQUERY) is very insecure because @storedProcedureName and @fnID could be pretty well anything: unless you can guarantee that this stored procedure can only be called by trusted code which ensures that the strings passed in are valid ones you are wide open to an SQL Injection attack. So that raises the question: if the code making the call can pass in the stored procedure's name and the fnID which has to be attended to get the real target procedure's name, why can't it just call that target stored procedure in the first place, and avoid this security issue by using the protection provided by ADO for procedure names and parameters?
Tom
April 17, 2011 at 4:13 am
Because, i want to have one code with whom i want to call datasources for my forms. I use FoxPro9 and SQL SErver 2008, and code is that write to call individual stored procedure to be datasource for individual forms.
In property you write the name of stored procedure and pnID and then with this parameter my CallerStoredProcedure decide which stored procedure is executing.
I hope you understand me.
Is it possible do something like this:
set@sql=' select * from resultStoredProcedure
where myCondition'
Thanks
April 17, 2011 at 5:40 pm
nikplic (4/17/2011)
Is it possible do something like this:set@sql=' select * from resultStoredProcedure
where myCondition'
Thanks
No, there's a problem with the set of rows returned by a stored procedure - you can't use a stored procedure as a subquery (ie make a table valued expression out of it). You would need to rewrite the stored procedure as a table-valued function, and then you could do it. If it's written as a function you can use it in a statement like
select * from dbo.resultFunction() where myCondition order by myOrder
- or in fact do anything that can be done in a select statement.
You can find information about creating functions at this url and example C near the bottom of that page is an example of a table valued function.
Incidentally, you can't call a stored procedure using OPENQUERY either. I imagine that this is because some stored procedures don't return a single rowset; some return no rowset (not even an empty one), others return multiple rowsets (perhaps with different sets of columns).
Tom
April 17, 2011 at 9:09 pm
Tom.Thomson (4/17/2011)
Incidentally, you can't call a stored procedure using OPENQUERY either.
Maybe not, but OPENROWSET works a treat.
SELECT *
FROM OPENROWSET('SQLOLEDB','Server=YourServer\InstanceHere;Trusted_Connection=Yes;Database=Master',
'Set FmtOnly OFF; EXEC dbo.sp_Who')
--Jeff Moden
Change is inevitable... Change for the better is not.
April 18, 2011 at 12:21 am
Jeff, is this possible?
SELECT *
FROM OPENROWSET('SQLOLEDB','Server=YourServer\InstanceHere;Trusted_Connection=Yes;Database=Master',
'Set FmtOnly OFF; EXEC dbo.'+@spName+''+@pnID+')
where myCondition
//spName is name of stored procedure which i want to call
//pnID is parameter of that stored procedure
Can you please to write code, because i don't know sintax the best and have error?
thanks
April 18, 2011 at 5:59 am
You can't have variables in OPENROWSET. Instead, you have to make the whole thing a dynamic statment. With that comes the possiblity of SQL INJECTION. You need to Google for that and see whatelse you can do to prevent that in the following:
DECLARE @SQL NVARCHAR(4000),
@ProcName NVARCHAR(128),
@SPID NVARCHAR(10),
@Condition NVARCHAR(128)
SELECT @ProcName = 'sp_who',
@SPID = '1',
@Condition = 'background'
SELECT @SQL =
N' SELECT *
FROM OPENROWSET
(
''SQLOLEDB'',
''Server=YourServer\Instance;Trusted_Connection=Yes;Database=YourDB'',
''Set FmtOnly OFF; EXEC dbo.@ProcName @SPID''
)
WHERE Status = @Condition',
@SQL = REPLACE(
REPLACE(
REPLACE(
@SQL
,'@ProcName',QUOTENAME(@ProcName,'"'))
,'@SPID',QUOTENAME(@SPID,'"'))
,'@Condition',QUOTENAME(@Condition,''''))
PRINT @SQL
EXEC (@SQL)
Of course, you need to look for things in that code that begin with the word "Your" because you need to hardcode replace your server and db information there.
And don't shine on the warning about SQL INJECTION. It's a serious threat. I just don't have the time to make sure I have all the bases covered in the code above.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 18, 2011 at 6:07 am
I'll also state that "genericising" stored procs like this is a really bad idea. Be very careful. And, I'll warn again, I've not tested the above for SQL INJECTION possibilities. GOOGLE for SQL INJECTION and find out what it's about. The reason I didn't use sp_executesql is I just didn't have the time to do it but it should probably be converted.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 18, 2011 at 6:22 am
nikplic (4/17/2011)
Because, i want to have one code with whom i want to call datasources for my forms. I use FoxPro9 and SQL SErver 2008, and code is that write to call individual stored procedure to be datasource for individual forms.In property you write the name of stored procedure and pnID and then with this parameter my CallerStoredProcedure decide which stored procedure is executing.
I hope you understand me.
Is it possible do something like this:
set@sql=' select * from resultStoredProcedure
where myCondition'
Thanks
What's wrong with SQLEXEC?
Without VFP in front of me, it would look something like this:
lcSQLString =[EXEC resultStoredProcedure ] + myparameter
lnResult = SQLEXEC(gnConnHandle, lcSQLString, sqlresults)
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
April 18, 2011 at 7:08 am
ChrisM@home (4/18/2011)
nikplic (4/17/2011)
Because, i want to have one code with whom i want to call datasources for my forms. I use FoxPro9 and SQL SErver 2008, and code is that write to call individual stored procedure to be datasource for individual forms.In property you write the name of stored procedure and pnID and then with this parameter my CallerStoredProcedure decide which stored procedure is executing.
I hope you understand me.
Is it possible do something like this:
set@sql=' select * from resultStoredProcedure
where myCondition'
Thanks
What's wrong with SQLEXEC?
Without VFP in front of me, it would look something like this:
lcSQLString =[EXEC resultStoredProcedure ] + myparameter
lnResult = SQLEXEC(gnConnHandle, lcSQLString, sqlresults)
Now we know why I don't write GUI code. 😛 Nicely done, Chris.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 18, 2011 at 7:58 am
Jeff Moden (4/18/2011)
ChrisM@home (4/18/2011)
nikplic (4/17/2011)
Because, i want to have one code with whom i want to call datasources for my forms. I use FoxPro9 and SQL SErver 2008, and code is that write to call individual stored procedure to be datasource for individual forms.In property you write the name of stored procedure and pnID and then with this parameter my CallerStoredProcedure decide which stored procedure is executing.
I hope you understand me.
Is it possible do something like this:
set@sql=' select * from resultStoredProcedure
where myCondition'
Thanks
What's wrong with SQLEXEC?
Without VFP in front of me, it would look something like this:
lcSQLString =[EXEC resultStoredProcedure ] + myparameter
lnResult = SQLEXEC(gnConnHandle, lcSQLString, sqlresults)
Now we know why I don't write GUI code. 😛 Nicely done, Chris.
LOL! I know exactly why you don't write GUI code Jeff - having recently inherited a website from a reluctant hobby developer. 25 years of xBase isn't helping at all.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
April 18, 2011 at 10:52 am
Jeff Moden (4/17/2011)
Tom.Thomson (4/17/2011)
Incidentally, you can't call a stored procedure using OPENQUERY either.Maybe not, but OPENROWSET works a treat.
Interesting. I wonder why MS decided to forbid calling a stored proc with OPENQUERY but not with OPENROWSET. It seems a strange thing to do.
edit: I'm losing it! :blush: It's not Stored Procedures that are banned, but Extended Stored Procedures. I am going to check everything in BoL in future, I've got too out of touch with things. Spending too much time on learning Castellano and Galego and not enough on SQL, I guess.
Tom
April 18, 2011 at 11:38 am
Tom.Thomson (4/18/2011)
Jeff Moden (4/17/2011)
Tom.Thomson (4/17/2011)
Incidentally, you can't call a stored procedure using OPENQUERY either.Maybe not, but OPENROWSET works a treat.
.... Spending too much time on learning Castellano and Galego[/i] and not enough on SQL, I guess.
Spend some time there Tom, make us all really jealous.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
April 18, 2011 at 5:28 pm
ChrisM@home (4/18/2011)
Jeff Moden (4/18/2011)
ChrisM@home (4/18/2011)
nikplic (4/17/2011)
Because, i want to have one code with whom i want to call datasources for my forms. I use FoxPro9 and SQL SErver 2008, and code is that write to call individual stored procedure to be datasource for individual forms.In property you write the name of stored procedure and pnID and then with this parameter my CallerStoredProcedure decide which stored procedure is executing.
I hope you understand me.
Is it possible do something like this:
set@sql=' select * from resultStoredProcedure
where myCondition'
Thanks
What's wrong with SQLEXEC?
Without VFP in front of me, it would look something like this:
lcSQLString =[EXEC resultStoredProcedure ] + myparameter
lnResult = SQLEXEC(gnConnHandle, lcSQLString, sqlresults)
Now we know why I don't write GUI code. 😛 Nicely done, Chris.
LOL! I know exactly why you don't write GUI code Jeff - having recently inherited a website from a reluctant hobby developer. 25 years of xBase isn't helping at all.
Exactly! 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
April 18, 2011 at 5:33 pm
Tom.Thomson (4/18/2011)
Jeff Moden (4/17/2011)
Tom.Thomson (4/17/2011)
Incidentally, you can't call a stored procedure using OPENQUERY either.Maybe not, but OPENROWSET works a treat.
Interesting. I wonder why MS decided to forbid calling a stored proc with OPENQUERY but not with OPENROWSET. It seems a strange thing to do.
edit: I'm losing it! :blush: It's not Stored Procedures that are banned, but Extended Stored Procedures. I am going to check everything in BoL in future, I've got too out of touch with things. Spending too much time on learning Castellano and Galego and not enough on SQL, I guess.
Heh... nah... I'm right there with ya, Tom. I didn't even check to see what OPENQUERY would actually do.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply