Solved

EXEC sp_executesql

Posted on 2013-01-16
4
562 Views
Last Modified: 2013-01-17
I am having problems with sp_executesql

I have this sql I am running

            SET @Sql = 'UPDATE #Data SET [' + @FieldTag  + '] = ' + @ControlType + '
            FROM
                  (SELECT
                        tblField.DataVarchar, tblField.DataDateTime, tblField.DataDecimal, tblField.DataInt
                  FROM
                        tblPage WITH (NOLOCK)
                  INNER JOIN tblPageType WITH (NOLOCK) ON tblPage.PageTypeId = tblPageType.PageTypeID
                  INNER JOIN tblField WITH (NOLOCK) ON tblPage.PageId = tblField.PageID
                  INNER JOIN tblFieldType WITH (NOLOCK) ON dbo.tblField.FieldTypeID = dbo.tblFieldType.FieldTypeID
                  WHERE
                        (tblPage.CaseID = ''' + @CaseID + ''') AND (tblPageType.PageTypeGUID = ''' + @PageTypeGUID + ''') AND (tblFieldType.FieldTag = + ''' + @FieldTag  + ''')) AS SQ1
            WHERE
                  CaseID = ''' + @CaseID + ''''

            EXEC(@Sql)

Which works fine but normally to make this faster I would change to

            SET @Sql = 'UPDATE #Data SET @xFieldTag = @xControlType
            FROM
                  (SELECT
                        dbo.tblField.DataVarchar, dbo.tblField.DataDateTime, dbo.tblField.DataDecimal, dbo.tblField.DataInt
                  FROM
                        dbo.tblPage WITH (NOLOCK)
                  INNER JOIN dbo.tblPageType WITH (NOLOCK) ON dbo.tblPage.PageTypeId = dbo.tblPageType.PageTypeID
                  INNER JOIN dbo.tblField WITH (NOLOCK) ON dbo.tblPage.PageId = dbo.tblField.PageID
                  INNER JOIN dbo.tblFieldType WITH (NOLOCK) ON dbo.tblField.FieldTypeID = dbo.tblFieldType.FieldTypeID
                  WHERE
                        (dbo.tblPage.CaseID = @xCaseID ) AND (dbo.tblPageType.PageTypeGUID = @xPageTypeGUID) AND (dbo.tblFieldType.FieldTag = @xFieldTag)) AS SQ1
            WHERE
                  CaseID = @xCaseID'

            -- create the parameter list
            SET @paramlist = '
                  @xCaseID       varchar(50),
                  @xPageTypeGUID varchar(50),
                  @xFieldTag     varchar(255),
                  @xControlType  varchar(50)'

            -- execute the sql string
            EXEC sp_executesql @sql, @paramlist, @CaseID, @PageTypeGUID, @FieldTag, @ControlType

Where #Data is a temp table

But I cannot get it to work I always get

Invalid column name '@xFieldTag'.

Have tried for hours adding '''''s everywhere but to no avail
0
Comment
Question by:MMTadmin
  • 2
4 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38785444
Make the following change:
-- EXEC sp_executesql @sql, @paramlist, @CaseID, @PageTypeGUID, @FieldTag, @ControlType
SELECT @sql, @paramlist, @CaseID, @PageTypeGUID, @FieldTag, @ControlType

And if the answer is not obvious post the results here.
0
 
LVL 2

Author Comment

by:MMTadmin
ID: 38785595
That just returns a lot of result sets and made sql server go crazy

The sql has to be RAN not displayed

100 of these then failure

UPDATE #Data SET @xFieldTag = @xControlType      FROM       (SELECT        dbo.tblField.DataVarchar, dbo.tblField.DataDateTime, dbo.tblField.DataDecimal, dbo.tblField.DataInt       FROM        dbo.tblPage WITH (NOLOCK)       INNER JOIN dbo.tblPageType WITH (NOLOCK) ON dbo.tblPage.PageTypeId = dbo.tblPageType.PageTypeID       INNER JOIN dbo.tblField WITH (NOLOCK) ON dbo.tblPage.PageId = dbo.tblField.PageID       INNER JOIN dbo.tblFieldType WITH (NOLOCK) ON dbo.tblField.FieldTypeID = dbo.tblFieldType.FieldTypeID       WHERE        (dbo.tblPage.CaseID = @xCaseID ) AND (dbo.tblPageType.PageTypeGUID = @xPageTypeGUID) AND (dbo.tblFieldType.FieldTag = @xFieldTag)) AS SQ1      WHERE       CaseID = @xCaseID            @xCaseID       varchar(50),      @xPageTypeGUID varchar(50),      @xFieldTag     varchar(255),      @xControlType  varchar(50),      @xFieldName    varchar(255)      21429      89377717-2B2B-47FF-B4CF-A3A01F6583E5      PT DOB      DataVarchar      NULL
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 38785986
field and table names cannot be variables.
you have to stick with the working way for that part.
for values, you can use the parameter way.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38788599
The sql has to be RAN not displayed
Unfortunately you have to fix it before you can "RAN" it.  This is called debugging and I am sorry that the answer was not obvious to you, but it looks like angelIII has pointed out the error of your ways.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

706 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

16 Experts available now in Live!

Get 1:1 Help Now