Local variables in stored proc

How do you declare and then SELECT a value for a local variable within
stored procedure, increment the value and then use in an Insert
statement? Thanks

Any sites that explain this syntax for SQL Server 2000? Thanks
hals_left

CREATE PROCEDURE [dbo].[InsertQualUnit]
@.QualRef tinyint,
@.UnitRef tinyint,
@.UnitGroupRef tinyint,

// this needs to be a local var not an output param, how ?
@.UnitPosition tinyint Output

AS

// Assign a value to the the variable from a SELECT query, how ?
SELECT @.UnitPosition= SELECT MAX(UnitPosition) FROM tblUnitGroup
WHERE QualRef=@.QualRef AND UnitRef=@.UnitRef AND
UnitGroupRef=@.UnitGroupRef

// inc the value
@.UnitPosition+=1

// Use the new value in another SQL statement
INSERT INTO tblQualUnits ( QualRef, UnitRef, UnitGroupRef ,
UnitPosition )
VALUES ( @.QualRef, @.UnitRef, @.UnitGroupRef , @.UnitPosition)
GO-- MODIFIED STORED PROC:
CREATE PROCEDURE [dbo].[InsertQualUnit]
@.QualRef tinyint,
@.UnitRef tinyint,
@.UnitGroupRef tinyint

AS

-- this needs to be a local var not an output param, how ?
declare @.UnitPosition tinyint

-- Assign a value to the the variable from a SELECT query, how ?
SELECT @.UnitPosition= MAX(UnitPosition)
FROM tblUnitGroup
WHERE QualRef=@.QualRef AND UnitRef=@.UnitRef AND
UnitGroupRef=@.UnitGroupRef

-- inc the value
select @.UnitPosition=@.UnitPosition+1

-- Use the new value in another SQL statement
INSERT INTO tblQualUnits ( QualRef, UnitRef, UnitGroupRef ,
UnitPosition )
VALUES ( @.QualRef, @.UnitRef, @.UnitGroupRef , @.UnitPosition)|||See below and the following link.
http://www.google.co.uk/groups?selm...%40giganews.com

But your proc looks a bit strange. Why not just make the key (qualref,
unitref, unitgroupref) and then increment a quantity column? Like:

UPDATE tblQualUnits
SET unit_quantity = unit_quantity + 1
WHERE qualref = @.qualref
AND unitref = @.unitref
AND unitgroupref = @.unitgroupref

Otherwise your table is just an accumulator of redundant rows.

CREATE PROCEDURE [dbo].[InsertQualUnit]
@.qualref TINYINT,
@.unitref TINYINT,
@.unitgroupref TINYINT
AS

INSERT INTO tblQualUnits (qualref, unitref, unitgroupref, unitposition)

SELECT @.qualref, @.unitref, @.unitgroupref,
COALESCE(MAX(unitposition),0)+1
FROM tblUnitGroup
WHERE qualref = @.qualref
AND unitref = @.unitref
AND unitgroupref = @.unitgroupref
GO

--
David Portas
SQL Server MVP
--|||Thankyou for the quick reply.|||Thanks

Local Variable in stored procedure

Hi ,
I need to store a variable which has length more than 8000 bytes..Is there
is a workaround as varchar column takes only 8000 bytes/chracters..Text
cannot be used as local variable..
Pls suggest..
Regards
rectHave you tried creating a temporary table in tempdb, one
column of text ?
Peter
"It's true hard work never killed anybody, but I figure,
why take the chance?"
Ronald Reagan
>--Original Message--
>Hi ,
>I need to store a variable which has length more than
8000 bytes..Is there
>is a workaround as varchar column takes only 8000
bytes/chracters..Text
>cannot be used as local variable..
>Pls suggest..
>Regards
>rect
>.
>

local variable in select statement

