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 a role exist?

In my proc I have to add a user to a role in another database. I do it
in the following manner:
declare @.IIDdatabase varchar(255)
set @.IIDdatabase = 'IID'
SELECT @.cmd = N'USE ' + CAST(@.IIDdatabase as nvarchar) + @.crlf + N'exec
sp_addrolemember @.rolename = ''Role_MyCustomRole'', @.membername = ''' +
CAST(@.user as nvarchar) + '''' + @.crlf
EXEC @.STATUS = sp_executesql @.cmd
And this seems to work fine. However, how do I find out whether the
role (in the other database) exists to begin with? The difficulty here
is that the name of the database is passed in as a variable.
Thanks.Hi Frank,
I understand that you would like to judge wheter the database role is
existed before your execute your SQL statement.
If I have misunderstood, please let me know.
You may refer to the following stored procedure:
---
/* judge if a database role is existed in a database */
CREATE PROCEDURE proc_existrole
(
@.dbname nvarchar(50)=null,
@.rolename nvarchar(50)=null,
@.isexisted int output
)
as
declare @.strSQL nvarchar(200)
declare @.strCount nvarchar(50)
begin
if @.dbname is null or @.rolename is null
begin
set @.isexisted = -1
return
end
set @.strCount = N'@.nCount int output'
set @.strSQL = N'select @.nCount = count(*) from ' + @.dbname
+'.sys.database_principals where type=''R'' and name='''+@.rolename + ''''
execute sp_executesql @.strSQL, @.strCount,@.nCount = @.isexisted output
if @.isexisted>0
set @.isexisted = 1
else
set @.isexisted = 0
end
----
--
You can call it like this:
----
--
declare @.isexisted int
exec proc_existrole @.IIDdatabase,N'Role_MyCustomRole',@.isexisted output
if @.isexisted=0
begin
..
end
----
--
Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Best regards,
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||I am sorry, I should have mentioned. The code must work on SQL 2000 as
well as SQL 2005.
Charles Wang[MSFT] wrote:
> Hi Frank,
> I understand that you would like to judge wheter the database role is
> existed before your execute your SQL statement.
> If I have misunderstood, please let me know.
> You may refer to the following stored procedure:
> ---
> /* judge if a database role is existed in a database */
> CREATE PROCEDURE proc_existrole
> (
> @.dbname nvarchar(50)=null,
> @.rolename nvarchar(50)=null,
> @.isexisted int output
> )
> as
> declare @.strSQL nvarchar(200)
> declare @.strCount nvarchar(50)
> begin
> if @.dbname is null or @.rolename is null
> begin
> set @.isexisted = -1
> return
> end
> set @.strCount = N'@.nCount int output'
> set @.strSQL = N'select @.nCount = count(*) from ' + @.dbname
> +'.sys.database_principals where type=''R'' and name='''+@.rolename + ''''
> execute sp_executesql @.strSQL, @.strCount,@.nCount = @.isexisted output
> if @.isexisted>0
> set @.isexisted = 1
> else
> set @.isexisted = 0
> end
> ----
> --
> You can call it like this:
> ----
> --
> declare @.isexisted int
> exec proc_existrole @.IIDdatabase,N'Role_MyCustomRole',@.isexisted output
> if @.isexisted=0
> begin
> ..
> end
> ----
> --
> Hope this helps. If you have any other questions or concerns, please feel
> free to let me know.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ======================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
> ======================================================>|||Frank,
This will still work on both 2000 and 2005, although it is certainly
deprecated.
SELECT name
FROM sysusers
WHERE issqlrole = 1
RLF
"Frank Rizzo" <none@.none.com> wrote in message
news:uEsuyFB5HHA.2108@.TK2MSFTNGP02.phx.gbl...
>I am sorry, I should have mentioned. The code must work on SQL 2000 as
>well as SQL 2005.
> Charles Wang[MSFT] wrote:
>> Hi Frank,
>> I understand that you would like to judge wheter the database role is
>> existed before your execute your SQL statement.
>> If I have misunderstood, please let me know.
>> You may refer to the following stored procedure:
>> ---
>> /* judge if a database role is existed in a database */
>> CREATE PROCEDURE proc_existrole
>> (
>> @.dbname nvarchar(50)=null,
>> @.rolename nvarchar(50)=null,
>> @.isexisted int output
>> )
>> as
>> declare @.strSQL nvarchar(200)
>> declare @.strCount nvarchar(50)
>> begin
>> if @.dbname is null or @.rolename is null
>> begin
>> set @.isexisted = -1
>> return
>> end
>> set @.strCount = N'@.nCount int output'
>> set @.strSQL = N'select @.nCount = count(*) from ' + @.dbname
>> +'.sys.database_principals where type=''R'' and name='''+@.rolename + ''''
>> execute sp_executesql @.strSQL, @.strCount,@.nCount = @.isexisted output
>> if @.isexisted>0
>> set @.isexisted = 1
>> else
>> set @.isexisted = 0
>> end
>> ----
>> --
>> You can call it like this:
>> ----
>> --
>> declare @.isexisted int
>> exec proc_existrole @.IIDdatabase,N'Role_MyCustomRole',@.isexisted output
>> if @.isexisted=0
>> begin
>> ..
>> end
>> ----
>> --
>> Hope this helps. If you have any other questions or concerns, please feel
>> free to let me know.
>> Best regards,
>> Charles Wang
>> Microsoft Online Community Support
>> ====================================================== When responding to
>> posts, please "Reply to Group" via your newsreader so that others may
>> learn and benefit from this issue.
>> ======================================================>> This posting is provided "AS IS" with no warranties, and confers no
>> rights. ======================================================|||Hi Frank,
In this case, you can combine Russell's suggestion and mine into one
procedure like this:
----
--
/* judge if a database role is existed in a database */
ALTER PROCEDURE proc_existrole
(
@.dbname nvarchar(50)=null,
@.rolename nvarchar(50)=null,
@.isexisted int output
)
as
declare @.strSQL nvarchar(200)
declare @.strCount nvarchar(50)
declare @.productVersion nchar(1)
begin
if @.dbname is null or @.rolename is null
begin
set @.isexisted = -1
return
end
set @.strCount = N'@.nCount int output'
SELECT @.productVersion = SUBSTRING(CAST(SERVERPROPERTY ( 'PRODUCTVERSION' )
AS NVARCHAR(10)),1,1)
IF @.productVersion = '9'
BEGIN
set @.strSQL = N'select @.nCount = count(*) from ' + @.dbname
+'.sys.database_principals where type=''R'' and name='''+@.rolename + ''''
END
ELSE
BEGIN
set @.strSQL = N'select @.nCount = count(*) from ' + @.dbname
+'.dbo.sysusers where issqlrole=1 and name='''+@.rolename + ''''
END
execute sp_executesql @.strSQL, @.strCount,@.nCount = @.isexisted output
if @.isexisted>0
set @.isexisted = 1
else
set @.isexisted = 0
end
----
--
Please feel free to let us know if you need further assistance on this
issue. Have a good day!
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hi Frank,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Charles Wang[MSFT] wrote:
> Hi Frank,
> I am interested in this issue. Would you mind letting me know the result of
> the suggestions? If you need further assistance, feel free to let me know.
> I will be more than happy to be of assistance.
Thanks, your suggestion worked great.