Solved

Passing a variable in a different field (interview question)

Posted on 2015-01-01
7
100 Views
Last Modified: 2015-01-04
Question 1: Is the solution provided below correct?
Question 2: What "Passing a variable in a different field" means? Could you give me examples of its use and when we may need to do something like this?

Interview question text:
"I have a procedure like the following, but it doesn't work:
.   solution that doesn't work not included because we already have the solution below
.   (please focus on Question 1 and 2 above)
I want to have the field as variable to pass it through a different field. Which part of the is wrong?"

Open in new window


Solution:
use northwind
go
Create Procedure GetFieldPath
@FieldName varchar(10),
@ReturnField varchar(10)
As
Declare @lcSQL varchar(300)
Set @lcSQL ='Select @MyReturnField = ' + @FieldName +' From Customers'
Exec sp_executesql @lcSQL, N'@MyReturnField varchar(10) output', 
 @MyReturnField = @ReturnField output
 Select @ReturnField

Open in new window

0
Comment
Question by:Mike Eghtebas
[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
7 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40527695
Is this a homework or a take home exam question?
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 40527703
Firstly, the SQL being executed is:

Select @MyReturnField = ' + @FieldName +' From Customers

which will give you @MyReturnField containing always the last value of the @FieldName field from the Customers table. This may or may not, for example, be the value from the most recently added record - there's really no reliable way to predict what you'll get back.

Secondly, there's no point in the last line of code if you're already returning that value as a parameter.

So, what are you trying to achieve? Please tell us!

BTW, "Passing a variable in a different field" seems just to be the way the person asking the question has of expressing himself - there is no special secret meaning to the phrase!

hth

Mike
0
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 40527737
Dale Fye (Access MVP),

I bet you did not read the question. At least not carefully. Because I clearly wrote this is an interview question.
0
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 
LVL 37

Expert Comment

by:ValentinoV
ID: 40527750
A1: no.

A2: if you want to return a value through a parameter then you need to specify this in the definition of the parameter:

@ReturnField varchar(10) OUTPUT

Open in new window

More info: Using a Stored Procedure with Output Parameters
0
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 40527780
DcpKing,

re:> So, what are you trying to achieve? Please tell us!

As shown on the attached image, there is an interview question I am trying to make sense of it. I want to know when and where such a thing is used. What is its use? And also because I want to test it, I have made my own example of it in Northwind database.

Question 1: Is the solution provided above (my Northwind sample titled "Solution:" in the original post) correct?
Question 2: What "Passing a variable in a different field" means? Could you give me examples of its use and when we may need to do something like this?
SQL_InterviewQuestion02
I can see that it builds a SQL string inside the proc and then executes it. But, to execute and use it, what do we supply and what do we get back and how we accomplish the task. And finally what is the task.
0
 
LVL 44

Accepted Solution

by:
Arthur_Wood earned 500 total points
ID: 40528101
Try this change:

Set @lcSQL ='Select ' + @MyReturnField  + ' = ' + @FieldName +' From Customers'
Exec sp_executesql @lcSQL, N'@MyReturnField varchar(10) output',
 @MyReturnField = @ReturnField output

You pass in the name of the field that  you want to be returned, but the original SQL never makes use of that user supplied field name, as it is hard-coded in the SQL.

AW
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 40530313
A1. looks right
A2.
please check these articles
"Introduction to Dynamic SQL (Part 1)"
http://www.sqlteam.com/article/introduction-to-dynamic-sql-part-1
and
"How to specify output parameters when you use the sp_executesql stored procedure in SQL Server"
http://support.microsoft.com/kb/262499
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

739 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