[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

How do I use a parameter to create a query heading in a stored pro

Mark Hollander

3/28/2007 12:40:00 PM

I have written a strored procedure that will return a incremented number, I
would like to use the value of the variable as the column heading.
CREATE PROCEDURE GetNextID @vcIDName as VARCHAR(30) AS
.... code ...
-- Would like to use something like this
Select @NextID as @vcIDName -- This gives me an error
-- Instead of
Select @NextID as 'RETVAL' -- This works but is not ideal

Your help will be greatly appreciated
--
Thank you
Mark Hollander
Novice SQL Programmer
8 Answers

Uri Dimant

3/28/2007 12:47:00 PM

0

Mark
> Select @NextID as vcIDName --not variable

Permit me to show some trick (learned from Itzik Ben-Gan) how to get a next
value

create table seq(val int not null);
insert into seq values(0);
go
select * from seq
create proc usp_getseq @val as int output
as
update seq set @val = val = val + 1;
go
-- usage
declare @i as int;
exec usp_getseq @i output;
select @i;






"Mark Hollander" <MarkHollander@discussions.microsoft.com> wrote in message
news:3578A2C0-9E78-4444-B290-B0B1E507E3AC@microsoft.com...
>I have written a strored procedure that will return a incremented number, I
> would like to use the value of the variable as the column heading.
> CREATE PROCEDURE GetNextID @vcIDName as VARCHAR(30) AS
> ... code ...
> -- Would like to use something like this
> Select @NextID as @vcIDName -- This gives me an error
> -- Instead of
> Select @NextID as 'RETVAL' -- This works but is not ideal
>
> Your help will be greatly appreciated
> --
> Thank you
> Mark Hollander
> Novice SQL Programmer


Mark Hollander

3/28/2007 1:08:00 PM

0

Thanks,
This is fine when working with a single incremental number, The procdure I
have written can handle has many incremental numbers as the max records allow
in the sql server that is why I need to be able to use the input variable as
the column heading, and I cannot seem to get it right. See below for the code:
---- START OF CODE ----
CREATE PROCEDURE _GetNextID
/*
This function will extract the number from the tblCounters table and
increment the value
by 1. This stored procedure must only be called from another stored
procedure as it does
not return any rows in a select query. It is designed only to work
with other stored
procedures.
*/
( -- Start Parameter Declaration
@vcIDName as VARCHAR(30) -- The name of the variable to extract the
number
) -- End Parameter Declaration
AS

/*
This BEGIN-END block is where all the variables are declared
*/
BEGIN -- Variable Declarations start
-- The next id storage variable
DECLARE @nNextID Numeric
SELECT @nNextID = 0 -- Initialise the variable to it's default value

/*
Declare a cursor set that applies the record locking on the
selected record. We want it to be a forward only cursor as we
are not performing any backward operations.
*/
END -- Variable Declarations end

SelectQuery:
BEGIN -- Select Query Start
/*
The name of the variable is stored in the NAME and the
actual value is stored in the COUNTER field.
*/
DECLARE crsID CURSOR
LOCAL
FORWARD_ONLY
SCROLL_LOCKS
FOR
SELECT COUNTER
FROM tblCounters
WHERE [NAME] = @vcIDName
END

-- We do not want to return the record count of records affected
SET NOCOUNT ON

-- Start a sequence
BEGIN
-- Open the cursor set for processing
OPEN crsID
/*
Fetch the first available record into the variable
If you examine the select statement above you will see
that the select querie returns only one value hence the
reason we fetch from the cursor into only one variable.
*/
FETCH NEXT FROM crsID INTO @nNextID

/*
Check to see if there was an error in the fetch statement.
A return value of 0 means that the fetch statement executed
without any errors i.e. Was successfull.
*/
IF @@FETCH_STATUS <> 0
/*
It is possible that the variable name does not exist
Attempt to insert the variable name and initialise it to 0
If it does exist return -1
*/
BEGIN
-- We must close the cursor so that it will release the
record locks
CLOSE crsID
DEALLOCATE crsID
INSERT INTO tblCounters (NAME,COUNTER) VALUES (@vcIDName,1)
IF (@@ERROR <> 0)
SELECT @nNextID = @@ERROR * -1
ELSE
GOTO SelectQuery
END
ELSE -- @@FETCH_STATUS <> 0, Successfully retrieved
BEGIN
/*
At this point the @biNextID contains the existing number
so all we have to do is to increment the value by 1
*/
UPDATE tblCounters
SET COUNTER = @nNextID + 1
WHERE NAME = @vcIDName
END
-- We must close the cursor so that it will release the record locks
CLOSE crsID
DEALLOCATE crsID
END



-- Return the value to the calling Stored Procedure
RETURN @nNextID

GO

CREATE PROCEDURE GetNextID
/*
This stored procedure wraps the sdk_func_GetNextID so that it can
return the
value in a select query.
*/
( -- Start Parameter Declaration
@vcIDName as VARCHAR(30) -- The name of the variable to extract the
number
) -- End Parameter Declaration
AS

/*
This BEGIN-END block is where all the variables are declared
*/
BEGIN -- Variable Declarations start
-- The next parameter sequence storage variable
DECLARE @nNextID Numeric
END -- Variable Declarations end

-- We do not want to return the record count of records affected
SET NOCOUNT ON

/*
The way to get a return value from a stored procedure is to
use the EXECUTE command.

in VB we would call a function like this
variable = functionname(parameter1, parameter2, ...)
in SQL we would call a function like this
execute variable = functionname parameter1, parameter2, ...
*/
EXECUTE @nNextID = _GetNextID @vcIDName
/*
Here we use the select command to return the value.
This is where it differs from the Return command.

When an application does a select sql statement, this function will
return
the value, but if we use a return statement, it does not return the
value
but does inform the application that the command was executed
successfully.


Here we say 'ID' is equal to the value.
What it does is the 'ID' becomes the column name and the variable is
returned
as the value.
*/
SELECT 'RETVAL' = @nNextID
GO

CREATE TABLE [dbo].[CountersTbl] (
[Name] [varchar] (30) COLLATE Latin1_General_CI_AS NOT NULL ,
[Counter] [numeric](18, 0) NOT NULL
) ON [PRIMARY]
GO
--- END OF CODE ---

--- USAGE ---
exec GetNextId 'NextInvNo'

--- RESULT ---

RETVAL
----------
1

--
Thank you
Mark Hollander







"Uri Dimant" wrote:

> Mark
> > Select @NextID as vcIDName --not variable
>
> Permit me to show some trick (learned from Itzik Ben-Gan) how to get a next
> value
>
> create table seq(val int not null);
> insert into seq values(0);
> go
> select * from seq
> create proc usp_getseq @val as int output
> as
> update seq set @val = val = val + 1;
> go
> -- usage
> declare @i as int;
> exec usp_getseq @i output;
> select @i;
>
>
>
>
>
>
> "Mark Hollander" <MarkHollander@discussions.microsoft.com> wrote in message
> news:3578A2C0-9E78-4444-B290-B0B1E507E3AC@microsoft.com...
> >I have written a strored procedure that will return a incremented number, I
> > would like to use the value of the variable as the column heading.
> > CREATE PROCEDURE GetNextID @vcIDName as VARCHAR(30) AS
> > ... code ...
> > -- Would like to use something like this
> > Select @NextID as @vcIDName -- This gives me an error
> > -- Instead of
> > Select @NextID as 'RETVAL' -- This works but is not ideal
> >
> > Your help will be greatly appreciated
> > --
> > Thank you
> > Mark Hollander
> > Novice SQL Programmer
>
>
>

Uri Dimant

3/28/2007 1:17:00 PM

0

Mark

I think in order to resturn value you need define a parameter OUTPUT. See
how to use it from my post or from BOL



"Mark Hollander" <MarkHollander@discussions.microsoft.com> wrote in message
news:E1C51C24-A723-4BF8-B27A-FF078A69F01C@microsoft.com...
> Thanks,
> This is fine when working with a single incremental number, The procdure I
> have written can handle has many incremental numbers as the max records
> allow
> in the sql server that is why I need to be able to use the input variable
> as
> the column heading, and I cannot seem to get it right. See below for the
> code:
> ---- START OF CODE ----
> CREATE PROCEDURE _GetNextID
> /*
> This function will extract the number from the tblCounters table
> and
> increment the value
> by 1. This stored procedure must only be called from another stored
> procedure as it does
> not return any rows in a select query. It is designed only to work
> with other stored
> procedures.
> */
> ( -- Start Parameter Declaration
> @vcIDName as VARCHAR(30) -- The name of the variable to extract the
> number
> ) -- End Parameter Declaration
> AS
>
> /*
> This BEGIN-END block is where all the variables are declared
> */
> BEGIN -- Variable Declarations start
> -- The next id storage variable
> DECLARE @nNextID Numeric
> SELECT @nNextID = 0 -- Initialise the variable to it's default
> value
>
> /*
> Declare a cursor set that applies the record locking on the
> selected record. We want it to be a forward only cursor as we
> are not performing any backward operations.
> */
> END -- Variable Declarations end
>
> SelectQuery:
> BEGIN -- Select Query Start
> /*
> The name of the variable is stored in the NAME and the
> actual value is stored in the COUNTER field.
> */
> DECLARE crsID CURSOR
> LOCAL
> FORWARD_ONLY
> SCROLL_LOCKS
> FOR
> SELECT COUNTER
> FROM tblCounters
> WHERE [NAME] = @vcIDName
> END
>
> -- We do not want to return the record count of records affected
> SET NOCOUNT ON
>
> -- Start a sequence
> BEGIN
> -- Open the cursor set for processing
> OPEN crsID
> /*
> Fetch the first available record into the variable
> If you examine the select statement above you will see
> that the select querie returns only one value hence the
> reason we fetch from the cursor into only one variable.
> */
> FETCH NEXT FROM crsID INTO @nNextID
>
> /*
> Check to see if there was an error in the fetch statement.
> A return value of 0 means that the fetch statement executed
> without any errors i.e. Was successfull.
> */
> IF @@FETCH_STATUS <> 0
> /*
> It is possible that the variable name does not exist
> Attempt to insert the variable name and initialise it to 0
> If it does exist return -1
> */
> BEGIN
> -- We must close the cursor so that it will release the
> record locks
> CLOSE crsID
> DEALLOCATE crsID
> INSERT INTO tblCounters (NAME,COUNTER) VALUES (@vcIDName,1)
> IF (@@ERROR <> 0)
> SELECT @nNextID = @@ERROR * -1
> ELSE
> GOTO SelectQuery
> END
> ELSE -- @@FETCH_STATUS <> 0, Successfully retrieved
> BEGIN
> /*
> At this point the @biNextID contains the existing
> number
> so all we have to do is to increment the value by 1
> */
> UPDATE tblCounters
> SET COUNTER = @nNextID + 1
> WHERE NAME = @vcIDName
> END
> -- We must close the cursor so that it will release the record
> locks
> CLOSE crsID
> DEALLOCATE crsID
> END
>
>
>
> -- Return the value to the calling Stored Procedure
> RETURN @nNextID
>
> GO
>
> CREATE PROCEDURE GetNextID
> /*
> This stored procedure wraps the sdk_func_GetNextID so that it can
> return the
> value in a select query.
> */
> ( -- Start Parameter Declaration
> @vcIDName as VARCHAR(30) -- The name of the variable to extract
> the
> number
> ) -- End Parameter Declaration
> AS
>
> /*
> This BEGIN-END block is where all the variables are declared
> */
> BEGIN -- Variable Declarations start
> -- The next parameter sequence storage variable
> DECLARE @nNextID Numeric
> END -- Variable Declarations end
>
> -- We do not want to return the record count of records affected
> SET NOCOUNT ON
>
> /*
> The way to get a return value from a stored procedure is to
> use the EXECUTE command.
>
> in VB we would call a function like this
> variable = functionname(parameter1, parameter2, ...)
> in SQL we would call a function like this
> execute variable = functionname parameter1, parameter2, ...
> */
> EXECUTE @nNextID = _GetNextID @vcIDName
> /*
> Here we use the select command to return the value.
> This is where it differs from the Return command.
>
> When an application does a select sql statement, this function will
> return
> the value, but if we use a return statement, it does not return the
> value
> but does inform the application that the command was executed
> successfully.
>
>
> Here we say 'ID' is equal to the value.
> What it does is the 'ID' becomes the column name and the variable
> is
> returned
> as the value.
> */
> SELECT 'RETVAL' = @nNextID
> GO
>
> CREATE TABLE [dbo].[CountersTbl] (
> [Name] [varchar] (30) COLLATE Latin1_General_CI_AS NOT NULL ,
> [Counter] [numeric](18, 0) NOT NULL
> ) ON [PRIMARY]
> GO
> --- END OF CODE ---
>
> --- USAGE ---
> exec GetNextId 'NextInvNo'
>
> --- RESULT ---
>
> RETVAL
> ----------
> 1
>
> --
> Thank you
> Mark Hollander
>
>
>
>
>
>
>
> "Uri Dimant" wrote:
>
>> Mark
>> > Select @NextID as vcIDName --not variable
>>
>> Permit me to show some trick (learned from Itzik Ben-Gan) how to get a
>> next
>> value
>>
>> create table seq(val int not null);
>> insert into seq values(0);
>> go
>> select * from seq
>> create proc usp_getseq @val as int output
>> as
>> update seq set @val = val = val + 1;
>> go
>> -- usage
>> declare @i as int;
>> exec usp_getseq @i output;
>> select @i;
>>
>>
>>
>>
>>
>>
>> "Mark Hollander" <MarkHollander@discussions.microsoft.com> wrote in
>> message
>> news:3578A2C0-9E78-4444-B290-B0B1E507E3AC@microsoft.com...
>> >I have written a strored procedure that will return a incremented
>> >number, I
>> > would like to use the value of the variable as the column heading.
>> > CREATE PROCEDURE GetNextID @vcIDName as VARCHAR(30) AS
>> > ... code ...
>> > -- Would like to use something like this
>> > Select @NextID as @vcIDName -- This gives me an error
>> > -- Instead of
>> > Select @NextID as 'RETVAL' -- This works but is not ideal
>> >
>> > Your help will be greatly appreciated
>> > --
>> > Thank you
>> > Mark Hollander
>> > Novice SQL Programmer
>>
>>
>>


Alejandro Mesa

3/28/2007 1:38:00 PM

0

Mark Hollander,

1 - You do not need to use a "cursor" to accomplish that.
2 - The "return" statement inside the stored procedure is mostly used to
indicate if the execution failed or succeeded.
3 - You can use output parameters to exchange data between stored procedures.

CREATE PROCEDURE _GetNextID
@vcIDName as VARCHAR(30),
@nNextID Numeric -- Numeric what? - specify precision and scale numeric(5, 0)
as
....
go

declare @vcIDName VARCHAR(30)
declare @nNextID numeric(5, 0)
declare @rv int
declare @error int

set @vcIDName = 'whatever'

EXEC @rv = _GetNextID @vcIDName, @nNextID OUTPUT
....
go


Error Handling in SQL Server â?? a Background
http://www.sommarskog.se/error-handl...

Implementing Error Handling with Stored Procedures
http://www.sommarskog.se/error-handli...

TRY...CATCH (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms1...

How to Share Data Between Stored Procedures
http://www.sommarskog.se/share...


AMB


"Mark Hollander" wrote:

> Thanks,
> This is fine when working with a single incremental number, The procdure I
> have written can handle has many incremental numbers as the max records allow
> in the sql server that is why I need to be able to use the input variable as
> the column heading, and I cannot seem to get it right. See below for the code:
> ---- START OF CODE ----
> CREATE PROCEDURE _GetNextID
> /*
> This function will extract the number from the tblCounters table and
> increment the value
> by 1. This stored procedure must only be called from another stored
> procedure as it does
> not return any rows in a select query. It is designed only to work
> with other stored
> procedures.
> */
> ( -- Start Parameter Declaration
> @vcIDName as VARCHAR(30) -- The name of the variable to extract the
> number
> ) -- End Parameter Declaration
> AS
>
> /*
> This BEGIN-END block is where all the variables are declared
> */
> BEGIN -- Variable Declarations start
> -- The next id storage variable
> DECLARE @nNextID Numeric
> SELECT @nNextID = 0 -- Initialise the variable to it's default value
>
> /*
> Declare a cursor set that applies the record locking on the
> selected record. We want it to be a forward only cursor as we
> are not performing any backward operations.
> */
> END -- Variable Declarations end
>
> SelectQuery:
> BEGIN -- Select Query Start
> /*
> The name of the variable is stored in the NAME and the
> actual value is stored in the COUNTER field.
> */
> DECLARE crsID CURSOR
> LOCAL
> FORWARD_ONLY
> SCROLL_LOCKS
> FOR
> SELECT COUNTER
> FROM tblCounters
> WHERE [NAME] = @vcIDName
> END
>
> -- We do not want to return the record count of records affected
> SET NOCOUNT ON
>
> -- Start a sequence
> BEGIN
> -- Open the cursor set for processing
> OPEN crsID
> /*
> Fetch the first available record into the variable
> If you examine the select statement above you will see
> that the select querie returns only one value hence the
> reason we fetch from the cursor into only one variable.
> */
> FETCH NEXT FROM crsID INTO @nNextID
>
> /*
> Check to see if there was an error in the fetch statement.
> A return value of 0 means that the fetch statement executed
> without any errors i.e. Was successfull.
> */
> IF @@FETCH_STATUS <> 0
> /*
> It is possible that the variable name does not exist
> Attempt to insert the variable name and initialise it to 0
> If it does exist return -1
> */
> BEGIN
> -- We must close the cursor so that it will release the
> record locks
> CLOSE crsID
> DEALLOCATE crsID
> INSERT INTO tblCounters (NAME,COUNTER) VALUES (@vcIDName,1)
> IF (@@ERROR <> 0)
> SELECT @nNextID = @@ERROR * -1
> ELSE
> GOTO SelectQuery
> END
> ELSE -- @@FETCH_STATUS <> 0, Successfully retrieved
> BEGIN
> /*
> At this point the @biNextID contains the existing number
> so all we have to do is to increment the value by 1
> */
> UPDATE tblCounters
> SET COUNTER = @nNextID + 1
> WHERE NAME = @vcIDName
> END
> -- We must close the cursor so that it will release the record locks
> CLOSE crsID
> DEALLOCATE crsID
> END
>
>
>
> -- Return the value to the calling Stored Procedure
> RETURN @nNextID
>
> GO
>
> CREATE PROCEDURE GetNextID
> /*
> This stored procedure wraps the sdk_func_GetNextID so that it can
> return the
> value in a select query.
> */
> ( -- Start Parameter Declaration
> @vcIDName as VARCHAR(30) -- The name of the variable to extract the
> number
> ) -- End Parameter Declaration
> AS
>
> /*
> This BEGIN-END block is where all the variables are declared
> */
> BEGIN -- Variable Declarations start
> -- The next parameter sequence storage variable
> DECLARE @nNextID Numeric
> END -- Variable Declarations end
>
> -- We do not want to return the record count of records affected
> SET NOCOUNT ON
>
> /*
> The way to get a return value from a stored procedure is to
> use the EXECUTE command.
>
> in VB we would call a function like this
> variable = functionname(parameter1, parameter2, ...)
> in SQL we would call a function like this
> execute variable = functionname parameter1, parameter2, ...
> */
> EXECUTE @nNextID = _GetNextID @vcIDName
> /*
> Here we use the select command to return the value.
> This is where it differs from the Return command.
>
> When an application does a select sql statement, this function will
> return
> the value, but if we use a return statement, it does not return the
> value
> but does inform the application that the command was executed
> successfully.
>
>
> Here we say 'ID' is equal to the value.
> What it does is the 'ID' becomes the column name and the variable is
> returned
> as the value.
> */
> SELECT 'RETVAL' = @nNextID
> GO
>
> CREATE TABLE [dbo].[CountersTbl] (
> [Name] [varchar] (30) COLLATE Latin1_General_CI_AS NOT NULL ,
> [Counter] [numeric](18, 0) NOT NULL
> ) ON [PRIMARY]
> GO
> --- END OF CODE ---
>
> --- USAGE ---
> exec GetNextId 'NextInvNo'
>
> --- RESULT ---
>
> RETVAL
> ----------
> 1
>
> --
> Thank you
> Mark Hollander
>
>
>
>
>
>
>
> "Uri Dimant" wrote:
>
> > Mark
> > > Select @NextID as vcIDName --not variable
> >
> > Permit me to show some trick (learned from Itzik Ben-Gan) how to get a next
> > value
> >
> > create table seq(val int not null);
> > insert into seq values(0);
> > go
> > select * from seq
> > create proc usp_getseq @val as int output
> > as
> > update seq set @val = val = val + 1;
> > go
> > -- usage
> > declare @i as int;
> > exec usp_getseq @i output;
> > select @i;
> >
> >
> >
> >
> >
> >
> > "Mark Hollander" <MarkHollander@discussions.microsoft.com> wrote in message
> > news:3578A2C0-9E78-4444-B290-B0B1E507E3AC@microsoft.com...
> > >I have written a strored procedure that will return a incremented number, I
> > > would like to use the value of the variable as the column heading.
> > > CREATE PROCEDURE GetNextID @vcIDName as VARCHAR(30) AS
> > > ... code ...
> > > -- Would like to use something like this
> > > Select @NextID as @vcIDName -- This gives me an error
> > > -- Instead of
> > > Select @NextID as 'RETVAL' -- This works but is not ideal
> > >
> > > Your help will be greatly appreciated
> > > --
> > > Thank you
> > > Mark Hollander
> > > Novice SQL Programmer
> >
> >
> >

Mark Hollander

3/28/2007 1:42:00 PM

0

Hi Uri,

I get the value with no problem, and returns the incremented number every
time, but I would like to change the 'Title' or 'Column Heading' to reflect
the name of the incremented number instead of RETVAL as I have had to hard
code the title.
E.g.

Select @NextID as 'RETVAL' -- In the stored procedure, I can get this to
work

exec GetNextID 'InvNo'

gives me the following result
TITLE | RETVAL
---------|----------
VALUE | 2

You will see if you copy the code below and run it in the "SQL Query
Analyzer" the value does increment every time, but in the grid the column
title is RETVAL and I would like to change it to the name of the Incremental
value.
E.G.
Select @NextID as @IncrementName -- In the stored procedure , but I cannot
get it to work

exec GetNextID 'InvNo'
will give me the following result
TITLE | InvNo
---------|----------
VALUE | 2


--
Thank you
Mark Hollander


"Uri Dimant" wrote:

> Mark
>
> I think in order to resturn value you need define a parameter OUTPUT. See
> how to use it from my post or from BOL
>
>
>
> "Mark Hollander" <MarkHollander@discussions.microsoft.com> wrote in message
> news:E1C51C24-A723-4BF8-B27A-FF078A69F01C@microsoft.com...
> > Thanks,
> > This is fine when working with a single incremental number, The procdure I
> > have written can handle has many incremental numbers as the max records
> > allow
> > in the sql server that is why I need to be able to use the input variable
> > as
> > the column heading, and I cannot seem to get it right. See below for the
> > code:
> > ---- START OF CODE ----
> > CREATE PROCEDURE _GetNextID
> > /*
> > This function will extract the number from the tblCounters table
> > and
> > increment the value
> > by 1. This stored procedure must only be called from another stored
> > procedure as it does
> > not return any rows in a select query. It is designed only to work
> > with other stored
> > procedures.
> > */
> > ( -- Start Parameter Declaration
> > @vcIDName as VARCHAR(30) -- The name of the variable to extract the
> > number
> > ) -- End Parameter Declaration
> > AS
> >
> > /*
> > This BEGIN-END block is where all the variables are declared
> > */
> > BEGIN -- Variable Declarations start
> > -- The next id storage variable
> > DECLARE @nNextID Numeric
> > SELECT @nNextID = 0 -- Initialise the variable to it's default
> > value
> >
> > /*
> > Declare a cursor set that applies the record locking on the
> > selected record. We want it to be a forward only cursor as we
> > are not performing any backward operations.
> > */
> > END -- Variable Declarations end
> >
> > SelectQuery:
> > BEGIN -- Select Query Start
> > /*
> > The name of the variable is stored in the NAME and the
> > actual value is stored in the COUNTER field.
> > */
> > DECLARE crsID CURSOR
> > LOCAL
> > FORWARD_ONLY
> > SCROLL_LOCKS
> > FOR
> > SELECT COUNTER
> > FROM tblCounters
> > WHERE [NAME] = @vcIDName
> > END
> >
> > -- We do not want to return the record count of records affected
> > SET NOCOUNT ON
> >
> > -- Start a sequence
> > BEGIN
> > -- Open the cursor set for processing
> > OPEN crsID
> > /*
> > Fetch the first available record into the variable
> > If you examine the select statement above you will see
> > that the select querie returns only one value hence the
> > reason we fetch from the cursor into only one variable.
> > */
> > FETCH NEXT FROM crsID INTO @nNextID
> >
> > /*
> > Check to see if there was an error in the fetch statement.
> > A return value of 0 means that the fetch statement executed
> > without any errors i.e. Was successfull.
> > */
> > IF @@FETCH_STATUS <> 0
> > /*
> > It is possible that the variable name does not exist
> > Attempt to insert the variable name and initialise it to 0
> > If it does exist return -1
> > */
> > BEGIN
> > -- We must close the cursor so that it will release the
> > record locks
> > CLOSE crsID
> > DEALLOCATE crsID
> > INSERT INTO tblCounters (NAME,COUNTER) VALUES (@vcIDName,1)
> > IF (@@ERROR <> 0)
> > SELECT @nNextID = @@ERROR * -1
> > ELSE
> > GOTO SelectQuery
> > END
> > ELSE -- @@FETCH_STATUS <> 0, Successfully retrieved
> > BEGIN
> > /*
> > At this point the @biNextID contains the existing
> > number
> > so all we have to do is to increment the value by 1
> > */
> > UPDATE tblCounters
> > SET COUNTER = @nNextID + 1
> > WHERE NAME = @vcIDName
> > END
> > -- We must close the cursor so that it will release the record
> > locks
> > CLOSE crsID
> > DEALLOCATE crsID
> > END
> >
> >
> >
> > -- Return the value to the calling Stored Procedure
> > RETURN @nNextID
> >
> > GO
> >
> > CREATE PROCEDURE GetNextID
> > /*
> > This stored procedure wraps the sdk_func_GetNextID so that it can
> > return the
> > value in a select query.
> > */
> > ( -- Start Parameter Declaration
> > @vcIDName as VARCHAR(30) -- The name of the variable to extract
> > the
> > number
> > ) -- End Parameter Declaration
> > AS
> >
> > /*
> > This BEGIN-END block is where all the variables are declared
> > */
> > BEGIN -- Variable Declarations start
> > -- The next parameter sequence storage variable
> > DECLARE @nNextID Numeric
> > END -- Variable Declarations end
> >
> > -- We do not want to return the record count of records affected
> > SET NOCOUNT ON
> >
> > /*
> > The way to get a return value from a stored procedure is to
> > use the EXECUTE command.
> >
> > in VB we would call a function like this
> > variable = functionname(parameter1, parameter2, ...)
> > in SQL we would call a function like this
> > execute variable = functionname parameter1, parameter2, ...
> > */
> > EXECUTE @nNextID = _GetNextID @vcIDName
> > /*
> > Here we use the select command to return the value.
> > This is where it differs from the Return command.
> >
> > When an application does a select sql statement, this function will
> > return
> > the value, but if we use a return statement, it does not return the
> > value
> > but does inform the application that the command was executed
> > successfully.
> >
> >
> > Here we say 'ID' is equal to the value.
> > What it does is the 'ID' becomes the column name and the variable
> > is
> > returned
> > as the value.
> > */
> > SELECT 'RETVAL' = @nNextID
> > GO
> >
> > CREATE TABLE [dbo].[CountersTbl] (
> > [Name] [varchar] (30) COLLATE Latin1_General_CI_AS NOT NULL ,
> > [Counter] [numeric](18, 0) NOT NULL
> > ) ON [PRIMARY]
> > GO
> > --- END OF CODE ---
> >
> > --- USAGE ---
> > exec GetNextId 'NextInvNo'
> >
> > --- RESULT ---
> >
> > RETVAL
> > ----------
> > 1
> >
> > --
> > Thank you
> > Mark Hollander
> >
> >
> >
> >
> >
> >
> >
> > "Uri Dimant" wrote:
> >
> >> Mark
> >> > Select @NextID as vcIDName --not variable
> >>
> >> Permit me to show some trick (learned from Itzik Ben-Gan) how to get a
> >> next
> >> value
> >>
> >> create table seq(val int not null);
> >> insert into seq values(0);
> >> go
> >> select * from seq
> >> create proc usp_getseq @val as int output
> >> as
> >> update seq set @val = val = val + 1;
> >> go
> >> -- usage
> >> declare @i as int;
> >> exec usp_getseq @i output;
> >> select @i;
> >>
> >>
> >>
> >>
> >>
> >>
> >> "Mark Hollander" <MarkHollander@discussions.microsoft.com> wrote in
> >> message
> >> news:3578A2C0-9E78-4444-B290-B0B1E507E3AC@microsoft.com...
> >> >I have written a strored procedure that will return a incremented
> >> >number, I
> >> > would like to use the value of the variable as the column heading.
> >> > CREATE PROCEDURE GetNextID @vcIDName as VARCHAR(30) AS
> >> > ... code ...
> >> > -- Would like to use something like this
> >> > Select @NextID as @vcIDName -- This gives me an error
> >> > -- Instead of
> >> > Select @NextID as 'RETVAL' -- This works but is not ideal
> >> >
> >> > Your help will be greatly appreciated
> >> > --
> >> > Thank you
> >> > Mark Hollander
> >> > Novice SQL Programmer
> >>
> >>
> >>
>
>
>

Mark Hollander

3/28/2007 2:00:00 PM

0

Hi Alejandro,

Thank you for your help, I will rectify the coding issues that you have
highlighted,

I still need to have my originla question answered though:

I would like to change the 'Title' or 'Column Heading' to reflect
the name of the incremented number instead of RETVAL as I have had to hard
code the title.
E.g.

Select @NextID as 'RETVAL' -- In the stored procedure, I can get this to
work

exec GetNextID 'InvNo'

gives me the following result
TITLE | RETVAL
---------|----------
VALUE | 2

You will see if you copy the code below and run it in the "SQL Query
Analyzer" the value does increment every time, but in the grid the column
title is RETVAL and I would like to change it to the name of the Incremental
value.
E.G.
Select @NextID as @IncrementName -- In the stored procedure , but I cannot
get it to work

exec GetNextID 'InvNo'
will give me the following result
TITLE | InvNo
---------|----------
VALUE | 2



--
Thank you
Mark Hollander


"Alejandro Mesa" wrote:

> Mark Hollander,
>
> 1 - You do not need to use a "cursor" to accomplish that.
> 2 - The "return" statement inside the stored procedure is mostly used to
> indicate if the execution failed or succeeded.
> 3 - You can use output parameters to exchange data between stored procedures.
>
> CREATE PROCEDURE _GetNextID
> @vcIDName as VARCHAR(30),
> @nNextID Numeric -- Numeric what? - specify precision and scale numeric(5, 0)
> as
> ...
> go
>
> declare @vcIDName VARCHAR(30)
> declare @nNextID numeric(5, 0)
> declare @rv int
> declare @error int
>
> set @vcIDName = 'whatever'
>
> EXEC @rv = _GetNextID @vcIDName, @nNextID OUTPUT
> ...
> go
>
>
> Error Handling in SQL Server â?? a Background
> http://www.sommarskog.se/error-handl...
>
> Implementing Error Handling with Stored Procedures
> http://www.sommarskog.se/error-handli...
>
> TRY...CATCH (Transact-SQL)
> http://msdn2.microsoft.com/en-us/library/ms1...
>
> How to Share Data Between Stored Procedures
> http://www.sommarskog.se/share...
>
>
> AMB
>
>
> "Mark Hollander" wrote:
>
> > Thanks,
> > This is fine when working with a single incremental number, The procdure I
> > have written can handle has many incremental numbers as the max records allow
> > in the sql server that is why I need to be able to use the input variable as
> > the column heading, and I cannot seem to get it right. See below for the code:
> > ---- START OF CODE ----
> > CREATE PROCEDURE _GetNextID
> > /*
> > This function will extract the number from the tblCounters table and
> > increment the value
> > by 1. This stored procedure must only be called from another stored
> > procedure as it does
> > not return any rows in a select query. It is designed only to work
> > with other stored
> > procedures.
> > */
> > ( -- Start Parameter Declaration
> > @vcIDName as VARCHAR(30) -- The name of the variable to extract the
> > number
> > ) -- End Parameter Declaration
> > AS
> >
> > /*
> > This BEGIN-END block is where all the variables are declared
> > */
> > BEGIN -- Variable Declarations start
> > -- The next id storage variable
> > DECLARE @nNextID Numeric
> > SELECT @nNextID = 0 -- Initialise the variable to it's default value
> >
> > /*
> > Declare a cursor set that applies the record locking on the
> > selected record. We want it to be a forward only cursor as we
> > are not performing any backward operations.
> > */
> > END -- Variable Declarations end
> >
> > SelectQuery:
> > BEGIN -- Select Query Start
> > /*
> > The name of the variable is stored in the NAME and the
> > actual value is stored in the COUNTER field.
> > */
> > DECLARE crsID CURSOR
> > LOCAL
> > FORWARD_ONLY
> > SCROLL_LOCKS
> > FOR
> > SELECT COUNTER
> > FROM tblCounters
> > WHERE [NAME] = @vcIDName
> > END
> >
> > -- We do not want to return the record count of records affected
> > SET NOCOUNT ON
> >
> > -- Start a sequence
> > BEGIN
> > -- Open the cursor set for processing
> > OPEN crsID
> > /*
> > Fetch the first available record into the variable
> > If you examine the select statement above you will see
> > that the select querie returns only one value hence the
> > reason we fetch from the cursor into only one variable.
> > */
> > FETCH NEXT FROM crsID INTO @nNextID
> >
> > /*
> > Check to see if there was an error in the fetch statement.
> > A return value of 0 means that the fetch statement executed
> > without any errors i.e. Was successfull.
> > */
> > IF @@FETCH_STATUS <> 0
> > /*
> > It is possible that the variable name does not exist
> > Attempt to insert the variable name and initialise it to 0
> > If it does exist return -1
> > */
> > BEGIN
> > -- We must close the cursor so that it will release the
> > record locks
> > CLOSE crsID
> > DEALLOCATE crsID
> > INSERT INTO tblCounters (NAME,COUNTER) VALUES (@vcIDName,1)
> > IF (@@ERROR <> 0)
> > SELECT @nNextID = @@ERROR * -1
> > ELSE
> > GOTO SelectQuery
> > END
> > ELSE -- @@FETCH_STATUS <> 0, Successfully retrieved
> > BEGIN
> > /*
> > At this point the @biNextID contains the existing number
> > so all we have to do is to increment the value by 1
> > */
> > UPDATE tblCounters
> > SET COUNTER = @nNextID + 1
> > WHERE NAME = @vcIDName
> > END
> > -- We must close the cursor so that it will release the record locks
> > CLOSE crsID
> > DEALLOCATE crsID
> > END
> >
> >
> >
> > -- Return the value to the calling Stored Procedure
> > RETURN @nNextID
> >
> > GO
> >
> > CREATE PROCEDURE GetNextID
> > /*
> > This stored procedure wraps the sdk_func_GetNextID so that it can
> > return the
> > value in a select query.
> > */
> > ( -- Start Parameter Declaration
> > @vcIDName as VARCHAR(30) -- The name of the variable to extract the
> > number
> > ) -- End Parameter Declaration
> > AS
> >
> > /*
> > This BEGIN-END block is where all the variables are declared
> > */
> > BEGIN -- Variable Declarations start
> > -- The next parameter sequence storage variable
> > DECLARE @nNextID Numeric
> > END -- Variable Declarations end
> >
> > -- We do not want to return the record count of records affected
> > SET NOCOUNT ON
> >
> > /*
> > The way to get a return value from a stored procedure is to
> > use the EXECUTE command.
> >
> > in VB we would call a function like this
> > variable = functionname(parameter1, parameter2, ...)
> > in SQL we would call a function like this
> > execute variable = functionname parameter1, parameter2, ...
> > */
> > EXECUTE @nNextID = _GetNextID @vcIDName
> > /*
> > Here we use the select command to return the value.
> > This is where it differs from the Return command.
> >
> > When an application does a select sql statement, this function will
> > return
> > the value, but if we use a return statement, it does not return the
> > value
> > but does inform the application that the command was executed
> > successfully.
> >
> >
> > Here we say 'ID' is equal to the value.
> > What it does is the 'ID' becomes the column name and the variable is
> > returned
> > as the value.
> > */
> > SELECT 'RETVAL' = @nNextID
> > GO
> >
> > CREATE TABLE [dbo].[CountersTbl] (
> > [Name] [varchar] (30) COLLATE Latin1_General_CI_AS NOT NULL ,
> > [Counter] [numeric](18, 0) NOT NULL
> > ) ON [PRIMARY]
> > GO
> > --- END OF CODE ---
> >
> > --- USAGE ---
> > exec GetNextId 'NextInvNo'
> >
> > --- RESULT ---
> >
> > RETVAL
> > ----------
> > 1
> >
> > --
> > Thank you
> > Mark Hollander
> >
> >
> >
> >
> >
> >
> >
> > "Uri Dimant" wrote:
> >
> > > Mark
> > > > Select @NextID as vcIDName --not variable
> > >
> > > Permit me to show some trick (learned from Itzik Ben-Gan) how to get a next
> > > value
> > >
> > > create table seq(val int not null);
> > > insert into seq values(0);
> > > go
> > > select * from seq
> > > create proc usp_getseq @val as int output
> > > as
> > > update seq set @val = val = val + 1;
> > > go
> > > -- usage
> > > declare @i as int;
> > > exec usp_getseq @i output;
> > > select @i;
> > >
> > >
> > >
> > >
> > >
> > >
> > > "Mark Hollander" <MarkHollander@discussions.microsoft.com> wrote in message
> > > news:3578A2C0-9E78-4444-B290-B0B1E507E3AC@microsoft.com...
> > > >I have written a strored procedure that will return a incremented number, I
> > > > would like to use the value of the variable as the column heading.
> > > > CREATE PROCEDURE GetNextID @vcIDName as VARCHAR(30) AS
> > > > ... code ...
> > > > -- Would like to use something like this
> > > > Select @NextID as @vcIDName -- This gives me an error
> > > > -- Instead of
> > > > Select @NextID as 'RETVAL' -- This works but is not ideal
> > > >
> > > > Your help will be greatly appreciated
> > > > --
> > > > Thank you
> > > > Mark Hollander
> > > > Novice SQL Programmer
> > >
> > >
> > >

Mark Hollander

3/28/2007 2:02:00 PM

0

My Apologies,

I Forgot to mention that I am using SQL2000 at this moment
--
Thank you
Mark Hollander


"Alejandro Mesa" wrote:

> Mark Hollander,
>
> 1 - You do not need to use a "cursor" to accomplish that.
> 2 - The "return" statement inside the stored procedure is mostly used to
> indicate if the execution failed or succeeded.
> 3 - You can use output parameters to exchange data between stored procedures.
>
> CREATE PROCEDURE _GetNextID
> @vcIDName as VARCHAR(30),
> @nNextID Numeric -- Numeric what? - specify precision and scale numeric(5, 0)
> as
> ...
> go
>
> declare @vcIDName VARCHAR(30)
> declare @nNextID numeric(5, 0)
> declare @rv int
> declare @error int
>
> set @vcIDName = 'whatever'
>
> EXEC @rv = _GetNextID @vcIDName, @nNextID OUTPUT
> ...
> go
>
>
> Error Handling in SQL Server â?? a Background
> http://www.sommarskog.se/error-handl...
>
> Implementing Error Handling with Stored Procedures
> http://www.sommarskog.se/error-handli...
>
> TRY...CATCH (Transact-SQL)
> http://msdn2.microsoft.com/en-us/library/ms1...
>
> How to Share Data Between Stored Procedures
> http://www.sommarskog.se/share...
>
>
> AMB
>
>
> "Mark Hollander" wrote:
>
> > Thanks,
> > This is fine when working with a single incremental number, The procdure I
> > have written can handle has many incremental numbers as the max records allow
> > in the sql server that is why I need to be able to use the input variable as
> > the column heading, and I cannot seem to get it right. See below for the code:
> > ---- START OF CODE ----
> > CREATE PROCEDURE _GetNextID
> > /*
> > This function will extract the number from the tblCounters table and
> > increment the value
> > by 1. This stored procedure must only be called from another stored
> > procedure as it does
> > not return any rows in a select query. It is designed only to work
> > with other stored
> > procedures.
> > */
> > ( -- Start Parameter Declaration
> > @vcIDName as VARCHAR(30) -- The name of the variable to extract the
> > number
> > ) -- End Parameter Declaration
> > AS
> >
> > /*
> > This BEGIN-END block is where all the variables are declared
> > */
> > BEGIN -- Variable Declarations start
> > -- The next id storage variable
> > DECLARE @nNextID Numeric
> > SELECT @nNextID = 0 -- Initialise the variable to it's default value
> >
> > /*
> > Declare a cursor set that applies the record locking on the
> > selected record. We want it to be a forward only cursor as we
> > are not performing any backward operations.
> > */
> > END -- Variable Declarations end
> >
> > SelectQuery:
> > BEGIN -- Select Query Start
> > /*
> > The name of the variable is stored in the NAME and the
> > actual value is stored in the COUNTER field.
> > */
> > DECLARE crsID CURSOR
> > LOCAL
> > FORWARD_ONLY
> > SCROLL_LOCKS
> > FOR
> > SELECT COUNTER
> > FROM tblCounters
> > WHERE [NAME] = @vcIDName
> > END
> >
> > -- We do not want to return the record count of records affected
> > SET NOCOUNT ON
> >
> > -- Start a sequence
> > BEGIN
> > -- Open the cursor set for processing
> > OPEN crsID
> > /*
> > Fetch the first available record into the variable
> > If you examine the select statement above you will see
> > that the select querie returns only one value hence the
> > reason we fetch from the cursor into only one variable.
> > */
> > FETCH NEXT FROM crsID INTO @nNextID
> >
> > /*
> > Check to see if there was an error in the fetch statement.
> > A return value of 0 means that the fetch statement executed
> > without any errors i.e. Was successfull.
> > */
> > IF @@FETCH_STATUS <> 0
> > /*
> > It is possible that the variable name does not exist
> > Attempt to insert the variable name and initialise it to 0
> > If it does exist return -1
> > */
> > BEGIN
> > -- We must close the cursor so that it will release the
> > record locks
> > CLOSE crsID
> > DEALLOCATE crsID
> > INSERT INTO tblCounters (NAME,COUNTER) VALUES (@vcIDName,1)
> > IF (@@ERROR <> 0)
> > SELECT @nNextID = @@ERROR * -1
> > ELSE
> > GOTO SelectQuery
> > END
> > ELSE -- @@FETCH_STATUS <> 0, Successfully retrieved
> > BEGIN
> > /*
> > At this point the @biNextID contains the existing number
> > so all we have to do is to increment the value by 1
> > */
> > UPDATE tblCounters
> > SET COUNTER = @nNextID + 1
> > WHERE NAME = @vcIDName
> > END
> > -- We must close the cursor so that it will release the record locks
> > CLOSE crsID
> > DEALLOCATE crsID
> > END
> >
> >
> >
> > -- Return the value to the calling Stored Procedure
> > RETURN @nNextID
> >
> > GO
> >
> > CREATE PROCEDURE GetNextID
> > /*
> > This stored procedure wraps the sdk_func_GetNextID so that it can
> > return the
> > value in a select query.
> > */
> > ( -- Start Parameter Declaration
> > @vcIDName as VARCHAR(30) -- The name of the variable to extract the
> > number
> > ) -- End Parameter Declaration
> > AS
> >
> > /*
> > This BEGIN-END block is where all the variables are declared
> > */
> > BEGIN -- Variable Declarations start
> > -- The next parameter sequence storage variable
> > DECLARE @nNextID Numeric
> > END -- Variable Declarations end
> >
> > -- We do not want to return the record count of records affected
> > SET NOCOUNT ON
> >
> > /*
> > The way to get a return value from a stored procedure is to
> > use the EXECUTE command.
> >
> > in VB we would call a function like this
> > variable = functionname(parameter1, parameter2, ...)
> > in SQL we would call a function like this
> > execute variable = functionname parameter1, parameter2, ...
> > */
> > EXECUTE @nNextID = _GetNextID @vcIDName
> > /*
> > Here we use the select command to return the value.
> > This is where it differs from the Return command.
> >
> > When an application does a select sql statement, this function will
> > return
> > the value, but if we use a return statement, it does not return the
> > value
> > but does inform the application that the command was executed
> > successfully.
> >
> >
> > Here we say 'ID' is equal to the value.
> > What it does is the 'ID' becomes the column name and the variable is
> > returned
> > as the value.
> > */
> > SELECT 'RETVAL' = @nNextID
> > GO
> >
> > CREATE TABLE [dbo].[CountersTbl] (
> > [Name] [varchar] (30) COLLATE Latin1_General_CI_AS NOT NULL ,
> > [Counter] [numeric](18, 0) NOT NULL
> > ) ON [PRIMARY]
> > GO
> > --- END OF CODE ---
> >
> > --- USAGE ---
> > exec GetNextId 'NextInvNo'
> >
> > --- RESULT ---
> >
> > RETVAL
> > ----------
> > 1
> >
> > --
> > Thank you
> > Mark Hollander
> >
> >
> >
> >
> >
> >
> >
> > "Uri Dimant" wrote:
> >
> > > Mark
> > > > Select @NextID as vcIDName --not variable
> > >
> > > Permit me to show some trick (learned from Itzik Ben-Gan) how to get a next
> > > value
> > >
> > > create table seq(val int not null);
> > > insert into seq values(0);
> > > go
> > > select * from seq
> > > create proc usp_getseq @val as int output
> > > as
> > > update seq set @val = val = val + 1;
> > > go
> > > -- usage
> > > declare @i as int;
> > > exec usp_getseq @i output;
> > > select @i;
> > >
> > >
> > >
> > >
> > >
> > >
> > > "Mark Hollander" <MarkHollander@discussions.microsoft.com> wrote in message
> > > news:3578A2C0-9E78-4444-B290-B0B1E507E3AC@microsoft.com...
> > > >I have written a strored procedure that will return a incremented number, I
> > > > would like to use the value of the variable as the column heading.
> > > > CREATE PROCEDURE GetNextID @vcIDName as VARCHAR(30) AS
> > > > ... code ...
> > > > -- Would like to use something like this
> > > > Select @NextID as @vcIDName -- This gives me an error
> > > > -- Instead of
> > > > Select @NextID as 'RETVAL' -- This works but is not ideal
> > > >
> > > > Your help will be greatly appreciated
> > > > --
> > > > Thank you
> > > > Mark Hollander
> > > > Novice SQL Programmer
> > >
> > >
> > >

Alejandro Mesa

3/28/2007 2:26:00 PM

0

Mark Hollander,

I guess you will need to use dynamic sql.

declare @sql nvarchar(4000)

set @sql = N'select @n as [' + @vcIDName + ']'

exec sp_executesql @sql, N'@n numeric(5, 0)', @nNextID
go

The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynami...


AMB

"Mark Hollander" wrote:

> Hi Alejandro,
>
> Thank you for your help, I will rectify the coding issues that you have
> highlighted,
>
> I still need to have my originla question answered though:
>
> I would like to change the 'Title' or 'Column Heading' to reflect
> the name of the incremented number instead of RETVAL as I have had to hard
> code the title.
> E.g.
>
> Select @NextID as 'RETVAL' -- In the stored procedure, I can get this to
> work
>
> exec GetNextID 'InvNo'
>
> gives me the following result
> TITLE | RETVAL
> ---------|----------
> VALUE | 2
>
> You will see if you copy the code below and run it in the "SQL Query
> Analyzer" the value does increment every time, but in the grid the column
> title is RETVAL and I would like to change it to the name of the Incremental
> value.
> E.G.
> Select @NextID as @IncrementName -- In the stored procedure , but I cannot
> get it to work
>
> exec GetNextID 'InvNo'
> will give me the following result
> TITLE | InvNo
> ---------|----------
> VALUE | 2
>
>
>
> --
> Thank you
> Mark Hollander
>
>
> "Alejandro Mesa" wrote:
>
> > Mark Hollander,
> >
> > 1 - You do not need to use a "cursor" to accomplish that.
> > 2 - The "return" statement inside the stored procedure is mostly used to
> > indicate if the execution failed or succeeded.
> > 3 - You can use output parameters to exchange data between stored procedures.
> >
> > CREATE PROCEDURE _GetNextID
> > @vcIDName as VARCHAR(30),
> > @nNextID Numeric -- Numeric what? - specify precision and scale numeric(5, 0)
> > as
> > ...
> > go
> >
> > declare @vcIDName VARCHAR(30)
> > declare @nNextID numeric(5, 0)
> > declare @rv int
> > declare @error int
> >
> > set @vcIDName = 'whatever'
> >
> > EXEC @rv = _GetNextID @vcIDName, @nNextID OUTPUT
> > ...
> > go
> >
> >
> > Error Handling in SQL Server â?? a Background
> > http://www.sommarskog.se/error-handl...
> >
> > Implementing Error Handling with Stored Procedures
> > http://www.sommarskog.se/error-handli...
> >
> > TRY...CATCH (Transact-SQL)
> > http://msdn2.microsoft.com/en-us/library/ms1...
> >
> > How to Share Data Between Stored Procedures
> > http://www.sommarskog.se/share...
> >
> >
> > AMB
> >
> >
> > "Mark Hollander" wrote:
> >
> > > Thanks,
> > > This is fine when working with a single incremental number, The procdure I
> > > have written can handle has many incremental numbers as the max records allow
> > > in the sql server that is why I need to be able to use the input variable as
> > > the column heading, and I cannot seem to get it right. See below for the code:
> > > ---- START OF CODE ----
> > > CREATE PROCEDURE _GetNextID
> > > /*
> > > This function will extract the number from the tblCounters table and
> > > increment the value
> > > by 1. This stored procedure must only be called from another stored
> > > procedure as it does
> > > not return any rows in a select query. It is designed only to work
> > > with other stored
> > > procedures.
> > > */
> > > ( -- Start Parameter Declaration
> > > @vcIDName as VARCHAR(30) -- The name of the variable to extract the
> > > number
> > > ) -- End Parameter Declaration
> > > AS
> > >
> > > /*
> > > This BEGIN-END block is where all the variables are declared
> > > */
> > > BEGIN -- Variable Declarations start
> > > -- The next id storage variable
> > > DECLARE @nNextID Numeric
> > > SELECT @nNextID = 0 -- Initialise the variable to it's default value
> > >
> > > /*
> > > Declare a cursor set that applies the record locking on the
> > > selected record. We want it to be a forward only cursor as we
> > > are not performing any backward operations.
> > > */
> > > END -- Variable Declarations end
> > >
> > > SelectQuery:
> > > BEGIN -- Select Query Start
> > > /*
> > > The name of the variable is stored in the NAME and the
> > > actual value is stored in the COUNTER field.
> > > */
> > > DECLARE crsID CURSOR
> > > LOCAL
> > > FORWARD_ONLY
> > > SCROLL_LOCKS
> > > FOR
> > > SELECT COUNTER
> > > FROM tblCounters
> > > WHERE [NAME] = @vcIDName
> > > END
> > >
> > > -- We do not want to return the record count of records affected
> > > SET NOCOUNT ON
> > >
> > > -- Start a sequence
> > > BEGIN
> > > -- Open the cursor set for processing
> > > OPEN crsID
> > > /*
> > > Fetch the first available record into the variable
> > > If you examine the select statement above you will see
> > > that the select querie returns only one value hence the
> > > reason we fetch from the cursor into only one variable.
> > > */
> > > FETCH NEXT FROM crsID INTO @nNextID
> > >
> > > /*
> > > Check to see if there was an error in the fetch statement.
> > > A return value of 0 means that the fetch statement executed
> > > without any errors i.e. Was successfull.
> > > */
> > > IF @@FETCH_STATUS <> 0
> > > /*
> > > It is possible that the variable name does not exist
> > > Attempt to insert the variable name and initialise it to 0
> > > If it does exist return -1
> > > */
> > > BEGIN
> > > -- We must close the cursor so that it will release the
> > > record locks
> > > CLOSE crsID
> > > DEALLOCATE crsID
> > > INSERT INTO tblCounters (NAME,COUNTER) VALUES (@vcIDName,1)
> > > IF (@@ERROR <> 0)
> > > SELECT @nNextID = @@ERROR * -1
> > > ELSE
> > > GOTO SelectQuery
> > > END
> > > ELSE -- @@FETCH_STATUS <> 0, Successfully retrieved
> > > BEGIN
> > > /*
> > > At this point the @biNextID contains the existing number
> > > so all we have to do is to increment the value by 1
> > > */
> > > UPDATE tblCounters
> > > SET COUNTER = @nNextID + 1
> > > WHERE NAME = @vcIDName
> > > END
> > > -- We must close the cursor so that it will release the record locks
> > > CLOSE crsID
> > > DEALLOCATE crsID
> > > END
> > >
> > >
> > >
> > > -- Return the value to the calling Stored Procedure
> > > RETURN @nNextID
> > >
> > > GO
> > >
> > > CREATE PROCEDURE GetNextID
> > > /*
> > > This stored procedure wraps the sdk_func_GetNextID so that it can
> > > return the
> > > value in a select query.
> > > */
> > > ( -- Start Parameter Declaration
> > > @vcIDName as VARCHAR(30) -- The name of the variable to extract the
> > > number
> > > ) -- End Parameter Declaration
> > > AS
> > >
> > > /*
> > > This BEGIN-END block is where all the variables are declared
> > > */
> > > BEGIN -- Variable Declarations start
> > > -- The next parameter sequence storage variable
> > > DECLARE @nNextID Numeric
> > > END -- Variable Declarations end
> > >
> > > -- We do not want to return the record count of records affected
> > > SET NOCOUNT ON
> > >
> > > /*
> > > The way to get a return value from a stored procedure is to
> > > use the EXECUTE command.
> > >
> > > in VB we would call a function like this
> > > variable = functionname(parameter1, parameter2, ...)
> > > in SQL we would call a function like this
> > > execute variable = functionname parameter1, parameter2, ...
> > > */
> > > EXECUTE @nNextID = _GetNextID @vcIDName
> > > /*
> > > Here we use the select command to return the value.
> > > This is where it differs from the Return command.
> > >
> > > When an application does a select sql statement, this function will
> > > return
> > > the value, but if we use a return statement, it does not return the
> > > value
> > > but does inform the application that the command was executed
> > > successfully.
> > >
> > >
> > > Here we say 'ID' is equal to the value.
> > > What it does is the 'ID' becomes the column name and the variable is
> > > returned
> > > as the value.
> > > */
> > > SELECT 'RETVAL' = @nNextID
> > > GO
> > >
> > > CREATE TABLE [dbo].[CountersTbl] (
> > > [Name] [varchar] (30) COLLATE Latin1_General_CI_AS NOT NULL ,
> > > [Counter] [numeric](18, 0) NOT NULL
> > > ) ON [PRIMARY]
> > > GO
> > > --- END OF CODE ---
> > >
> > > --- USAGE ---
> > > exec GetNextId 'NextInvNo'
> > >
> > > --- RESULT ---
> > >
> > > RETVAL
> > > ----------
> > > 1
> > >
> > > --
> > > Thank you
> > > Mark Hollander
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > > "Uri Dimant" wrote:
> > >
> > > > Mark
> > > > > Select @NextID as vcIDName --not variable
> > > >
> > > > Permit me to show some trick (learned from Itzik Ben-Gan) how to get a next
> > > > value
> > > >
> > > > create table seq(val int not null);
> > > > insert into seq values(0);
> > > > go
> > > > select * from seq
> > > > create proc usp_getseq @val as int output
> > > > as
> > > > update seq set @val = val = val + 1;
> > > > go
> > > > -- usage
> > > > declare @i as int;
> > > > exec usp_getseq @i output;
> > > > select @i;