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.
| Author |
Topic |
|
cbtoolkit
Starting Member
3 Posts |
 Posted - 2004-12-15 : 15:38:33
|
| I want to use a single view and have the where clause be dynamic - actually passing the where clause to a stored procedure. I'm attempting to do this in query analyzer and receiving an error. Please see the code below:Declare @whstr as varcharSet @whstr = "IsComplete = 0 and IsInactive <> 0 and (CharIndex('S@',STAALL)+CharIndex('GC',STAALL)>0)"Select * from vwCallList_TZWhere @whstr ORDER BY CallCount, DelTotalI receive an error that says "Incorrect syntax near the keyword 'ORDER'.If I remove the order by portion of the sql string, the error simply moves up a line and specifies @whstr.Any ideas?Thanks.Matthew MoranThe IT Career Builder's Toolkithttp://www.cbtoolkit.com |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2004-12-15 : 16:04:12
|
| 1) No, you cannot do this syntactically. You can add some logic to your stored procedure (e.g., IF statment) or to your WHERE clause (e.g., CASE) or you can use dynamic SQL (See BOL).2) Your current declaration of the variable "@Whstr" will default to a length of one. If you print out its value, you'll see that it equals 'I'.HTH=================================================================Happy Holidays! |
 |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-12-15 : 16:12:06
|
| [code]Declare @whstr as varchar(8000), @sql varchar(8000)Set @whstr = 'IsComplete = 0 and IsInactive <> 0 and (CharIndex(''S@'',STAALL)+CharIndex(''GC'',STAALL)>0)'Set @sql = 'Select * from vwCallList_TZ Where ' + @whstr + ' ORDER BY CallCount, DelTotal 'SELECT @SQL--EXEC(@SQL)[/code]Brett8-) |
|
|
|
cbtoolkit
Starting Member
3 Posts |
Posted - 2004-12-15 : 16:23:16
|
| X002548,Your example worked awesome! Does the trick.....I am putting into test right now.Thanks,MattMatthew MoranThe IT Career Builder's Toolkithttp://www.cbtoolkit.com |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-12-15 : 16:25:34
|
| OK, but you really should minimize dynamic sqlHow many combination could you possibly have?Brett8-) |
|
|
|
cbtoolkit
Starting Member
3 Posts |
Posted - 2004-12-15 : 16:31:51
|
| The challenge is that there are about 10 different fields that possible come into play and many iterations. Sometimes they are used in the where clause, sometimes not, sometimes in combination, etc.Otherwise, I have to alter my stored-procedure logic everytime they want to add the various combinations of fields to use.The fact is, there is only one stored produre where this is needed but flexibility is an absolute must.Matthew MoranThe IT Career Builder's Toolkithttp://www.cbtoolkit.com |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
|
|
|
|
Question about variables as a where clause..