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.
| Author |
Topic |
|
goranzr85
Starting Member
5 Posts |
 Posted - 2011-04-16 : 07:09:55
|
| Hi, GuysI 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 thatThanks |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-04-16 : 22:00:29
|
| What you have indicated is one way (although you would want to pass in the value of @fnID rather than the variable @fnID in the string).If the only purpose is to execute stored procedures based on parameters, you could use dynamic sql using sp_executesql as described here: http://msdn.microsoft.com/en-us/library/ms188001.aspxWhen you use dynamic sql there is a risk of SQL injection. If the list of stored procs that will be passed in as parameter are known in advance, you could use if statements to select which stored proc to execute, eliminating any risk of injection attacks.My knowledge of security and SQL injection attacks is limited, but I do know that it is a serious risk, even to security experts: http://www.informationweek.com/news/security/attacks/showArticle.jhtml?articleID=229400764&cid=RSSfeed_IWK_Allhttp://www.eweek.com/c/a/Security/Security-Firm-Barracuda-Networks-Embarrassed-by-Hacker-Database-Breakin-729619/ |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-04-17 : 01:43:58
|
| if you're certain on what all stored procs you want to call based on parameters you pass, you can even achieve this using series of if else statements. also if possible use direct call to procedure rather than using openquery unless you're procs are in different server.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
goranzr85
Starting Member
5 Posts |
Posted - 2011-04-17 : 05:52:20
|
| But I have around 60-70, maybe more stored procedures, that is to many to use if else...visakh16, can you,please, show me example how to use direct call to procedure and then select * from result of stored procedure because i must then use:select * from resultSPwhere myConditionAll my procs are in same server.Thanks |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-04-17 : 06:00:58
|
| oh you want to select from it? are resultset of all procs similar? if yes,use temp table to take it and then use it in select------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
goranzr85
Starting Member
5 Posts |
Posted - 2011-04-17 : 06:21:46
|
| yes, i want to select from result sp and use where statement.But, my resultset isn't similar in all my procs, and that is a problem.Is any other solution? |
|
|
|
|
|
|
|
|
OpenQuery and stored procedure