Solved

Dynamic Query Creation -- Bugs and Questions (long)

Posted on 2004-09-29
21
286 Views
Last Modified: 2008-01-09
Hiya!  This is kinda complicated and I'll try to be as clear as possible.  I have to validate some tables for data discrepancies on a weekly basis.  I had been creating all the queries manually and then updating them when a new field was added or when there was a new table ... this was a pain in the rump and took a lot of time.  So, I have been working on a procedure to dynamically create the queries from system tables and passing in values for the table names... it seems to have worked really well... however, it won't execute the query once I have created it.  

Also, I have created the queries in pieces as some of my queries get very large and would exceed the maxium length for a variable... so, I am concatenating the pieces at the end of the code to run one big sql query.  The query seems to have compiled exactly the way I want it to, however when I run the procedure automatically I get errors.

When it gets to the final exec line of the procedure (the line that actually executes the sql statement once it has been compiled) I get the following errors:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'P_ALT_ITEM'. (Or any other table name that gets passed in)
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'U'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'P'.

My questions:
1. The thing I can't understand is that if I copy and paste the compiled query into a blank query analyzer window, the query runs perfectly.  So, I'm not sure why it is giving me the syntax errors.
2. One of my enviornments has the potential of having more fields in the tables than in the other enviornment.  So, when it compiles the query, it adds a few too many extra fields to the compiled query and then I get "invalid column name" errors.  How can I alter my code to prevent unmatch column names from getting compiled into the query?

Here is the procedure:
ALTER PROCEDURE spQuery (@QueryName nvarchar(255), @TempQueryNum varchar(10)) AS

declare @SQL nvarchar(500)
declare @SQL2 nvarchar(500)
declare @QueryName2 nvarchar(255)
--First 4 variables are for production vs. master
declare @ProdQuery varchar(8000)
declare @ProdQuery2 varchar(8000)
declare @ProdQuery3 varchar(8000)
declare @ProdQuery4 varchar(8000)
--Next 4 variables are for master vs. production
declare @ProdQuery5 varchar(8000)
declare @ProdQuery6 varchar(8000)
declare @ProdQuery7 varchar(8000)
declare @ProdQuery8 varchar(8000)

declare @FieldName nvarchar(255)
declare @Length int

--Start Production vs. Master Code

Select @QueryName2 = 'P_' + @QueryName
--Get field names for passed in table
SELECT @SQL = 'SELECT syscol.name INTO TempFields from RTM..syscolumns syscol inner join
RTM..sysobjects sysob on syscol.id = sysob.id where sysob.name = ''' + @QueryName2 + ''' order by syscol.name'
exec (@SQL)

DECLARE table_cursor SCROLL CURSOR FOR
Select * from TempFields

Select @ProdQuery = 'SELECT [Table] = ''' + @QueryName + ''', Type = ''PRODUCTION'''
Select @ProdQuery3 = ''
OPEN table_cursor

FETCH FIRST FROM table_cursor INTO
@FieldName

Select @ProdQuery4 = 'WHERE (((U.' + @FieldName + ') Is Null)) UNION ALL'

WHILE @@Fetch_Status = 0
      BEGIN
            Select @ProdQuery = @ProdQuery + ', P.' + @FieldName
            Select @ProdQuery3 = @ProdQuery3 + '(ISNULL(P.' + @FieldName + ',0) = ISNULL(U.' + @FieldName + ',0)) AND '
            FETCH NEXT FROM table_cursor INTO
            @FieldName
      END
Select @ProdQuery = @ProdQuery + ' INTO TempQuery' + @TempQueryNum
Select @ProdQuery2 = 'FROM P_' + @QueryName + ' P LEFT JOIN U_' + @QueryName + ' U ON'
Select @Length = len(@ProdQuery3)
Select @ProdQuery3 = substring(@ProdQuery3,1, @Length - 4)
--End Production vs. Master code

--Start Master vs. Production code
Select @QueryName2 = 'U_' + @QueryName
SELECT @SQL2 = 'SELECT syscol.name INTO TempFields2 from RTM..syscolumns syscol inner join
RTM..sysobjects sysob on syscol.id = sysob.id where sysob.name = ''' + @QueryName2 + ''' order by syscol.name'
exec (@SQL2)

DECLARE table_cursor2 SCROLL CURSOR FOR
Select * from TempFields2
Open Table_cursor2

Select @ProdQuery5 = 'SELECT [Table] = ''' + @QueryName + ''', Type = ''MASTER'''
Select @ProdQuery7 = ''

