And you thought i was gone!! NEVER
-
Hello all,
Got a simple one for you,,,, hard one for me...
For my Procedure below why do some of the statement blocks use the -----------> ( and the ---------> ) ( ) to delimt the block,,, and why can't i use it on all of the blocks..
The lower blocks will not excute if i delimit using the ( )
Erik....
CREATE PROCEDURE GetDataByPageSize
@CurrentPage int,
@PageSize int,
@TotalRecords int OUT
AS
Create Table #Temp
(
ID INT IDENTITY PRIMARY KEY,
CompanyName varchar (45),
FirstName varchar (45),
LastName varchar (45),
)
INSERT INTO #Temp
(
CompanyName,
FirstName,
LastName
)
SELECT
CompanyName,
FirstName,
LastName
FROM Customers
/**/
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@CurrentPage - 1) * @PageSize
SELECT @LastRec = (@CurrentPage * @PageSize + 1)
/**/
SELECT
CompanyName,
FirstName,
LastName
from #Temp
WHERE
(
ID > @FirstRec
AND
ID < @LastRec
)
SELECT @TotalRecords = COUNT(*)FROM Customers
Dam again!
-
count(*)from should have a space. Bot other than that you should be alright (assuming no mathematical formula errors). Where's the problem exactly?
-
There was not problem on this one after i removed the ( and the ) where they did not belong
Do it like this.
Here is the one that i am learning off of..................
ALTER PROCEDURE
Get_Customers_By_Page
@CurrentPage
int,
@PageSize
int,
@TotalRecords
int output
AS
--Create a temp table to hold the current page of data
--Add and ID column to count the records
CREATE TABLE
#TempTable
(
ID
int IDENTITY PRIMARY KEY,
CompanyName
nvarchar(40),
ContactName
nvarchar (30),
ContactTitle
nvarchar (30),
Phone
nvarchar (24),
Fax
nvarchar (24)
)
--Fill the temp table with the Customers data
INSERT INTO
#TempTable
(
CompanyName,
ContactName,
ContactTitle,
Phone,
Fax
)
SELECT
CompanyName,
ContactName,
ContactTitle,
Phone,
Fax
FROM
Customers
--Create variable to identify the first and last record that should be selected
DECLARE
@FirstRec int, @LastRec int
SELECT
@FirstRec = (@CurrentPage - 1) * @PageSize
SELECT
@LastRec = (@CurrentPage * @PageSize + 1)
--Select one page of data based on the record numbers above
SELECT
CompanyName,
ContactName,
ContactTitle,
Phone,
Fax
FROM
#TempTable
WHERE
ID > @FirstRec
AND
ID < @LastRec
--Return the total number of records available as an output parameter
SELECT
@TotalRecords = COUNT(*) FROM Customers
-----------------------------------------------------------------
When ever i was rewriting this (i rewrite code over and over to understand it better) i only messed up one thing.. I added ..... ( .........and........) in areas where they did not belong.
Like so
---------------------------------------------------------
ALTER PROCEDURE
Get_Customers_By_Page
@CurrentPage
int,
@PageSize
int,
@TotalRecords
int output
AS
--Create a temp table to hold the current page of data
--Add and ID column to count the records
CREATE TABLE
#TempTable
(
ID
int IDENTITY PRIMARY KEY,
CompanyName
nvarchar(40),
ContactName
nvarchar (30),
ContactTitle
nvarchar (30),
Phone
nvarchar (24),
Fax
nvarchar (24)
)
--Fill the temp table with the Customers data
INSERT INTO
#TempTable
(
CompanyName,
ContactName,
ContactTitle,
Phone,
Fax
)
SELECT
(
CompanyName,
ContactName,
ContactTitle,
Phone,
Fax
)
FROM
Customers
--Create variable to identify the first and last record that should be selected
DECLARE
@FirstRec int, @LastRec int
SELECT
@FirstRec = (@CurrentPage - 1) * @PageSize
SELECT
@LastRec = (@CurrentPage * @PageSize + 1)
--Select one page of data based on the record numbers above
SELECT
(
CompanyName,
ContactName,
ContactTitle,
Phone,
Fax
)
FROM
#TempTable
WHERE
ID > @FirstRec
AND
ID < @LastRec
--Return the total number of records available as an output parameter
SELECT
@TotalRecords = COUNT(*) FROM Customers
Dam again!
-
Well you just can't add () just after the select if it's not a derived table.
BTW we knew you weren't gone
. -
Just another mystery
Dam again!
-
What's mysterious here??
-
The mystery here is that i need to know why some statement blocks are delimited with the ()
Dam again!
-
Because that's the way it is... also because of mathematical operations. And that's the only answer you'll get from me
. -
Could it be that little thing called syntax
For some commands the syntax requires (), for other it doesn't. This is part of the language specification. To understand why () are required you should understand the syntax of the command you're using.
Unless of course you're using VB where Call MyFunction(Param1, Param2) and MyFunction Param1, Param2 are accomplish the same thing
--------------------
Colt 45 - the original point and click interface
-
Succint and accurate Phil !!!
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
I see it better now with the vb demo..
thanks,
erik.
Dam again!
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply