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.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 making dynamic select query w/o using dynamic sql?

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 Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2008-04-17 : 16:03:05
thank you.

quote:


CREATE Procedure sp_test @input varchar(5) AS

Select *
From SomeTable
Where 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 SomeTable
Where 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.
Go to Top of Page

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) AS

Select *
From SomeTable
Where ColumnName = @input

So you need to provide a better example.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

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 procedure

declare @where_clause varchar(50)

set @where_clause = 'ColumnName Like ''%' + @input + '%'''

Select *
From SomeTable
Where + @WhereClause





Is it possible to do something like this or must I use D-SQL?
Go to Top of Page

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) AS

Select *
From SomeTable
Where ColumnName LIKE '%' + @input + '%'


Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

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 procedure

declare @where_clause varchar(50)

set @where_clause = 'ColumnName Like ''%' + @input + '%'''

Select *
From SomeTable
Where + @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 SQL
www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

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
)

AS

declare @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.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-18 : 14:32:54
You can handle this with COALESCE which is described in the dynamic WHERE clause article I mentioned. So you get a dynamic query without dynamic SQL.

http://www.sqlteam.com/article/implementing-a-dynamic-where-clause

Also read this:
http://www.sqlteam.com/article/dynamic-order-by

And make sure to check out the comments for the data type issues.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2008-04-18 : 15:07:30
Wow, you are a life saver. Thank you very much!
Go to Top of Page
   

- Advertisement -