FETCH FIRST FROM table_cursor2 INTO
@FieldName

Select @ProdQuery8 = 'WHERE (((P.' + @FieldName + ') Is Null))'

WHILE @@Fetch_Status = 0
      BEGIN
            Select @ProdQuery5 = @ProdQuery5 + ', U.' + @FieldName
            Select @ProdQuery7 = @ProdQuery7 + '(ISNULL(P.' + @FieldName + ',0) = ISNULL(U.' + @FieldName + ',0)) AND '
            FETCH NEXT FROM table_cursor2 INTO
            @FieldName
      END

Select @ProdQuery6 = 'FROM U_' + @QueryName + ' U LEFT JOIN P_' + @QueryName + ' P ON'
Select @Length = len(@ProdQuery7)
Select @ProdQuery7 = substring(@ProdQuery7,1, @Length - 4)
--End Master vs. Production code

print @ProdQuery
print @ProdQuery2
print @ProdQuery3
print @ProdQuery4
print @ProdQuery5
print @ProdQuery6
print @ProdQuery7
print @ProdQuery8

--Put the Query together
exec (@ProdQuery + '' + @ProdQuery2 + '' + @ProdQuery3 + '' + @ProdQuery4 + '' + @ProdQuery5 + '' + @ProdQuery6 + '' + @ProdQuery7 + '' + @ProdQuery8)

DROP TABLE TempFields
DROP TABLE TempFields2
DEALLOCATE table_cursor
DEALLOCATE table_cursor2

This is an example of a compiled query:
SELECT [Table] = 'ALT_ITEM', Type = 'PRODUCTION', P.ALT_ITEM_ID, P.APPLICATION_ID, P.DL_SERVICE_CODE, P.DL_UPDATE_STAMP, P.ITEM_ID, P.OPERATOR_ID, P.SYS_CREATION_DATE, P.SYS_UPDATE_DATE INTO TempQuery4
FROM P_ALT_ITEM P LEFT JOIN U_ALT_ITEM U ON
(ISNULL(P.ALT_ITEM_ID,0) = ISNULL(U.ALT_ITEM_ID,0)) AND (ISNULL(P.APPLICATION_ID,0) = ISNULL(U.APPLICATION_ID,0)) AND (ISNULL(P.DL_SERVICE_CODE,0) = ISNULL(U.DL_SERVICE_CODE,0)) AND (ISNULL(P.DL_UPDATE_STAMP,0) = ISNULL(U.DL_UPDATE_STAMP,0)) AND (ISNULL(P.ITEM_ID,0) = ISNULL(U.ITEM_ID,0)) AND (ISNULL(P.OPERATOR_ID,0) = ISNULL(U.OPERATOR_ID,0)) AND (ISNULL(P.SYS_CREATION_DATE,0) = ISNULL(U.SYS_CREATION_DATE,0)) AND (ISNULL(P.SYS_UPDATE_DATE,0) = ISNULL(U.SYS_UPDATE_DATE,0))
WHERE (((U.ALT_ITEM_ID) Is Null)) UNION ALL
SELECT [Table] = 'ALT_ITEM', Type = 'MASTER', U.ALT_ITEM_ID, U.APPLICATION_ID, U.DL_SERVICE_CODE, U.DL_UPDATE_STAMP, U.ITEM_ID, U.OPERATOR_ID, U.SYS_CREATION_DATE, U.SYS_UPDATE_DATE
FROM U_ALT_ITEM U LEFT JOIN P_ALT_ITEM P ON
(ISNULL(P.ALT_ITEM_ID,0) = ISNULL(U.ALT_ITEM_ID,0)) AND (ISNULL(P.APPLICATION_ID,0) = ISNULL(U.APPLICATION_ID,0)) AND (ISNULL(P.DL_SERVICE_CODE,0) = ISNULL(U.DL_SERVICE_CODE,0)) AND (ISNULL(P.DL_UPDATE_STAMP,0) = ISNULL(U.DL_UPDATE_STAMP,0)) AND (ISNULL(P.ITEM_ID,0) = ISNULL(U.ITEM_ID,0)) AND (ISNULL(P.OPERATOR_ID,0) = ISNULL(U.OPERATOR_ID,0)) AND (ISNULL(P.SYS_CREATION_DATE,0) = ISNULL(U.SYS_CREATION_DATE,0)) AND (ISNULL(P.SYS_UPDATE_DATE,0) = ISNULL(U.SYS_UPDATE_DATE,0))
WHERE (((P.ALT_ITEM_ID) Is Null))
0
Comment
Question by:Roxanne25
  • 10
  • 7
  • 4
