Field Name variable

11 Nov 2005 12:11 AM

Scott

I'm trying to use sField as a variable for a field in the [Order Details]
table in Northwind, but having trouble with syntax. Can anyone shed light on
a solution?

CODE:

declare @orderID int
declare @sField Char(40)

set @orderID = '10248'
set @sField='UnitPrice'

"SELECT SUM(" + @sField + ") FROM [Order Details] WHERE orderID=" +
@orderID

11 Nov 2005 1:12 AM

Alejandro Mesa

Scott,

You will need to use dynamic sql in order to do this. Before going forward,
take a minute and read this article to learn the pros and cons od dynamic sql.

The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html

declare @sql nvarchar(4000)
declare @orderID int
declare @sField sysname
declare @result int

set @orderID = 10248
set @sField = N'UnitPrice'

set @sql = N'SELECT @result = SUM([' + @sField + N']) FROM [Order Details]
WHERE orderID = @orderID'

exec sp_executesql @sql, N'@orderID int, @result int output', @orderID,
@result output

print @result
go

AMB

Show quote

"Scott" wrote:

> I'm trying to use sField as a variable for a field in the [Order Details]
> table in Northwind, but having trouble with syntax. Can anyone shed light on
> a solution?
>
> CODE:
>
> declare @orderID int
> declare @sField Char(40)
>
> set @orderID = '10248'
> set @sField='UnitPrice'
>
> "SELECT SUM(" + @sField + ") FROM [Order Details] WHERE orderID=" +
> @orderID
>
>
>

11 Nov 2005 4:45 PM

scott

Can you point me to a link explaining the N' syntax? Or maybe offer an
explanation? I see it sometimes and not familiar with it.

thanks

Show quote

"Alejandro Mesa" <AlejandroM***@discussions.microsoft.com> wrote in message
news:51D77971-D702-4851-9E9E-54FFACB1736F@microsoft.com...
> Scott,
>
> You will need to use dynamic sql in order to do this. Before going
> forward,
> take a minute and read this article to learn the pros and cons od dynamic
> sql.
>
> The Curse and Blessings of Dynamic SQL
> http://www.sommarskog.se/dynamic_sql.html
>
> declare @sql nvarchar(4000)
> declare @orderID int
> declare @sField sysname
> declare @result int
>
> set @orderID = 10248
> set @sField = N'UnitPrice'
>
> set @sql = N'SELECT @result = SUM([' + @sField + N']) FROM [Order Details]
> WHERE orderID = @orderID'
>
> exec sp_executesql @sql, N'@orderID int, @result int output', @orderID,
> @result output
>
> print @result
> go
>
>
> AMB
>
> "Scott" wrote:
>
>> I'm trying to use sField as a variable for a field in the [Order Details]
>> table in Northwind, but having trouble with syntax. Can anyone shed light
>> on
>> a solution?
>>
>> CODE:
>>
>> declare @orderID int
>> declare @sField Char(40)
>>
>> set @orderID = '10248'
>> set @sField='UnitPrice'
>>
>> "SELECT SUM(" + @sField + ") FROM [Order Details] WHERE orderID=" +
>> @orderID
>>
>>
>>

11 Nov 2005 5:08 PM

David Portas

N' just denotes a unicode literal string rather than a ANSI string.

In your case it seems unnecessary and wasteful to use dynamic SQL

DECLARE @orderid INTEGER
DECLARE @sfield CHAR(40)

SET @orderid = '10248'
SET @sfield = 'unitprice'

SELECT
CASE @sfield
WHEN 'unitprice' THEN SUM(unitprice)
WHEN 'quantity' THEN SUM(quantity)
END
FROM [order details]
WHERE orderid = @orderid ;

--
David Portas
SQL Server MVP
--

11 Nov 2005 10:38 PM

Scott

i have a problem with your syntax. Can you fix my code to work. The reason I
need it written this way is I will be using about 3 WHERE variables and
couldn't figure out how to pass them with your previous syntax.

CODE:

declare @orderID int
declare @sField sysname
declare @result int

set @orderID = 10248
set @sField = 'UnitPrice'

set @result = 'SELECT @result = SUM([' + @sField + ']) FROM [Order Details]
WHERE orderID = @orderID'

print @result

Show quote

"David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message
news:1131728882.552388.46330@f14g2000cwb.googlegroups.com...
> N' just denotes a unicode literal string rather than a ANSI string.
>
> In your case it seems unnecessary and wasteful to use dynamic SQL
>
> DECLARE @orderid INTEGER
> DECLARE @sfield CHAR(40)
>
> SET @orderid = '10248'
> SET @sfield = 'unitprice'
>
> SELECT
> CASE @sfield
> WHEN 'unitprice' THEN SUM(unitprice)
> WHEN 'quantity' THEN SUM(quantity)
> END
> FROM [order details]
> WHERE orderid = @orderid ;
>
> --
> David Portas
> SQL Server MVP
> --
>

12 Nov 2005 11:38 AM

David Portas

Scott wrote:

> i have a problem with your syntax. Can you fix my code to work. The reason I
> need it written this way is I will be using about 3 WHERE variables and
> couldn't figure out how to pass them with your previous syntax.
>

I'm no longer clear about what you are trying to achive. WHERE can be
parametemerized without using dynamic SQL. You also don't need dynamic
code to return a value to a variable - in fact it's easier to do
without.

DECLARE @orderid INTEGER
DECLARE @sfield CHAR(40)
DECLARE @result INTEGER

SET @orderid = '10248'
SET @sfield = 'unitprice'

SET @result =
(SELECT
   CASE @sfield
    WHEN 'unitprice' THEN SUM(unitprice)
    WHEN 'quantity' THEN SUM(quantity)
   END
   FROM [order details]
   WHERE orderid = @orderid) ;

PRINT @result

--
David Portas
SQL Server MVP
--