Column name passed in as a parameter

Question

Column name passed in as a parameter

Debra Hodges-389283

SSC Enthusiast

Points: 136
More actions
December 12, 2006 at 10:06 am

#175573

I cannot figure out how to use a parameter within a qualified identifier to reference a column name. In the shipping rates table there are 150 columns named 1,2,3,4,5,6, etc. I'm trying to create a stored procedure that takes a column name as a parameter passed in as an integer. When I try to concatenate the parameter into the Brackets it doesn’t recognize it. If I don’t use the brackets it doesn’t return the real value. It works in the vb.net code like this:
sqlcmd.CommandText = "SELECT [" & CInt(Weight) & "] FROM ShippingRates WITH (NOLOCK) WHERE ShipMethod='" & theShipMethod & "' AND CountryCode='" & dc.CountryCode & "' AND Zone=" & DestinationZone
If anyone knows how to do this in Transact-SQL I'd appreciate it.
Thank you,
Debra

Viewing 15 posts - 1 through 15 (of 19 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Debra

The best solution to your problem would be to redesign your database so that it's properly normalised. If you can't do that, the only option you have is to use dynamic SQL. Search on this site for dynamic SQL to learn about the implications of doing this.

John

Jeff Block Old Hand Points: 324 More actions · Answer 2

Jeff Block

Old Hand

Points: 324

December 12, 2006 at 10:18 am

#677542

Can you please post the sql for the stored proc.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

http://www.sommarskog.se/dynamic_sql.html

http://www.sommarskog.se/dyn-search.html

http://www.sommarskog.se/arrays-in-sql.html

Debra Hodges-389283 SSC Enthusiast Points: 136 More actions · Answer 4

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[Shipping_GetShippingRate]

@Weight char(50),

@theShipMethod char(30),

@CountryCode char(3),

@DestinationZone int

AS

SET NOCOUNT ON

SELECT [ + @Weight + ] FROM ShippingRates WITH (NOLOCK) WHERE ShipMethod=@theShipMethod AND CountryCode=@CountryCode AND Zone=@DestinationZone

I'm looking for the value in a column named 5 . @Weight is 5 but it doesn't recognize it as the column name.

Thanks.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

I'd strongly suggest you rethink the design. This query is supposed to be very simple!!

Debra Hodges-389283 SSC Enthusiast Points: 136 More actions · Answer 6

I'd like to change the column names but didn't design this database, so can't do that, unfortunately. Thanks.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

How many distinct groups of columns can be selected from this table?

Debra Hodges-389283 SSC Enthusiast Points: 136 More actions · Answer 8

Not sure what you mean. Each column can be selected. This query works just fine in retrieving the value.

select top 1 [5] from shippingrates with (NOLOCK)

returns 8.35

this doesn't work: @Weight = 5 for column named 5

select top 1 [@Weight] from shippingrates with (NOLOCK)

returns 5

Debra Hodges-389283 SSC Enthusiast Points: 136 More actions · Answer 9

sorry, let me correct that.

select

top 1 @Weight from shippingrates WITH (NOLOCK)

returns 5

select top 1 [@Weight] from shippingrates with (NOLOCK)

returns

Msg 207, Level 16, State 1, Line 4

Invalid column name '@Weight'.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

And it shouldn't work that way either. I'd strongly suggest you read the 3 articles when you get a few hours of free time. However to solve that particular solution there seem to be only one way :

DECLARE @SQL VARCHAR(8000)

SET @SQL = 'SELECT ' + @ColsList + ' FROM dbo.YourTable WHERE SomeCol = ' + STR(@Parm)

EXEC (@SQL)

Sergiy SSC Guru Points: 110207 More actions · Answer 11

Problem is:

@Weight char(50),

[ + @Weight + ]

becomes

[5 ]

Is there such column in your table?

Always use PRINT for dynamic SQL before going to EXEC.

_____________
Code for TallyGenerator

Debra Hodges-389283 SSC Enthusiast Points: 136 More actions · Answer 12

declare

@weight varchar(20)

set

@weight = 5

declare

@sql varchar(8000)

set

@sql = 'select [' + @weight + '] from shippingrates with (NOLOCK)'

select

@sql

returns: select [5] from shippingrates with (NOLOCK)

exec @sql

returns:

Msg 203, Level 16, State 2, Line 8

The name 'select [5] from shippingrates with (NOLOCK)' is not a valid identifier.

Oh well, thanks anyway, I'll read up on dynamic sql.

Sergiy SSC Guru Points: 110207 More actions · Answer 13

Debra, take a break and spend several minutes learning SQL syntax.

declare @weight varchar(20)

set @weight = 5

declare @sql varchar(8000)

set @sql = 'select [' + @weight + '] from shippingrates with (NOLOCK)'

PRINT @sql

EXEC (@sql)

_____________
Code for TallyGenerator

Ronald San Juan SSCrazy Points: 2937 More actions · Answer 14

If you read Ninja's links,

http://www.sommarskog.se/dynamic_sql.html

http://www.sommarskog.se/dyn-search.html

http://www.sommarskog.se/arrays-in-sql.html

you'd find out that your syntax should be..

EXEC('

SELECT [' + @Weight + '] FROM ShippingRates WITH (NOLOCK) WHERE ShipMethod=''' + @theShipMethod + ''' AND CountryCode=''' + @CountryCode + ''' AND Zone=' + @DestinationZone

)

Ronald San Juan | SQL DBA
ID 710124 ~ Code "Northwind"