21 Comments
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12184825
Trimming your sample query, you're tripping over a SELECT INTO followed by UNION:

SELECT [Table] = 'ALT_ITEM'
INTO TempQuery4
FROM P_ALT_ITEM P LEFT JOIN U_ALT_ITEM U ON ISNULL(P.ALT_ITEM_ID,0) = ISNULL(U.ALT_ITEM_ID,0)
UNION ALL
SELECT [Table] = 'ALT_ITEM'
FROM U_ALT_ITEM U LEFT JOIN P_ALT_ITEM P ON ISNULL(P.ALT_ITEM_ID,0) = ISNULL(U.ALT_ITEM_ID,0)

You can correct this by making your code produce the following basic structure. It should be an easy fix, just add a line segment at the beginning, move the INTO clause, and put a new tail on it:

SELECT * INTO TempQuery4 FROM (
  SELECT [Table] = 'ALT_ITEM'
  FROM P_ALT_ITEM P LEFT JOIN U_ALT_ITEM U ON ISNULL(P.ALT_ITEM_ID,0) = ISNULL(U.ALT_ITEM_ID,0)
  UNION ALL
  SELECT [Table] = 'ALT_ITEM'
  FROM U_ALT_ITEM U LEFT JOIN P_ALT_ITEM P ON ISNULL(P.ALT_ITEM_ID,0) = ISNULL(U.ALT_ITEM_ID,0)
) q
0
 

Author Comment

by:Roxanne25
ID: 12184923
I'm not quite sure how that would solve anything... the queries work just fine as they are.  So, I'm not sure what you mean by I'm "tripping over a select into".
0
 
LVL 15

Accepted Solution

by:
jdlambert1 earned 450 total points
ID: 12184993
Okay, sorry. For problem 1, if you pasted together the output of your 8 print statements, you may not be creating the same thing QA is. Try this and make sure you have spaces between the query segments:

print @ProdQuery8
DECLARE @sql nvarchar(4000)

--Put the Query together
SET @sql = @ProdQuery + '' + @ProdQuery2 + '' + @ProdQuery3 + '' + @ProdQuery4 + '' + @ProdQuery5 + '' + @ProdQuery6 + '' + @ProdQuery7 + '' + @ProdQuery8

PRINT @sql
0
 
LVL 15

Assisted Solution

by:jdlambert1
jdlambert1 earned 450 total points
ID: 12185021
Problem 2: Modify your SELECT INTO TempFields command to inner join to a subquery that produces a list of the second set of column names, with the join based on column names.
0
 

Author Comment

by:Roxanne25
ID: 12185102
Ok, for problem 1:
That is what I have done.  The example query that I put below my procedure is copied exactly from the print @sql statement.  If I copy and paste the exact output from the print @sql into a blank query window and run it, it works.  But within the procedure, I get those syntax errors...

Problem 2:
I was looking at your solution and how I could implement it and then it hit me that I can just use the same column listing from the one enviornment (production), I don't need to requery the second enviornment (master) to get the field list... because I should already have the ones I want from the first enviornment (production).  If that makes any sense...

I never realized how hard it is to write down these sort of things! :)

Anyways, thanks for your help with this... so, one problem down... one to go! :)
0
 
LVL 34

Expert Comment

by:arbert
ID: 12185560
Also, why don't you simply query the information_schema.columns view instead?
0
 
LVL 15

Assisted Solution

