Solved

EXEC sp_executesql

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

828 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