sattesonjr
asked on
SQL column validation "pre-check"
I've got a standard select statement (inside a large stored procedure) where I'm pulling the value of nDIFF_SEC from a table where the nINDEX column matches @nINDEX.
SELECT @nDIFF_SEC = nDIFF_SEC FROM TableOwner.TableName WHERE nINDEX = @nINDEX
This select statement is inside a large while loop. The problem is that SQL errors out at the beginning of the loop, long before ever getting to this statement, but still referencing this program line, saying that nDIFF_SEC is an invalid column. At the beginning of the loop, that is a correct statement, because the beginning part of the loop adds the column nDIFF_SEC. However, by the time execution gets to the select statement, the column IS there. It's almost like SQL is trying to be too smart and is doing some kind of pre-check of all the tables refereenced inside the loop, and validates their columns. Does anyone know anything about this behavior? Is there a way to turn this "pre-check" off?
If nobody has seen this before, an alternate work-around might just be to turn the statement into a character string and execute it, so that SQL is forced to compile it at time of exectution. I don't know how to do this, though, because I have to pass @nDIFF_SEC out of the execute and back to the stored procedure. Here's an example of what I'd like to do, but these statements won't work because I can't pass out @nDIFF_SEC, and I haven't declared @nDIFF_SEC as a variable inside the execute session.
SET @query = SELECT @nDIFF_SEC = nDIFF_SEC FROM TableOwner.TableName WHERE nINDEX = @nINDEX
EXECUTE @query = 'SELECT @nDIFF_SEC = nDIFF_SEC FROM TableOwner.TableName WHERE nINDEX = ' + CONVERT(VARCHAR, @nINDEX);
Any ideas? (I don't want to have to call another stored procedure just for this one statement) I'd like to turn the precheck off, but I'd settle for getting the work around working also. Thanks in advance for your help.
SELECT @nDIFF_SEC = nDIFF_SEC FROM TableOwner.TableName WHERE nINDEX = @nINDEX
This select statement is inside a large while loop. The problem is that SQL errors out at the beginning of the loop, long before ever getting to this statement, but still referencing this program line, saying that nDIFF_SEC is an invalid column. At the beginning of the loop, that is a correct statement, because the beginning part of the loop adds the column nDIFF_SEC. However, by the time execution gets to the select statement, the column IS there. It's almost like SQL is trying to be too smart and is doing some kind of pre-check of all the tables refereenced inside the loop, and validates their columns. Does anyone know anything about this behavior? Is there a way to turn this "pre-check" off?
If nobody has seen this before, an alternate work-around might just be to turn the statement into a character string and execute it, so that SQL is forced to compile it at time of exectution. I don't know how to do this, though, because I have to pass @nDIFF_SEC out of the execute and back to the stored procedure. Here's an example of what I'd like to do, but these statements won't work because I can't pass out @nDIFF_SEC, and I haven't declared @nDIFF_SEC as a variable inside the execute session.
SET @query = SELECT @nDIFF_SEC = nDIFF_SEC FROM TableOwner.TableName WHERE nINDEX = @nINDEX
EXECUTE @query = 'SELECT @nDIFF_SEC = nDIFF_SEC FROM TableOwner.TableName WHERE nINDEX = ' + CONVERT(VARCHAR, @nINDEX);
Any ideas? (I don't want to have to call another stored procedure just for this one statement) I'd like to turn the precheck off, but I'd settle for getting the work around working also. Thanks in advance for your help.
Scott Pletcher
You could put the column addition in a separate sp. I think that will make SQL "recognize" the new column upon return from the other sp.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hilaire,
That looks like the way to go. I've never used dynamic sql before. I'm trying to understand it a bit before I use it.
When I type in the sample you gave me:
SET @query = N'SELECT @nDIFF_SEC = nDIFF_SEC FROM TableOwner.TableName WHERE nINDEX = ' + cast(@nINDEX as nvarchar(20))
exec sp_executesql @query, N'@ndiff_sec int out', @nDIFF_SEC out
I get the follwing error in the query analyizer:
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.
Can I use dynamic SQL to execute the follwing transaction also?
SELECT @fSTATS_RESULT = SUM(nDIFF_SEC) FROM TableOwner.TableName;
Would it just be:
SET @query = N'SELECT @fSTATS_RESULT = SUM(nDIFF_SEC) FROM TableOwner.TableName WHERE nINDEX = ' + CAST(@nINDEX AS NVARCHAR(20))
EXECUTE sp_executesql @query, N'@fSTATS_RESULT FLOAT OUT', @fSTATS_RESULT OUT
Thanks
That looks like the way to go. I've never used dynamic sql before. I'm trying to understand it a bit before I use it.
When I type in the sample you gave me:
SET @query = N'SELECT @nDIFF_SEC = nDIFF_SEC FROM TableOwner.TableName WHERE nINDEX = ' + cast(@nINDEX as nvarchar(20))
exec sp_executesql @query, N'@ndiff_sec int out', @nDIFF_SEC out
I get the follwing error in the query analyizer:
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.
Can I use dynamic SQL to execute the follwing transaction also?
SELECT @fSTATS_RESULT = SUM(nDIFF_SEC) FROM TableOwner.TableName;
Would it just be:
SET @query = N'SELECT @fSTATS_RESULT = SUM(nDIFF_SEC) FROM TableOwner.TableName WHERE nINDEX = ' + CAST(@nINDEX AS NVARCHAR(20))
EXECUTE sp_executesql @query, N'@fSTATS_RESULT FLOAT OUT', @fSTATS_RESULT OUT
Thanks
what datatype do you use for the @query variable ?
It should be declared as follows
declare @query nvarchar(2000)
I can't think of anything else wrong in the code
SET @query = N'SELECT @nDIFF_SEC = nDIFF_SEC FROM TableOwner.TableName WHERE nINDEX = ' + cast(@nINDEX as nvarchar(20))
exec sp_executesql @query, N'@ndiff_sec int out', @nDIFF_SEC out
As for your second question,
SELECT @fSTATS_RESULT = SUM(nDIFF_SEC) FROM TableOwner.TableName;
Most probably you'll have to use dynamic SQL , if the stored procedure does not compile when you refer to the nDIFF_SEC column.
Cheers
Hilaire
It should be declared as follows
declare @query nvarchar(2000)
I can't think of anything else wrong in the code
SET @query = N'SELECT @nDIFF_SEC = nDIFF_SEC FROM TableOwner.TableName WHERE nINDEX = ' + cast(@nINDEX as nvarchar(20))
exec sp_executesql @query, N'@ndiff_sec int out', @nDIFF_SEC out
As for your second question,
SELECT @fSTATS_RESULT = SUM(nDIFF_SEC) FROM TableOwner.TableName;
Most probably you'll have to use dynamic SQL , if the stored procedure does not compile when you refer to the nDIFF_SEC column.
Cheers
Hilaire
ASKER
Sorry about that guys....I was off on a tangent on another project. However, I would have expected to receive at least a reminder email before being classified as "abandoned". I simply forgot. I do aplogize to the answerers who should have received points promptly.
Jason
Jason