May 12, 2011 at 8:49 am
OK, this is a new one for me. I'm working on a tool that will be used at design time. A user/developer will set some properties and I'll go off and effectively generate some SQL code. I'd like to give the user/developer the ability to set as one of the properties a SQL Select statement that returns a value. I'd expect the statement to be something like "SELECT CompanyId + ' ' + CompanyName from dbo.Company where CompanyId = ?". Think of it as setting the value of a group header.
What I'd like to do at a minimum is to validate that the string provided is at least syntactically correct. Something along the lines of:
If dbo.fnIsSQLValid(@sql) THEN
--continue
ELSE
--error
Of course, ideally I'd like to ensure that only one column is being returned. I'm not having much luck in finding an easy way to do this. Ideally I'd like to avoid actually executing the statement. And by the way, I'm not too concerned about SQL injection as this will be an internal, design time only tool with relatively expert users; I'm just trying to catch fat-fingered SQL statements not malicious intent. Any suggestions?
May 12, 2011 at 8:49 am
Check out "SET FMTONLY ON"
May 12, 2011 at 11:25 am
I've looked at that and SET PARSEONLY ON; both seem to work fine interactively but I can't get them to work inside a function.
May 12, 2011 at 11:28 am
Why can't you call it form within the app and not sql server?
May 12, 2011 at 3:12 pm
Basically the architecture of what I'm trying to fit this into -- pretty much everything happens in SQL inside stored procs, so at this point I'm not really allowed any other options.
I didn't think this would be so hard -- i thought there had to be something out there that I could pass a sql string to and get back a boolean good/bad flag. Sigh.
May 12, 2011 at 3:20 pm
You could always use a stored prod and do something like this...
Either use format only or parse only (never used that one so not sure what it does or how it works).
Pseudo-almost-code 🙂
TRY
EXEC (@Mystring_with_fmtonly)
catch
--process errors here
else go for it!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply