Variables in stored procedures

Question

Variables in stored procedures

Richard-127094

SSC Enthusiast

Points: 113
More actions
February 3, 2004 at 4:12 pm

#84144

Hello,
Only just registered, and only just recently into Stored Procedures - so sorry if the following seems a bit simple!
I have a asp (vbscript) page that calls an sql server(2000) stored procedure. It passes in a variable (string) that I would like to use in a select statement (i.e. SELECT (variable name) FROM blah blah blah). I've tried putting an @ symbol before the variable name, but instead of selecting the equivalent field name that is contained within the variable, it places the field name as the retrieved contents.
So, basically, I want to pass in at runtime what field to select data from.
e.g, results currently look like:
id product name
1 [product].[productname]
2 [product].[productname]
3 [product].[productname]

So "[product].[productname]" is being placed where the the actual name of the product should be.

If this makes sense, I would really appreciate help on this as I've been changing it for hours and it's driving me round the bend!
Thanks,
Richard.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

tbonty Newbie Points: 3 More actions · Answer 1

Let me see if I understand your problem. You are trying to do a dynamic SELECT statement and you are putting a column name inside of the variable to complete your SELECT statement.

If this is what you are trying to do, then what you need to do is run the select statement in another scope (i.e. in an EXEC statement).

For example:

CREATE PROCEDURE GetClientInfo

@columnName varchar(20)

AS

SET NOCOUNT ON

EXEC('SELECT ' + @columnName + ' From Customers')

-------------------------------------------------------

Then execute the stored procedure and pass in the column name

EXEC GetClientInfo 'CompanyName'

I hope this helps.

Richard-127094 SSC Enthusiast Points: 113 More actions · Answer 2

Yeeeeeeeessssssssss!!!!!!!

Brilliant! That worked - thanks very much for your help, much appreciated.

Cheers,

Rich.

Marcus Wilkinson SSC Journeyman Points: 88 More actions · Answer 3

Hi,

I'm trying to do the same thing but within a cursor and get a syntax error.

DECLARE C1 CURSOR FOR

EXEC('SELECT partitionID, '+ @FieldName + ' from

partitions for update')

OPEN C1

I must be missing something really obvious..

Thanks for your help