View With Table-Queried Parameters versus Sproc?

Question

View With Table-Queried Parameters versus Sproc?

larsosman56

SSC Enthusiast

Points: 111
More actions
January 6, 2009 at 8:05 am

#133478

Hi there,
I am trying to make a design decision related to accessing the same core set of data with a number of different parameters (like an API). Some of the queries will require accesing multiple tables, and the parameters for the query most likley won't be know until runtime.
As an alternative to Dynamic SQL, is there any merit to throwing the value of the query parameters into a seperate small table just before the query is run and then have the query itself query the parameters table:
SELECT sometable.somedata FROM sometable
WHERE somekey.sometable = (SELECT someotherkey FROM anothertable)
Obviously, I would use this approach with a View. I would have to maintain the parameters table on the fly, so it would require some work. Does this seem like overkill to simply building a parameter-driven sproc? Does a View provide signficant enough advantges over an sporc to go through this? I suspect not but thought I would check.
My thanks!
I can add some sample code, but I think the question is basic enough. If note, I am happy to add some ideas.

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

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

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 1

From what you're saying, dynamic TSQL is probably the way to go.

In a system where anything can be queried, any way, at any time, performance is usually an issue. It's hard to index systems that don't have coherent logic.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

larsosman56 SSC Enthusiast Points: 111 More actions · Answer 2

Thanks Grant. By Dynamic TSQL, do you mean the approach I described of pulling the value of the parameters from a table updated just before the query is fired? Or are you referring to the dynamic SQL run by EXEC?

Thanks again.

l

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 3

I mean building queries as strings and running them. I'd suggest using sp_executesql, but it amounts to the same thing as exec. Although, you can get some parameterization, and therefore code reuse, with sp_executesql.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning