Solved

SQL column validation "pre-check"

Posted on 2004-09-02
6
512 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
6 Comments
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 11964382
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
ID: 11964565
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
ID: 11971330
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
ID: 11971779
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
ID: 12165756
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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

617 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