Solved

Passing a variable in a different field (interview question)

Posted on 2015-01-01
7
97 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
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 33

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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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 33

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 42

Expert Comment

by:EugeneZ
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Need help how to find where my error is in UFD 6 30
VB.net and sql server 4 36
Syntax for query to update table 2 15
SQL invalid column name 5 13
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

831 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