Hello to all,
are there that have already used a select like the
following to catch backup history rows from different SQL
server defined as linked server
select *
from @.my_system.msdb.dbo.sysdbmaintplan_history
where .........
the local variable @...... set using a cursor seams to be
not sintactically correct.
Any idea
Thanks marinoYou need to use dynamic sql if the servername changes
e.g.
declare @.cmd nvarchar(500)
set @.cmd = N'select * from ' + @.my_system +
N'.msdb.dbo.sysdbmaintplan_history'
exec sp_executesql @.cmd
For more on dynamic sql see
http://www.algonet.se/~sommar/dynamic_sql.html
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Marino Prandini" <marino_prandini@.hotmail.com> wrote in message
news:181101c3aeb4$3af029b0$a601280a@.phx.gbl...
Hello to all,
are there that have already used a select like the
following to catch backup history rows from different SQL
server defined as linked server
select *
from @.my_system.msdb.dbo.sysdbmaintplan_history
where .........
the local variable @...... set using a cursor seams to be
not sintactically correct.
Any idea
Thanks marino

local variable assignment in CREATE TRIGGER

Hi Guys,

i'm batttling with the below Trigger creation

__________________________________________________ _
CREATE TRIGGER dbo.Fochini_Insert ON dbo.FochiniTable AFTER INSERT AS
BEGIN
DECLARE @.v_object_key VARCHAR(80)
DECLARE @.v_object_name VARCHAR(40)
DECLARE @.v_object_verb VARCHAR(40)
DECLARE @.v_datetime DATETIME

SELECT ins.Cust_Id INTO @.v_object_key FROM inserted ins <-- my problem area!!
SET @.v_object_name = 'FochiniTable'
SET @.v_object_verb = 'Create'
SET @.v_datetime = GETDATE()

IF ( USER <> 'webuser' )
INSERT INTO dbo.xworlds_events (connector_id, object_key, object_name, object_verb, event_priority, event_time, event_status, event_comment)
VALUES ('Fochini', @.v_object_key, @.v_object_name, @.v_object_verb, '1', @.v_datetime,'0', 'Triggered by Customer CREATE')

END
________________________________________________

i'm trying to get the INSERTED variable from table FochiniTable on colomn Cust_Id

and the statement: SELECT ins.Cust_Id INTO @.v_object_key FROM inserted ins - is failing [still a newbie on mssql server 2000]

any help will be appreciated
lehare.solved by modifying the errored line with

SELECT @.v_object_key = ins.Cust_Id FROM inserted ins

thanx any wayz

Local or global variable in an interaction session of the SQL analyzer

Hi,

I am new to SQL. Please bear with me and allow me to ask a dumb
question.

I am debugging a stored procedure (written in Trans-SQL), and I found
that the SQL analyzer that I use doesn't have a debugger. All I can
do it is execute a block of code and see what is going on in an
interaction seesion of the SQL analyzer. I would need to declare some
variable to hold values of the previous query in the interactive
session.

I understand that this can be easily done in a stored procedure via
the Declare command (e.g., Delcare @.order_no int). Is similar
functionality exists in an interaction session of the SQL analyzer?
If so, what is the command. Please advise.

Thank you very much for the help.

AlexAlex Cicco (lluum@.yahoo.com) writes:
> I am debugging a stored procedure (written in Trans-SQL), and I found
> that the SQL analyzer that I use doesn't have a debugger. All I can
> do it is execute a block of code and see what is going on in an
> interaction seesion of the SQL analyzer. I would need to declare some
> variable to hold values of the previous query in the interactive
> session.

I suppose that with SQL Analyzer you refer to Query Analyzer. If you are
using QA, you do indeed have an access to a debugger. Press F8 to get an
Object Browser Window, and then find the procedure. Right-click and at
the bottom of the context menu, voil!

> I understand that this can be easily done in a stored procedure via
> the Declare command (e.g., Delcare @.order_no int). Is similar
> functionality exists in an interaction session of the SQL analyzer?
> If so, what is the command. Please advise.

The same. For instance:

DECLARE @.x datetime
SET @.x = getdate()
SELECT @.x

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp