'Dynamic' queries in stored procedures

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

General SQL Server Forums

New to SQL Server Programming

'Dynamic' queries in stored procedures

Author

Topic

kpgraci
Yak Posting Veteran

68 Posts

Posted - 2010-11-23 : 15:30:28

Hi all,

You guys have answered a lot of my sql questions in the past, great forum!

I have a 'business form search' routine that has to do lot of logic in order to build the query needed to get the result set. The user can enter one or more parameters, like name, state, category, and I build a query that searches multiple fields for any matches.

I do this is a routine and build the sql query, but I want to use a stored procedure instead.

Is there a way to do logic in a stored procedure, like IF compares and such? Or is there a way to write this query so it is static and still return the same results?

Thanks,
kpg

Here is a snippet of what I'm doing in code, as you can see there is quite a bit of processing going on:

sb.Append("SELECT ID, FormKey, State, Code, Version, FormName, FROM Forms ")
If State <> "All" Then
If bWHERENeeded Then sb.Append(" WHERE ") : bWHERENeeded = False
If bANDNeeded Then sb.Append(" AND ")
bANDNeeded = True
If State.Length = 3 Then 'modifier present
Dim modifier As String = State.Substring(0, 1)
State = State.Substring(1, 2)
Select Case modifier
Case "!"
sb.Append(" (State <> @State) ")
Case "="
sb.Append(" (State = @State) ")
Case Else 'error
sb.Append(" (State = @State) ")
End Select
Else
sb.Append(" (State = 'XX' OR State = @State) ")
End If
End If

If Search.Length <> 0 Then
If bWHERENeeded Then sb.Append(" WHERE ") : bWHERENeeded = False
If bANDNeeded Then sb.Append(" AND ")
bANDNeeded = True
sb.Append(" (FormName LIKE '%" & Search & "%') OR (State LIKE '%" & Search & "%') OR (Code LIKE '%" & Search & "%') OR (Version LIKE '%" & Search & "%') ")
End If

If CategoryID > 0 Then
If bWHERENeeded Then sb.Append(" WHERE ") : bWHERENeeded = False
If bANDNeeded Then sb.Append(" AND ")
bANDNeeded = True
sb.Append(" (FormKey IN (SELECT FormKey FROM FormCategories WHERE CategoryID = @Category)) ")
End If

If LOBID <> 0 Then
If bWHERENeeded Then sb.Append(" WHERE ") : bWHERENeeded = False
If bANDNeeded Then sb.Append(" AND ")
bANDNeeded = True
sb.Append(" (FormKey IN (SELECT FormKey FROM FormLOBs WHERE LOBID = @LOB OR LOBID = @NEGLOB)) ")
End If

kpg

chadmat
The Chadinator

1974 Posts

Posted - 2010-11-23 : 15:47:16

Yes, you can do the logic in T-SQL and build a string of Dynamic SQL that you then Execute.

-Chad

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-24 : 09:22:11

you can make parameters optional and then use logic like below in procedure


....
WHERE (field1 = @name OR @name IS NULL)
AND (state = @state OR @state IS NULL)
AND (category = @category OR @category IS NULL)

and make default value of all parameters as NULL

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-26 : 03:15:27

quote:
Originally posted by chadmat

Yes, you can do the logic in T-SQL and build a string of Dynamic SQL that you then Execute.

-Chad

The code that Visakh posted is simple and error free

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources