Solved

SQL column validation "pre-check"

Posted on 2004-09-02
6
504 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: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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

713 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