Solved

EXEC sp_executesql

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

912 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

21 Experts available now in Live!

Get 1:1 Help Now