Solved

EXEC sp_executesql

Posted on 2013-01-16
4
582 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 143

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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

Suggested Solutions

This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

738 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