June 21, 2013 at 1:14 am
How do we validate a string query before executing the query.
e.g I have a table dbo.Test with columns A int, B int and C int.
I have a three VARCHAR variables
@VChrTotalString, @VChrString and @VChrFilterCondition (Filter condition can have multiple conditions)
@VChrString = 'SELECT * FROM dbo.Test WHERE '
@VChrFilterCondition = ' C = 5'
@VChrTotalString = @VChrString + @VChrFilterCondition
We do EXEC sp_executesql @VChrTotalString .
This runs fine and is perfectly legitimate.
Suppose @VChrFilterCondition = 'C=5 AND B IN (8,7,5,6) OR A1 NOT IN (4,2)'
Here A1 is not a valid column in the table.
If we execute this query we will get and error saying Invalid column A1.
I want to show a custom message for the same saying "Invalid Filter condition"
but for that i want value from the EXEC sp_executesql @VChrTotalString.
on which I can display a message.
If we use SET PARSEONLY ON;
Then this query is parse correctly
SELECT * FROM dbo.Test WHERE A1 = 2
Is there any way of capturing the error before executing the query.
Can you please help me resolving this issue.
June 21, 2013 at 5:26 am
Hi Friends,
Any thoughts on this !!
June 21, 2013 at 5:41 am
Rather than looking to validate first, can you not use TRY...CATCH and then use ERROR_MESSAGE, ERROR_NUMBER for example?
'Only he who wanders finds new paths'
June 21, 2013 at 5:48 am
Hi, Thanks for the reply.
I already have a TRY - CATCH in place.
Presently my script is having 3 input table names and there are 3 filter strings.
There could be error in any combination pair.
The CATCH block directly raises the error. "Invalid column A1"
So the question.
June 21, 2013 at 6:59 am
To be honest, if the user knows enough SQL to write down the select statement, they are probably good enough to understand the original error message.
Short of trying to parse and understand the query yourself i don't see how to do it.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply