Dynamic query to store record count in variable??

Hello everyone,
I am attempting to write a stored procedure to retrieve the record count but
have failed so far. Here's what I have tried...
SET @.value = 'SELECT COUNT(*) FROM ' + @.tablea
EXECUTE sp_executesql @.sql
...but this just ends up displaying the count to the screen, so I did...
SET @.value = 'DECLARE @.tablea_count NVARCHAR(128) SELECT
@.tablea_count=COUNT(*) FROM ' + @.tablea
EXECUTE sp_executesql @.sql, @.tablea_count OUTPUT
but this doesnt seem to work either.
Help?
--
ChrisHave a look here:
http://www.sommarskog.se/dynamic_sql.html
http://www.support.microsoft.com/?id=262499
Andrew J. Kelly SQL MVP
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:292BA190-F8FD-4696-A6EA-8EE930095F5A@.microsoft.com...
> Hello everyone,
> I am attempting to write a stored procedure to retrieve the record count
> but
> have failed so far. Here's what I have tried...
> SET @.value = 'SELECT COUNT(*) FROM ' + @.tablea
> EXECUTE sp_executesql @.sql
> ...but this just ends up displaying the count to the screen, so I did...
> SET @.value = 'DECLARE @.tablea_count NVARCHAR(128) SELECT
> @.tablea_count=COUNT(*) FROM ' + @.tablea
> EXECUTE sp_executesql @.sql, @.tablea_count OUTPUT
> but this doesnt seem to work either.
> Help?
> --
> Chris

Dynamic query to store record count in variable??

Hello everyone,
I am attempting to write a stored procedure to retrieve the record count but
have failed so far. Here's what I have tried...
SET @.value = 'SELECT COUNT(*) FROM ' + @.tablea
EXECUTE sp_executesql @.sql
...but this just ends up displaying the count to the screen, so I did...
SET @.value = 'DECLARE @.tablea_count NVARCHAR(128) SELECT
@.tablea_count=COUNT(*) FROM ' + @.tablea
EXECUTE sp_executesql @.sql, @.tablea_count OUTPUT
but this doesnt seem to work either.
Help?
Chris
Have a look here:
http://www.sommarskog.se/dynamic_sql.html
http://www.support.microsoft.com/?id=262499
Andrew J. Kelly SQL MVP
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:292BA190-F8FD-4696-A6EA-8EE930095F5A@.microsoft.com...
> Hello everyone,
> I am attempting to write a stored procedure to retrieve the record count
> but
> have failed so far. Here's what I have tried...
> SET @.value = 'SELECT COUNT(*) FROM ' + @.tablea
> EXECUTE sp_executesql @.sql
> ...but this just ends up displaying the count to the screen, so I did...
> SET @.value = 'DECLARE @.tablea_count NVARCHAR(128) SELECT
> @.tablea_count=COUNT(*) FROM ' + @.tablea
> EXECUTE sp_executesql @.sql, @.tablea_count OUTPUT
> but this doesnt seem to work either.
> Help?
> --
> Chris
sql

Dynamic query to store record count in variable??

Hello everyone,
I am attempting to write a stored procedure to retrieve the record count but
have failed so far. Here's what I have tried...
SET @.value = 'SELECT COUNT(*) FROM ' + @.tablea
EXECUTE sp_executesql @.sql
...but this just ends up displaying the count to the screen, so I did...
SET @.value = 'DECLARE @.tablea_count NVARCHAR(128) SELECT
@.tablea_count=COUNT(*) FROM ' + @.tablea
EXECUTE sp_executesql @.sql, @.tablea_count OUTPUT
but this doesnt seem to work either.
Help?
--
ChrisHave a look here:
http://www.sommarskog.se/dynamic_sql.html
http://www.support.microsoft.com/?id=262499
--
Andrew J. Kelly SQL MVP
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:292BA190-F8FD-4696-A6EA-8EE930095F5A@.microsoft.com...
> Hello everyone,
> I am attempting to write a stored procedure to retrieve the record count
> but
> have failed so far. Here's what I have tried...
> SET @.value = 'SELECT COUNT(*) FROM ' + @.tablea
> EXECUTE sp_executesql @.sql
> ...but this just ends up displaying the count to the screen, so I did...
> SET @.value = 'DECLARE @.tablea_count NVARCHAR(128) SELECT
> @.tablea_count=COUNT(*) FROM ' + @.tablea
> EXECUTE sp_executesql @.sql, @.tablea_count OUTPUT
> but this doesnt seem to work either.
> Help?
> --
> Chris