by:jdlambert1
jdlambert1 earned 450 total points
ID: 12185562
I can't think of any reason why the output should work in QA but not from an EXEC command, but I'm surprised your output has sufficient spaces. You have values like 'FROM U_' (in Select @ProdQuery6 = 'FROM U_') that doesn't have spacing and you don't pad it when you combine the strings (exec (@ProdQuery + '' + @ProdQuery2 ). In fact, the series of [+ '' +]  between strings don't do anything since there's no space between them.

Anyway, I suggest combining your string segments into a single string again, double-check the spaces one more time with a print, then try this instead of the EXEC():

EXEC sp_executesql @sql

Note there are no parenthesis for sp_executesql.
0
 

Author Comment

by:Roxanne25
ID: 12185654
Its because I'm padding at the end of the strings... not the beginning.
0
 

Author Comment

by:Roxanne25
ID: 12185657
Arbert, I was not aware of the information_schema.columns view... didn't think there were any system tables that were views... what would be the benefit of using that table instead of the way I'm doing it?
0
 

Author Comment

by:Roxanne25
ID: 12185670
Actually, there might be a spacing issue here:
WHILE @@Fetch_Status = 0
     BEGIN
          Select @ProdQuery = @ProdQuery + ', P.' + @FieldName
          Select @ProdQuery3 = @ProdQuery3 + '(ISNULL(P.' + @FieldName + ',0) = ISNULL(U.' + @FieldName + ',0)) AND '
          FETCH NEXT FROM table_cursor INTO
          @FieldName
     END
Select @ProdQuery = @ProdQuery + ' INTO TempQuery' + @TempQueryNum
Select @ProdQuery2 = 'FROM P_' + @QueryName + ' P LEFT JOIN U_' + @QueryName + ' U ON'

it doesn't look like there is anyspace in the ProdQuery2 section... I will double check that... but, if it were the spacing issue, I would think that the query wouldn't work if I cut and pasted it into a blank query window...
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 15

Expert Comment

by:jdlambert1
ID: 12185676
System tables are SQL Server specific, while INFORMATION_SCHEMA views are ANSI-standard. Therefore, the views are less likely to change and require updates to your scripts.
0
 

Author Comment

by:Roxanne25
ID: 12185691
Ok, but what do you mean about the views "less likely to change"... how exactly would the system tables change?  Aside from the information in the tables being updated when a new table is added or a schema changes in a table?
0
 
LVL 15

Assisted Solution

by:jdlambert1
jdlambert1 earned 450 total points
ID: 12185838
If you code against sysobjects to get table names and Microsoft creates a separate systables table for table objects, your code could break. I feel sure Microsoft will avoid changing system tables whenever possible, but they make it clear in their documentation that they are subject to structural changes. However, even if they implement change to sysobjects, at the same time they can change the definition behind the Information_Schema.Tables view so that it still produces table names, and there are no compatibility problems.
0
 

Author Comment

by:Roxanne25
ID: 12186055
Oh, I'm not getting my table names from a system table.  Just the fields.
0
 
LVL 34

Expert Comment

by:arbert
ID: 12186124
"I feel sure Microsoft will avoid changing system tables whenever possible"

Ya, anyone looking at yukon knows that's not the case....

"Oh, I'm not getting my table names from a system table.  Just the fields."

Hello, you're still getting data from system tables--whether it's a table name or a column name....
0
 
LVL 34

Assisted Solution

by:arbert
arbert earned 50 total points
ID: 12186131
(not to mention you wouldn't have to perform the join query that you have above by using the view)
0
 

Author Comment

by:Roxanne25
ID: 12186195
"Hello, you're still getting data from system tables--whether it's a table name or a column name...."
I could deal without the sarcasm...

It sounded like you guys were only talking about table names... I still don't see, based on the information provided the benefit of using the other table over the system table.  The structure of the table can't exactly change unless I install a patch or something... it can't just arbitrarily change, unless there is something I'm not getting.

However, bringing up the fact that I wouldn't need a join in my query to accomplish the task DOES display a benefit to me to changing it to the other table.  

I shall look into changing it to the view.
Hopefully I can figure out the syntax errors tomorrow and it be as easy as having my spacing wrong.  I'll let you guys know.

Thanks for the help.
0
 
LVL 34

Expert Comment

by:arbert
ID: 12187089
Sarcasm was given because your statement didn't make sense--did you read it when you typed it???

"The structure of the table can't exactly change unless I install a patch or something"

Search the EE archives--I've dealt with several question where people have been bitten by even service packs because the queries the system tables.  You're not only pulling a column name, but you're using join criteria and where clauses--any of that could change.  How many SQL2000 patches and service packs were release this year?  Each one increases your chaces for problems...
0
 

Author Comment

by:Roxanne25
ID: 12188869
I know how many packs and patches have been released... I have not installed any of them, so therefore none of my tables could change.  But thank you for the clarification.
0
 

Author Comment

by:Roxanne25
ID: 12189496
Yay!  I just got in and made the modifications that jdlambert suggested for my 2 problems and it seems to be working!  The tables are being created and its working.  

Thanks so much.
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12190082
Glad we could be of some assistance.
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Suggested Solutions

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…
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.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

707 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

18 Experts available now in Live!

Get 1:1 Help Now