Solved

Dynamic sql in MS SQL

Posted on 2014-03-12
2
248 Views
Last Modified: 2014-03-12
I have a stored procedure and I need to get a field value from a table based on a dynamic field name and a dynamic key value:

declare @FieldName varchar(255)
set @FieldName = 'myField' <-- in reality this is retrieved from a table

declare @KeyValue varchar(30)
set @KeyValue = 'key' <-- also retrieved from a table in the real world

declare @FieldValue varchar(30) <-- to return the results

select @FieldValue = @FieldName from myTable where keyField = @KeyValue

Then, of course, I have to use @FieldValue later in the stored procedure.

I know that this requires dynamic sql, but after looking at many examples and trying many things, I cannot figure it out.

Thanks in advance for whatever help you can provide
0
Comment
Question by:g_johnson
2 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
table and column names cannot be given by variables, so you need to really do dynamic sql, like this:
declare @sql nvarchar(max)
set @sql =  'select @FieldValue = [' + @FieldName + '] from myTable where keyField = @KeyValue '
exec sp_executesql @sql, N'@fieldvalue varchar(30) OUTPUT, @Keyvalue varchar(30)', @fieldvalue OUTPUT, @keyvalue 

Open in new window

0
 
LVL 4

Author Closing Comment

by:g_johnson
Comment Utility
Perfect.  Thank you!
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

772 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

9 Experts available now in Live!

Get 1:1 Help Now