Dynamic Order By!

Hi all, I am new to store procedures and am trying to write one with a
dynamic or..
Below is my feeble attempt if anyone can point me in the right direction
that would be great.
CREATE PROCEDURE get_event_list
@.order_column INT = null
@.order_mode = varchar(4)
AS
BEGIN
SELECT event_code,event_title,event_topic,event_category, event_id FROM
event
ORDER BY
//select a table column based on a numeric value in @.order_column
parameter
//Then determine orientation by value in @.order_mode
//If order_mode = "asc" then the query is to be order ASC and vica versa.
END
AJ
CREATE PROCEDURE get_event_list
(@.order_column INT = NULL,
@.order_mode VARCHAR(4))
AS
BEGIN
SELECT event_code, event_title, event_topic, event_category, event_id
FROM Event
ORDER BY
CASE WHEN @.order_mode='ASC'
AND @.order_column=1 THEN event_code END ASC,
CASE WHEN @.order_mode='ASC'
AND @.order_column=2 THEN event_title END ASC,
...
CASE WHEN @.order_mode='DESC'
AND @.order_column=1 THEN event_code END DESC,
CASE WHEN @.order_mode='DESC'
AND @.order_column=2 THEN event_title END DESC,
...
END
David Portas
SQL Server MVP
|||What I also should say is that you'll probably find you will get better
performance by including all the possible SELECTs in your SP, enclosing each
in an IF statement. If you prefer a single query solution then you can use
the CASE method I have given, or you could try using Dynamic SQL.
David Portas
SQL Server MVP
|||Anthony Judd wrote:
> Hi all, I am new to store procedures and am trying to write one with a
> dynamic or..
> Below is my feeble attempt if anyone can point me in the right direction
> that would be great.
Here is some example I have. I know it is not a fancy solution, but it
is flexible...
CREATE PROCEDURE SomeList
@.akt CHAR(1) = '1',
@.OrderBy VARCHAR(50) = 'NazivUcionice',
@.OrderSort VARCHAR(10) = 'ASC'
AS
DECLARE @.SQLstmt varchar (1000)
SELECT @.SQLstmt =
'SELECT U.ID AS ElementID,
U.Naziv
U.Vrsta,
U.Tip,
U.Vlasnistvo,
U.Aktivna,
U.Broj_mjesta
FROM U
WHERE U.Aktivna=' +@.akt + '
ORDER BY ' + @.OrderBy + ' ' + @.OrderSort
EXEC (@.SQLstmt)
GO
Tomislav Bilic
ICQ: 1824223
http://www.escapestudio.net
sql

Dynamic Order By!

Hi all, I am new to store procedures and am trying to write one with a
dynamic or..
Below is my feeble attempt if anyone can point me in the right direction
that would be great.
CREATE PROCEDURE get_event_list
@.order_column INT = null
@.order_mode = varchar(4)
AS
BEGIN
SELECT event_code,event_title,event_topic,event_category,event_id FROM
event
ORDER BY
//select a table column based on a numeric value in @.order_column
parameter
//Then determine orientation by value in @.order_mode
//If order_mode = "asc" then the query is to be order ASC and vica versa.
END
AJCREATE PROCEDURE get_event_list
(@.order_column INT = NULL,
@.order_mode VARCHAR(4))
AS
BEGIN
SELECT event_code, event_title, event_topic, event_category, event_id
FROM Event
ORDER BY
CASE WHEN @.order_mode='ASC'
AND @.order_column=1 THEN event_code END ASC,
CASE WHEN @.order_mode='ASC'
AND @.order_column=2 THEN event_title END ASC,
...
CASE WHEN @.order_mode='DESC'
AND @.order_column=1 THEN event_code END DESC,
CASE WHEN @.order_mode='DESC'
AND @.order_column=2 THEN event_title END DESC,
...
END
--
David Portas
SQL Server MVP
--|||What I also should say is that you'll probably find you will get better
performance by including all the possible SELECTs in your SP, enclosing each
in an IF statement. If you prefer a single query solution then you can use
the CASE method I have given, or you could try using Dynamic SQL.
--
David Portas
SQL Server MVP
--|||Hey guys,
How do I create views in MSSQL where the source is coming from an Oracle
table?
Thanks.
neil|||Anthony Judd wrote:
> Hi all, I am new to store procedures and am trying to write one with a
> dynamic or..
> Below is my feeble attempt if anyone can point me in the right direction
> that would be great.
Here is some example I have. I know it is not a fancy solution, but it
is flexible...
CREATE PROCEDURE SomeList
@.akt CHAR(1) = '1',
@.OrderBy VARCHAR(50) = 'NazivUcionice',
@.OrderSort VARCHAR(10) = 'ASC'
AS
DECLARE @.SQLstmt varchar (1000)
SELECT @.SQLstmt = 'SELECT U.ID AS ElementID,
U.Naziv
U.Vrsta,
U.Tip,
U.Vlasnistvo,
U.Aktivna,
U.Broj_mjesta
FROM U
WHERE U.Aktivna=' +@.akt + '
ORDER BY ' + @.OrderBy + ' ' + @.OrderSort
EXEC (@.SQLstmt)
GO
--
Tomislav Bilic
ICQ: 1824223
http://www.escapestudio.net|||Using the Oracle OLEDB Provider, either create a linked server or use
OPENROWSET and specify the connection string.
--
David Portas
SQL Server MVP
--

dynamic cross tab without aggregation? PLEASE help

Ok..we have a db that the company wants to store roles in (ie,
'auditor', 'accountant', etc..) that are active for a specific date
range ...its a wierd paradigm, dont ask... but we need a specific # of
roles filled for each date...
here's the script
Create table tblRoles(RoleID int primary key identity(1,1), RoleName
nvarchar(255))
create table tblDates(DateID int primary key identity(1,1),
ProjectDate DateTime)
create table tblRolesForDate(RoleForDateID int primary key identity
(1,1), RoleID int, DateID int, NumberNeeded)
what I need to see is a table from that...like this.
1/1 1/2 1/3 1/4 1/5
auditor 0 5 5 2 0
account 5 5 5 0 15
etc...
any ideas on how to make that work? the dates across the top are
from the dates table, the names in the left column are from roles, and
the #'s come from RolesForDate...
Grrr
Replied in .programming
Please do NOT multi-post.
David Portas

dynamic cross tab without aggregation? PLEASE help

Ok..we have a db that the company wants to store roles in (ie,
'auditor', 'accountant', etc..) that are active for a specific date
range ...its a wierd paradigm, dont ask... but we need a specific # of
roles filled for each date...
here's the script
Create table tblRoles(RoleID int primary key identity(1,1), RoleName
nvarchar(255))
create table tblDates(DateID int primary key identity(1,1),
ProjectDate DateTime)
create table tblRolesForDate(RoleForDateID int primary key identity
(1,1), RoleID int, DateID int, NumberNeeded)
what I need to see is a table from that...like this.
1/1 1/2 1/3 1/4 1/5
auditor 0 5 5 2 0
account 5 5 5 0 15
etc...
any ideas on how to make that work? the dates across the top are
from the dates table, the names in the left column are from roles, and
the #'s come from RolesForDate...
GrrrReplied in .programming
Please do NOT multi-post.
David Portas

dynamic cross tab without aggregation? PLEASE help

Ok..we have a db that the company wants to store roles in (ie,
'auditor', 'accountant', etc..) that are active for a specific date
range ...its a wierd paradigm, dont ask... but we need a specific # of
roles filled for each date...
here's the script
Create table tblRoles(RoleID int primary key identity(1,1), RoleName
nvarchar(255))
create table tblDates(DateID int primary key identity(1,1),
ProjectDate DateTime)
create table tblRolesForDate(RoleForDateID int primary key identity
(1,1), RoleID int, DateID int, NumberNeeded)
what I need to see is a table from that...like this.
1/1 1/2 1/3 1/4 1/5
auditor 0 5 5 2 0
account 5 5 5 0 15
etc...
any ideas on how to make that work? the dates across the top are
from the dates table, the names in the left column are from roles, and
the #'s come from RolesForDate...
GrrrReplied in .programming
Please do NOT multi-post.
--
David Portas