• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 110
  • Last Modified:

Passing a variable in a different field (interview question)

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
Mike Eghtebas
Asked:
Mike Eghtebas
1 Solution
 
Dale FyeCommented:
Is this a homework or a take home exam question?
0
 
DcpKingCommented:
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
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
ValentinoVBI ConsultantCommented:
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
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
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
 
Arthur_WoodCommented:
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
 
Eugene ZCommented:
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now