Solved

SQL column validation "pre-check"

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SSIS On fail action 5 38
SQL Backup Question 2 29
MS SQL: Return all results in a single row separated by commas 1 26
SQLCMD Syntax 2 13
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

856 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