Set a parameter in select statement

Question

I am trying to set a parameter in the select statement and then pass it to a user defined function in the same statement. Is this possible? If yes, where is my mistake? If no, then I guess I will have to write a cursor and some temporary tables which I want to avoid.

declare @param varchar(1000)
select Pincode, Name,( @param = AlternateName) as AlternateName 
,(select Top(1) part  from SDF_SplitString (@param,',')) as NewName  from PinCodeTable

Solution

You can either get all of the fields out as variables, or get the usual set of rows, but you can't mix and match in a single SELECT. Variables are limited to queries that return no more than one row. (Why do I feel like I'm about to learn something frightening?)

If you are writing a stored procedure and doing something like header/trailer rowsets, you can always return a rowset built from variables:

SELECT @Foo = Foo, @Bar = Bar, ... from Headers where Id = 42
SELECT @Foo as Foo -- Return the first rowset.
SELECT Size, Weight, Color from Trailers where Bar = @Bar -- Return second rowset.

Comments

[shashi]

I figured a way to achieve what I want. Basically I wanted to use the column AlternateName in the user defined function and I can do that by just using the column name instead of using a param. The problem I was facing with this earlier was due to a typo. What should I do with this question? Close it ? Please advise

[HABO]

You can answer your own question. BTDT.

[shashi]

This actually answers the question I asked, so marking it as answer.