Author |
Topic |
SQLIsTheDevil
Posting Yak Master
177 Posts |
Posted - 2008-04-17 : 15:42:01
|
Pretty confusing Subject line, I know. I have a procedure where I wish to employ set-based logic. The problem is I'm passing in input parameters. Why do I think this is a problem? Well, IIRC, dynamic sql is in its own scope and does not get cached. Doesn't that mean the procedure will be compiled each time its called? This can affect performance, no doubt. Is there a way to cache dynamic sql or is there another way to write a select query using variables without dynamic sql (stupid question but I thought why not ask)?Thank you. |
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-04-17 : 15:49:48
|
We'd have to see the dynamic SQL code to help. You can reference the dynamic WHERE clause and dynamic ORDER BY articles found here for some alternative solutions.You can get better dynamic SQL performance by using sp_executesql rather than EXEC(@SQL).Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
SQLIsTheDevil
Posting Yak Master
177 Posts |
Posted - 2008-04-17 : 16:03:05
|
thank you.quote:
CREATE Procedure sp_test @input varchar(5) ASSelect * From SomeTableWhere ColumnName = @input
Of course, the above didn't work. So I'm using this:quote:
CREATE Procedure sp_test @input varchar(5) AS@Declare @SQL varchar(100)Set @SQL = 'Select * From SomeTableWhere ColumnName = ''' + @input + ''''exec(@SQL)
I'll switch to sp_executesql, thanks. But this is pretty much the procedure. There are more variables I'm passing in and the D-SQL statement is a lot more complex but I'm trying to understand on a more conceptual standpoint, here. I'm thinking there must be a way to force caching on D-SQL but I'm not sure. I ask myself what is the point of d-sql in a procedure if it's going to be cached every time? I thought procedures are written for optimization. It's probably still faster, though, than writing dynamic sql in server-side script. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-04-17 : 16:07:18
|
There is nothing wrong with this (except that you shouldn't prefix stored procedures with sp_ for performance reasons):CREATE Procedure sp_test @input varchar(5) ASSelect * From SomeTableWhere ColumnName = @inputSo you need to provide a better example.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
SQLIsTheDevil
Posting Yak Master
177 Posts |
Posted - 2008-04-17 : 17:29:36
|
Yea, just did some reading up on the sp_ prefix. Gotchya.Yes, you're right. Very bad example. Ugh.here's a better one:quote:
-- continued from same proceduredeclare @where_clause varchar(50)set @where_clause = 'ColumnName Like ''%' + @input + '%''' Select * From SomeTableWhere + @WhereClause
Is it possible to do something like this or must I use D-SQL? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-04-17 : 18:56:09
|
Dynamic SQL is not required for that:CREATE Procedure usp_test @input varchar(5) ASSelect * From SomeTableWhere ColumnName LIKE '%' + @input + '%'Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-04-18 : 03:04:19
|
quote: Originally posted by SQLIsTheDevil Yea, just did some reading up on the sp_ prefix. Gotchya.Yes, you're right. Very bad example. Ugh.here's a better one:quote:
-- continued from same proceduredeclare @where_clause varchar(50)set @where_clause = 'ColumnName Like ''%' + @input + '%''' Select * From SomeTableWhere + @WhereClause
Is it possible to do something like this or must I use D-SQL?
Make sure you read this fully to understand the use of Dynamic SQLwww.sommarskog.se/dynamic_sql.htmlMadhivananFailing to plan is Planning to fail |
|
|
SQLIsTheDevil
Posting Yak Master
177 Posts |
Posted - 2008-04-18 : 14:29:28
|
Yes, I've read much of that article, thank you. It seems I will have to use d-sql, however, for the where clause may have multiple conditions (I used one where condition for conceptuation, but what I require in actuality is not as easy as pie). Unless there's a way to build sql statements in blocks, similarly to building in other languages (i.e. in php, $sql = "Select * From Table "; $sql .= "Where Column = 1";), d-sql is the only way. If I'm correct, d-sql will force the procedure to recompile on every call but I can't avoid it. Why do I say that d-sql is the only way? Let's say I have 4 where conditions that are correlated with 4 input parameters, and I don't know ahead of time how many of said conditions I may need. Why is that? Take a look at this:quote:
Create Procedure Example (@input1 varchar(50) = null , @input2 varchar(50) = null, @input3 varchar(50) = null, @input4 vachar(50 = null)ASdeclare @where_condition1 varchar(50)declare @where_condition2 varchar(50)declare @where_condition3 varchar(50)declare @where_condition4 varchar(50)set @where_condition1 = 'ColumnName1 Like ''%' + @input1 + '%''' set @where_condition2 = 'ColumnName2 Like ''%' + @input2 + '%''' set @where_condition3 = 'ColumnName3 Like ''%' + @input3 + '%''' set @where_condition4 = 'ColumnName4 Like ''%' + @input4 + '%''' --create query here...
See why I made the input parameters null? The procedure could take 1 input; or 2 inputs; or all 4 inputs; or none at all. That makes the query a bit more complicated because the where clause has to be dynamically built to account for how many inputs are given values. I hope that makes sense. So, unless there's a way I can make a non-dynamic query that takes care of using only input variables take contain non-null values, I must use d-sql. There where clause is a bit tricky. I need to insert "and" between each where condition. Again, I may have no where conditions or I may have up to 4 where conditions, which means I could have up to 3 "and"'s. I don't want to further confuse or convolute my problem; so, I'll simply ask what is the most efficient way to build the where clause?Thank you very much for the advice and wisdom. Much appreciated. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
SQLIsTheDevil
Posting Yak Master
177 Posts |
Posted - 2008-04-18 : 15:07:30
|
Wow, you are a life saver. Thank you very much! |
|
|
|