?
Solved

Passing a variable in a different field (interview question)

Posted on 2015-01-01
7
Medium Priority
?
103 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
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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 2000 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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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.
Suggested Courses

752 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