Does Sql 2005 allow ORDER BY @Variable ?

I am writing a stored proc and do not want to use dynamic sql. Does Sql
2005 allow ORDER BY @.Variable? The query compiler accepts it, but it
doesn't work. My @.Variable is a VARCHAR(50).
Thanks!This is a multi-part message in MIME format.
--=_NextPart_000_0924_01C71867.3AEC95F0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Not directly. You could use a CASE structure to allow alternative =orderings. Here is one idea:
USE Northwind
GO
DECLARE @.OrderVar varchar(20)
SET @.OrderVar =3D 'LastName'
SELECT LastName,
FirstName
FROM Employees
ORDER BY CASE @.OrderVar
WHEN 'LastName' THEN LastName
WHEN 'FirstName' THEN FirstName
END ASC,
CASE @.OrderVar
WHEN 'LastName' THEN FirstName
WHEN 'FirstName' THEN LastName
END ASC
-- Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous
You can't help someone get up a hill without getting a little closer to =the top yourself.
- H. Norman Schwarzkopf
"Dan E" <dan_english2@.cox.net> wrote in message =news:u1AfegKGHHA.1280@.TK2MSFTNGP04.phx.gbl...
>I am writing a stored proc and do not want to use dynamic sql. Does =Sql > 2005 allow ORDER BY @.Variable? The query compiler accepts it, but it > doesn't work. My @.Variable is a VARCHAR(50).
> > Thanks!
> >
--=_NextPart_000_0924_01C71867.3AEC95F0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Not directly. You could use a CASE =structure to allow alternative orderings. Here is one idea:
USE =NorthwindGO
DECLARE @.OrderVar =varchar(20)SET @.OrderVar =3D 'LastName'
SELECT LastName, FirstNameFROM EmployeesORDER BY CASE @.OrderVar = WHEN 'LastName' THEN LastName &=nbsp; WHEN 'FirstName' THEN FirstName END ASC, CASE @.OrderVar = WHEN 'LastName' THEN FirstName = WHEN 'FirstName' THEN LastName END ASC
-- Arnie Rowland, =Ph.D.Westwood Consulting, Inc
Most good judgment comes from =experience. Most experience comes from bad judgment. - Anonymous
You can't help someone get up a hill =without getting a little closer to the top yourself.- H. Norman Schwarzkopf
"Dan E" =wrote in message news:u1AfegKGHHA.1280@.TK2MSFTNGP04.phx.gbl...>I =am writing a stored proc and do not want to use dynamic sql. Does Sql > =2005 allow ORDER BY @.Variable? The query compiler accepts it, but it => doesn't work. My @.Variable is a VARCHAR(50).> > Thanks!> >

---

--=_NextPart_000_0924_01C71867.3AEC95F0--|||http://databases.aspfaq.com/database/how-do-i-use-a-variable-in-an-order-by-clause.html
"Dan E" <dan_english2@.cox.net> wrote in message
news:u1AfegKGHHA.1280@.TK2MSFTNGP04.phx.gbl...
>I am writing a stored proc and do not want to use dynamic sql. Does Sql
>2005 allow ORDER BY @.Variable? The query compiler accepts it, but it
>doesn't work. My @.Variable is a VARCHAR(50).
> Thanks!
>

Does Sql 2005 allow ORDER BY @Variable ?

I am writing a stored proc and do not want to use dynamic sql. Does Sql
2005 allow ORDER BY @.Variable? The query compiler accepts it, but it
doesn't work. My @.Variable is a VARCHAR(50).
Thanks!
Not directly. You could use a CASE structure to allow alternative orderings. Here is one idea:
USE Northwind
GO
DECLARE @.OrderVar varchar(20)
SET @.OrderVar = 'LastName'
SELECT
LastName,
FirstName
FROM Employees
ORDER BY CASE @.OrderVar
WHEN 'LastName' THEN LastName
WHEN 'FirstName' THEN FirstName
END ASC,
CASE @.OrderVar
WHEN 'LastName' THEN FirstName
WHEN 'FirstName' THEN LastName
END ASC
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the top yourself.
- H. Norman Schwarzkopf
"Dan E" <dan_english2@.cox.net> wrote in message news:u1AfegKGHHA.1280@.TK2MSFTNGP04.phx.gbl...
>I am writing a stored proc and do not want to use dynamic sql. Does Sql
> 2005 allow ORDER BY @.Variable? The query compiler accepts it, but it
> doesn't work. My @.Variable is a VARCHAR(50).
> Thanks!
>
|||http://databases.aspfaq.com/database/how-do-i-use-a-variable-in-an-order-by-clause.html
"Dan E" <dan_english2@.cox.net> wrote in message
news:u1AfegKGHHA.1280@.TK2MSFTNGP04.phx.gbl...
>I am writing a stored proc and do not want to use dynamic sql. Does Sql
>2005 allow ORDER BY @.Variable? The query compiler accepts it, but it
>doesn't work. My @.Variable is a VARCHAR(50).
> Thanks!
>

