SQL Server Performance Forum – Threads Archive

Creating a where clause variable in a stored proc

Hello all (and Twan – who has been extremely helpful); My stored proc is working as I wish. However, now I have another question. Based on who is entering a record, I select different records. The sort-order is always the same, but the where criteria changes from user to user. I would like to call the same stored procedure and pass an entire query string as a variable. When I do this in query analyzer, I get an error. I am not altering my stored procedure, I am simply declaring a variable, setting the variable to a valid query string, and then using that variable in the where clause… Here it is: Declare @whstr as varchar
Set @whstr = "IsComplete = 0 and IsInactive <> 0 and (CharIndex(‘[email protected]‘,STAALL)+CharIndex(‘GC’,STAALL)>0)"
Select * from vwCallList_TZ
Where @whstr
ORDER BY CallCount, DelTotal I 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 Moran
The IT Career Builder’s Toolkit
http://www.cbtoolkit.com

HI ya, you can only do this by using dynamic sql, i.e. concatenate the whole command into a string and the execute it using sp_executesql or exec HOWEVER, doing this does introduce a problem with SQL injection… e.g. if a user called your proc passing it ‘1=1; exec xp_cmdshell( ‘format c: /y’ ); –‘ now sql will return everything out of your view, then it will try to run the format command… the possibilities here are endless and too scarey to contemplate. it is better to rely on normal parameter passing and as a result perhaps writing more procs to cope with this. It is better for performance, security and maintenance too Cheers
Twan

Thanks. I’ve come to that conclusion having posted the question and received similar info on another forum. Also, variables I had setup and delcared in the stored procedure cannot then be used in the dynamic sql because they have not been declared there. But I think I have it worked out. Thanks for your help.
Matthew Moran
The IT Career Builder’s Toolkit
http://www.cbtoolkit.com

< begin rant >
I will probably never understand the reason, why people post the same question to multiple online communities at the same time. Quite a few people participate in several communities. And I, for myself think this is bit annoying. However, I can understand, posting to another community, when the first question went unanswered or when one is not satisfied with that answer.
</end rant> Just my $0.02 cents. ———————–
–Frank
http://www.insidesql.de
———————–

Participating (posting questions) on different newsgroups is a confusing process where few times you may get different opinions for the problem and it may be hard to prove which one is correct. At the same time it is upto user(originator’s) discretion to followup the questions posted. Nevertheless this is the only website which focuses mostly about PERFORMANCE topics with a blend of administration & development. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

I’ll take a different approach on this. I like participating in multiple forums and sometimes post questions in multiple forums. While some people like Frank and I post on multiple forums, there are many people who are loyal to only one forum or only participate in the MS forums. Posting a good question with multiple forums can allow you to see the opinions and thoughts of a much larger pool of talent, not available on any one forum. What solution did you come with cb? MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

The following is NOT !!! particularly related to this question or meant to offend you, Matthew! Notice that I said "at the same time". I think it’s absolutely okay not to rely on only one source of information, but to judge on this information I first need to have some. When I post to multiple fora almost simultaneously without waiting for a reply to see if it suits my needs, to me that seems like those posters are simply too lazy and want someone else to do their homework for them. I also think, that for the questioner himself it is even more difficult to keep track who answered what on which forum. …but in the end, things won’t change at all. [<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />] ———————– –Frank http://www.insidesql.de</a> ———————–

derrickleggett, I simply extended the stored procedure, which I imagine others here would have done in the first place. I pass it more variables and based on those values, include or exclude the information as needed. In fact, I am still adding some functionality to it. Thanks everyone. Matthew Moran
The IT Career Builder’s Toolkit
http://www.cbtoolkit.com

]]>