Solved

Passing a variable in a different field (interview question)

Posted on 2015-01-01
7
101 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 48

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

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

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

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.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

707 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