Does Sql 2005 allow ORDER BY @Variable ?

I am writing a stored proc and do not want to use dynamic sql. Does Sql
2005 allow ORDER BY @.Variable? The query compiler accepts it, but it
doesn't work. My @.Variable is a VARCHAR(50).
Thanks!Not directly. You could use a CASE structure to allow alternative orderings.
Here is one idea:
USE Northwind
GO
DECLARE @.OrderVar varchar(20)
SET @.OrderVar = 'LastName'
SELECT
LastName,
FirstName
FROM Employees
ORDER BY CASE @.OrderVar
WHEN 'LastName' THEN LastName
WHEN 'FirstName' THEN FirstName
END ASC,
CASE @.OrderVar
WHEN 'LastName' THEN FirstName
WHEN 'FirstName' THEN LastName
END ASC
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Dan E" <dan_english2@.cox.net> wrote in message news:u1AfegKGHHA.1280@.TK2MSFTNGP04.phx.gbl..
.
>I am writing a stored proc and do not want to use dynamic sql. Does Sql
> 2005 allow ORDER BY @.Variable? The query compiler accepts it, but it
> doesn't work. My @.Variable is a VARCHAR(50).
>
> Thanks!
>
>|||http://databases.aspfaq.com/databas...se
.html
"Dan E" <dan_english2@.cox.net> wrote in message
news:u1AfegKGHHA.1280@.TK2MSFTNGP04.phx.gbl...
>I am writing a stored proc and do not want to use dynamic sql. Does Sql
>2005 allow ORDER BY @.Variable? The query compiler accepts it, but it
>doesn't work. My @.Variable is a VARCHAR(50).
> Thanks!
>

Does dynamic SQL allow table variables?

Hello!
Please see below the test code that is using dynamic sql and table
variable. It is not working. I am not sure if the dynamic SQL allows
using table variables?
Thanks!
declare @.Tblvar TABLE(a int, b varchar(10))
declare @.s varchar(200)
create table #Dept(a int, b varchar(10))
insert into #Dept values(1, 'abcd')
insert into #Dept values(2, 'xyz')
set @.s = 'insert into ' + @.Tblvar
' select * from #Dept'
exec (@.s)
select * from @.Tblvar
*** Sent via Developersdex http://www.examnotes.net ***The problem is that you didn't create the #Dept table in the same scope.
Inside the EXEC() there is no #Dept table.
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:%23A2JudbLGHA.2276@.TK2MSFTNGP15.phx.gbl...
> Hello!
> Please see below the test code that is using dynamic sql and table
> variable. It is not working. I am not sure if the dynamic SQL allows
> using table variables?
> Thanks!
> declare @.Tblvar TABLE(a int, b varchar(10))
> declare @.s varchar(200)
> create table #Dept(a int, b varchar(10))
> insert into #Dept values(1, 'abcd')
> insert into #Dept values(2, 'xyz')
> set @.s = 'insert into ' + @.Tblvar
> ' select * from #Dept'
> exec (@.s)
> select * from @.Tblvar
>
>
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||As Aaron says..this is a scope problem...table variables must be used
in the same batch...
exec('declare @.Tblvar TABLE(a int, b varchar(10))
declare @.s varchar(200)
create table #Dept(a int, b varchar(10))
insert into #Dept values(1, ''abcd'')
insert into #Dept values(2, ''xyz'')
insert into @.Tblvar
select * from #Dept
select * from @.Tblvar')
MJKulangara
http://sqladventures.blogspot.com|||I see two problems here. First, you are trying to build your query string,
@.s, by concatenating a string with a table, which won't work. You could
instead to
set @.s = 'insert into @.Tblvar select * from #Dept'
but then you encounter another problem: neither the variable @.Tblvar or the
temporary table #Dept is defined in the scope that the query is executed
under with EXEC.
"Test Test" wrote:

> Hello!
> Please see below the test code that is using dynamic sql and table
> variable. It is not working. I am not sure if the dynamic SQL allows
> using table variables?
> Thanks!
> declare @.Tblvar TABLE(a int, b varchar(10))
> declare @.s varchar(200)
> create table #Dept(a int, b varchar(10))
> insert into #Dept values(1, 'abcd')
> insert into #Dept values(2, 'xyz')
> set @.s = 'insert into ' + @.Tblvar
> ' select * from #Dept'
> exec (@.s)
> select * from @.Tblvar
>
>
>
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Yes it does, but due to the fact that exec is opening another session
)verything you execute) it has to be put within the context of the (in
your case) INSERT INTO statement. Otherwise you can use a global
temporary data to share data with if you want to.
HTH, Jens Suessmeyer..|||Test Test (farooqhs_2000@.yahoo.com) writes:
> Please see below the test code that is using dynamic sql and table
> variable. It is not working. I am not sure if the dynamic SQL allows
> using table variables?
> Thanks!
> declare @.Tblvar TABLE(a int, b varchar(10))
> declare @.s varchar(200)
> create table #Dept(a int, b varchar(10))
> insert into #Dept values(1, 'abcd')
> insert into #Dept values(2, 'xyz')
> set @.s = 'insert into ' + @.Tblvar
> ' select * from #Dept'
> exec (@.s)
> select * from @.Tblvar
The dynamic SQL constitutes a scope on its own, and variables are
only visible in the direct scope that created it. This is in difference
to temp tables which are visible for inner scopes. (No less than two
posters gave incorrect information on this.)
A scope is a stored procedure, function, trigger - or a batch of dynamic
SQL.
A good demonstration of this is:
CREATE PROCEDURE nestlevel_sp AS
SELECT @.@.nestlevel
EXEC('SELECT @.@.nestlevel')
EXEC sp_executesql N'SELECT @.@.nestlevel'
go
EXEC nestlevel_sp
This prints 1, 2 3 in that order.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks to everyone!!!
*** Sent via Developersdex http://www.examnotes.net ***

