Field Name variable

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=" +
@.orderIDScott,
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 sq
l.
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
>
>|||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
"Alejandro Mesa" <AlejandroMesa@.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:
>|||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
--|||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
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.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
> --
>|||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
--