Hello there
I've build store proecdure that create dinamic sql sentences for updating
data.
Is there a way to check if the sencence is valid before running it?Roy,shalom
Yes it is
CREATE TABLE #Test (col INT)
INSERT INTO #Test VALUES (1)
DECLARE @.str VARCHAR(50),@.col INT
SET @.col=5
SET @.str='UPDATE #Test SET col='+CAST(@.col AS VARCHAR(10))
--EXEC (@.str)
PRINT (@.str)
SELECT * FROM #Test
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:OVHrU1LUGHA.5500@.TK2MSFTNGP12.phx.gbl...
> Hello there
> I've build store proecdure that create dinamic sql sentences for updating
> data.
> Is there a way to check if the sencence is valid before running it?
>|||[Reposted, as posts from outside msnews.microsoft.com does not seem to make
it in.]
Roy Goldhammer (roy@.hotmail.com) writes:
> I've build store proecdure that create dinamic sql sentences for updating
> data.
> Is there a way to check if the sencence is valid before running it?
In SQL 2005 you could embed the query in SET PARSEONLY ON and put it in
a TRY/CATCH handler. But that will not catch all errors, like misspelled
column names or misspelled table names. I guess you can catch these if
you use SET FMTONLY ON instead, but that will produce a result set with
metadata to the client, which is likely confuse it.
Working with dynamic SQL means that you have to test carefully, and by
other means ensure that you do not generate syntax errors at run-time.
A very important tool to achieve this is that you build parameterised
queries that you run with sp_executesql. If you interpolate all values
into the SQL string and run with EXEC(), there are more risk for problems.
Also, make sure that you use quotename for all object names you interpolate
into the string.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.seBooks Online for SQL
Server 2005
athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000
athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Consider SET FMTONLY ON and SET PARSEONLY ON. However, if the programming to
create the T-SQL statements is correct, then this should not be a recuring
problem.
If you are talking about dynamic SQL as in the entire structure of statement
(not just parameters) is created on the fly, then perhaps this programming
would be easier to implment on the application side. A class can be written
that exposes properties for table names, joins, column names, filter
expressions, etc. and then a few hundred lines of C# coding could assemble a
properly formatted select statement.
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:OVHrU1LUGHA.5500@.TK2MSFTNGP12.phx.gbl...
> Hello there
> I've build store proecdure that create dinamic sql sentences for updating
> data.
> Is there a way to check if the sencence is valid before running it?
>
