Dynamic SQL within Stored Proc without using sp_executesql
-
Does anyone know how to execute a dynamically generated sql statement within a stored procedure without using the sp_executesql procedure? We have a need to pass from the user 15 different parameters and 4 different grouping options which can be a value or can be "not applicable".
-
What's wrong with sp_executesql?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
My DBA doesn't want me to use it because of performance and security concerns.
-
can't you use a view ?
* Noel -
How would a view help?
-
With a view
1. you don't have to use dynamic sql.
2. you don't have to grant direct access to the tables
3. you can create the statements on the client against the view.
* Noel -
We are required to access the database through stored procedures. So the client couldn't just select against a view in our situation. Thanks for the idea though
-
Hey, your DBA is a wise man.
Actually you can use far more than 15 parameters in an s_proc and for the grouping options there is also another solution. I'm sure you'll find something useful here. I rechecked the article today because of a foolish answer in another thread.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
ok, if that is your only option :
1. READ the article that Frank posted!
2. besides trying to do all in one procedure with one single query sometimes it makes sense to write several stored procedures that are called from the main one depending on the input conditions
3. consider the use of temp tables it may help with the dynamic sql and security issues
HTH
* Noel -
Thanks Frank, that article has some great ideas!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply