Solved

SQL column validation "pre-check"

Posted on 2004-09-02
6
497 Views
Last Modified: 2012-05-05
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.
0
Comment
Question by:sattesonjr
  • 2
  • 2
6 Comments
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
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.
0
 
LVL 26

Accepted Solution

by:
Hilaire earned 325 total points
Comment Utility
You can use sp_executesql to make a dynamic sql statement "see" a variable declared in the parent scope/environment

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
0
 

Author Comment

by:sattesonjr
Comment Utility
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
0
 
LVL 26

Expert Comment

by:Hilaire
Comment Utility
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
0
 

Author Comment

by:sattesonjr
Comment Utility
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
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now