Does 2k5 Reporting Services need IIS?

I'd like to produce dynamic reports via SQL Server 2k5 Reporting
Services that can be rendered in a web browser. However, I don't want
anything to do with IIS or a web server. From what I've heard,
previous version of SQL Server Reporting Services needed IIS. Is this
true for 2k5?
Thanks,
BrettRS 2005 comes with WebForm and WinForm controls for showing reports. Also, t
hese can be client side
only (where the reporting engine is essentially built into the controls). Bu
t this is about as much
as I know. I suggest you post this to an RS forum, where you are more likely
to get a precise
response.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"brett" <account@.cygen.com> wrote in message
news:1142924193.608686.191110@.z34g2000cwc.googlegroups.com...
> I'd like to produce dynamic reports via SQL Server 2k5 Reporting
> Services that can be rendered in a web browser. However, I don't want
> anything to do with IIS or a web server. From what I've heard,
> previous version of SQL Server Reporting Services needed IIS. Is this
> true for 2k5?
> Thanks,
> Brett
>|||Yes, Reporting Services runs inside IIS.
"brett" <account@.cygen.com> wrote in message
news:1142924193.608686.191110@.z34g2000cwc.googlegroups.com...
> I'd like to produce dynamic reports via SQL Server 2k5 Reporting
> Services that can be rendered in a web browser. However, I don't want
> anything to do with IIS or a web server. From what I've heard,
> previous version of SQL Server Reporting Services needed IIS. Is this
> true for 2k5?
> Thanks,
> Brett
>

Does 2k5 Reporting Services need IIS?

I'd like to produce dynamic reports via SQL Server 2k5 Reporting
Services that can be rendered in a web browser. However, I don't want
anything to do with IIS or a web server. From what I've heard,
previous version of SQL Server Reporting Services needed IIS. Is this
true for 2k5?
Thanks,
BrettYes
"brett" <account@.cygen.com> schrieb im Newsbeitrag
news:1142925712.659616.140540@.t31g2000cwb.googlegroups.com...
> I'd like to produce dynamic reports via SQL Server 2k5 Reporting
> Services that can be rendered in a web browser. However, I don't want
> anything to do with IIS or a web server. From what I've heard,
> previous version of SQL Server Reporting Services needed IIS. Is this
> true for 2k5?
> Thanks,
> Brett
>|||Also, VS 2005 comes with two controls. A winform and a webform control.
These controls can either work with RS or can run in local mode where no
server is required. It takes some more work, especially for subreports, jump
to report etc but it works quite well. In local mode you give it the report
and the data and then it renders it.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"brett" <account@.cygen.com> wrote in message
news:1142925712.659616.140540@.t31g2000cwb.googlegroups.com...
> I'd like to produce dynamic reports via SQL Server 2k5 Reporting
> Services that can be rendered in a web browser. However, I don't want
> anything to do with IIS or a web server. From what I've heard,
> previous version of SQL Server Reporting Services needed IIS. Is this
> true for 2k5?
> Thanks,
> Brett
>