Missing Columns in Dynamic SQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Missing Columns in Dynamic SQL

Author

Topic

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-10-06 : 07:00:59

Hello people, long time no see.

I have a SQL statement (below) that I need to execute dynamically, as one of the table names is unknown at design time.

When I execute the statement directly, it works fine. But when I execute it dynamically using EXEC, the select query in the statement only returns 5 columns, so the insert fails. This looks like a bug in SQL server to me. Has anyone ever seen this before? Can anyone suggest a work around?

INSERT INTO #tmp
SELECT     t.RowNum, 
           c.KEY, 
        CASE WHEN lTrim(p.Title) = '' THEN NULL ELSE lTrim(rTrim(p.Title)) END AS Title, 
        CASE WHEN lTrim(p.Initials) = '' THEN NULL ELSE lTrim(rTrim(p.Initials)) END AS Initials, 
        CASE WHEN lTrim(p.Forenames) = '' THEN NULL ELSE lTrim(rTrim(p.Forenames)) END AS Forenames, 
        CASE WHEN lTrim(p.Surname) = '' THEN NULL ELSE lTrim(rTrim(p.Surname)) END AS Surname, 
        CASE WHEN lTrim(p.NetAdd) = '' THEN NULL ELSE lTrim(rTrim(p.NetAdd)) END AS NetAdd, 
        CASE WHEN lTrim(a.Postcode) = '' THEN NULL ELSE lTrim(rTrim(a.Postcode)) END AS Postcode
FROM Contact c
INNER JOIN people p
    ON c.PeopleKey = p.PeopleKey
INNER JOIN  Address a
    ON c.ADDRESSKEY = a.ADDRESSKEY
INNER JOIN ImportTable t
    ON p.Surname = t.Surname COLLATE SQL_Latin1_General_CP1_CI_AS
    AND a.PostCode = t.PostCode COLLATE SQL_Latin1_General_CP1_CI_AS
    AND t.KEY IS NULL
GROUP BY t.RowNum, c.KEY, p.Title, p.Initials, p.Forenames, p.Surname, p.NetAdd, a.Postcode

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-10-06 : 07:14:34

1) Can't possibly imagine why you want to do something like this dynamically. Dimly remember making this point before though.

2) Post your code where you try and EXEC it dynamically (include everything like where you add the table chosen at run time (shudder))

I'll give you good odds that there is some error in your code rather than a bug with EXEC().

Also -- why use EXEC() rather than sp_executeSql?

I'm almost certain I've given you this link before.
http://www.sommarskog.se/dynamic_sql.html

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-10-06 : 07:27:22

quote:
Originally posted by Transact Charlie

1) Can't possibly imagine why you want to do something like this dynamically. Dimly remember making this point before though.

In a nutshell, I'm writing a stored proc that will be used to identify duplicate records before they're imported. The records to be imported will be in a table with a specific format, but the table name could be one of many different names, depending on the source of the data to be imported. So I'm passing the table name to the stored proc as a string.

quote:
2) Post your code where you try and EXEC it dynamically (include everything like where you add the table chosen at run time (shudder))

I'll give you good odds that there is some error in your code rather than a bug with EXEC().

You were right. I found the problem. I didn't delimit the single quotes inside the query body. This is something I often forget to do, but usually this causes an error which is when I remember to do it. I don't use dynamic SQL much.

quote:
Also -- why use EXEC() rather than sp_executeSql?

Old habits die hard. :)

quote:
I'm almost certain I've given you this link before.
http://www.sommarskog.se/dynamic_sql.html

Yes you have, and my God you must have a good memory. Will read the article again.

Thanx for your response.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources