Solved

EXEC sp_executesql

Posted on 2013-01-16
4
595 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

Get HTML5 Certified

Want to be a web developer? You'll need to know HTML. Prepare for HTML5 certification by enrolling in July's Course of the Month! It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

617 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