Local Variable Assignment In CREATE TRIGGER

Mar 7, 2006

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.

View 1 Replies


ADVERTISEMENT

Variable Assignment

Jul 21, 1999

How do you assign the value returned by a stored procedure to a declared variable and then display the value of that declared variable?

The following does not work:

declare @variable int
set @variable = stored_procedure
...
...
show value of @variable?? <HOW>

Sorry for the stupid questions but I am new to SQL in general.

View 1 Replies View Related

Variable Assignment

Apr 7, 2008

Is it possible to set the value for several variables from one select query?

View 1 Replies View Related

Variable Assignment

Apr 7, 2008

Is it possible to set the value for several variables from one select query?

View 4 Replies View Related

Inline Variable Assignment

Jan 22, 2004

I have to write a query for printing multiple barcodes, depending on the quantity of items that came in the store, based on the order number.



DECLARE @num INT
SELECT BarCodes.BarCode, BarCodes.ArticleID, ArticlesTrafic.DocumentID, ArticlesTrafic.TrafficQuantity
FROM BarCodes INNER JOIN
Articles ON BarCodes.ArticleID = Articles.ArticleID INNER JOIN
getAutoNumberTable(@num) ON @num=ArticlesTrafic.TrafficQuantity
WHERE (ArticlesTrafic.DocumentID = @Param2)



The thing i would like to do, is somehow assign a value to @num and pass it to the getAutoNumberTable stored procedure, which generates a table of consequtive numbers, so that each record is displayed multiple times. Is it even possible to do it without using temp tables and loops?

View 1 Replies View Related

Variable Assignment In Cursor Declaration

Jul 5, 2006

Hi,

here is the code segment below;
...
DECLARE find_dates CURSOR FOR
SELECT @SQL = 'select DISTINC(Dates) from ['+@name+'].dbo.['+@t_name+'] order by [Dates] ASC'
EXEC (@SQL)

but it gives error, variable assignment is not allowed in a cursor declaration. I need to use dynamic SQL , the only way to access all the dbs and their tables inside. Please help.

thanks

View 8 Replies View Related

Help On Script Component Assignment Of Output Variable

Sep 7, 2005

Hi all,

View 9 Replies View Related

How To Create Trigger Which CREAT TABLE From A Variable String?

Feb 23, 2006



I have a Table Name "Forums". I want to ceate an AFTER-Trigger on it. It will execute when ever a new row is inserted to "Froums" Table.

Here is what I did but It needs to be corrected:

::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

ALTER TRIGGER CreateTopicsTableTrigger

ON dbo.Forums

AFTER INSERT

AS

SET NOCOUNT OFF

DECLARE @myNewForum varchar

CAST(@@ROWCOUNT as varchar) /*Is it OK???*/

SET @myNewForum=@myNewForum+@@ROWCOUNT /*Here I dont know how assigments work in SQL*/

GO

CREATE Table @myNewForum /*Will this work some how???*/

( TopicID int IDENTITY NOT NULL, TopicTitle varchar(50) , CreatedBy varchar(50) ,

DateCreated DateTime , DateLastUpdate DateTime , LastUpdateBy varchar(50) )



::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

View 5 Replies View Related

Using Local Variable To Pass Login Name To CREATE LOGIN Script

Mar 19, 2008

Dear all;

I'm trying to use a local variable @NEW_LOGIN_CODE to pass LOGIN NAME to CREATE LOGIN script as follows:

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
declare

@NEW_LOGIN_CODE varchar(255),
@NEW_LOGIN_PASSWORD varchar(255);
begin

SET @NEW_LOGIN_CODE = 'any_login';
SET @NEW_LOGIN_PASSWORD = 'AnyPassword';

CREATE LOGIN @NEW_LOGIN_CODE WITH PASSWORD @NEW_LOGIN_PASSWORD;
end
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

the script will not work unliss I provided a hard coded login code and password as follows:

CREATE LOGIN ANY_LOGIN WITH PASSWORD 'AnyPassword'

what should I do to make the CREATE LOGIN script accept local variables as parameters?

Thanks


View 3 Replies View Related

Parent Package Variable Assignment Issue In Child Package.

Dec 26, 2006


We
have one main package from which 7 other child packages are called. We are using
ParentPackage variables to assign values for variables and database connections.



While the values from ParentPackage variable get assigned to some of the
packages properly, to others it doesn€™t assign the value.

For example:
Except for one of the packages the database connection string gets assigned
properly to all other packages.

Similarly, in another package one of the
variables doesn€™t get assigned. In other packages it is assigned properly.

We
have checked all the other property values and they are exactly the same.


We cannot make any head or tail of this erratic behavior.

Please Help.

View 3 Replies View Related

Local Variable

May 27, 2004

I am trying to create a procedure or function that will deal with weekly information for staff records.

Rather than declaring 14 local variables for the information, is it possible to declare a local variable as an array? Sorry for using VB terminology - not sure how SQL would describe it.

TIA

Fatherjack

View 3 Replies View Related

Problem With A Local Variable

Dec 19, 2001

Hello everybody,

Here I have two tables. Let's call them A and B.

A contains the folowing data: (address of persons)

PostCode Street Number CdeTour
-------- ------ ------ -------
31000 Well. 0025 NULL
31000 Well. 0050 NULL
31000 Wash. 0010 NULL

B contains the folowing data:

PostCode Street FirstEven LastEven FirstOdd LastOdd CdeTour
-------- ------ --------- -------- -------- ------- -------
31000 Well. 0002 0040 0043 0073 100
31000 Well. 0042 0084 0001 0041 200
31000 Wash. 0002 0050 0001 0049 100

* My problem is that I want to update the field A.CdeTour depending on the data present in B.

Let's take the first line from the table A: the number of the street 'well.' is '25', and '25' is odd and between '1' and '41' which meens that I should update my field A.CdeTour should be set to '100' (B.CdeTour).
Let's take another example, so in the second line: the street is still 'well.' but here the number is '50'. '50' is even and between '42' and '84' so the field A.CdeTour should be set to '200' (B.CdeTour).

Here I wrote a query, which doesn't work properly:

DECLARE @Num VARCHAR(4)
UPDATE A
SET @Num = Number, CodeTournee=
CASE WHEN @Num % 2 = 0 THEN(
-- even number of street
SELECT CdeTour
FROM B
WHERE @Num BETWEEN FirstEven AND LastEven
AND A.PostCode = B.PostCode
AND A.Street = B.Street
)ELSE(
-- odd number of street
SELECT CdeTour
FROM B
WHERE @Num BETWEEN FirstOdd AND LastOdd
AND A.PostCode = B.PostCode
AND A.Street = B.Street
)END
FROM A, B

The query runs but the problem is that it doesn't update the field, because it doesn't interpret @num by its value.

In fact, if instead of @num, I hard-code a value it works
... WHERE '0025' BETWEEN FirstOdd AND LastOdd ...

Can someone help me???? I would be very pleased, if someones could give me another way of doing it or a trick in order to avoid this problem.

Thanks in advance.

View 1 Replies View Related

Transact SQL - Local Variable

Jun 28, 2004

I execute the following in my Query Analyzer:

Declare @Test varchar(8000)

Set @Test='SELECT VIOXX_LastName + '' + VIOXX_FirstName + '' + CONVERT(varchar(50), VIOXX_Number) AS PlaintiffsName, VIOXX_Number
FROM tblPlaintiff WHERE VIOXX_Number NOT IN(SELECT VIOXX_Number FROM tblCase_Plaintiff) OR
VIOXX_Number IN (SELECT tblCase_Plaintiff.VIOXX_Number FROM tblCase INNER JOIN tblCase_Plaintiff ON tblCase.Case_Number = tblCase_Plaintiff.Case_Number
WHERE (tblCase.Status = ''InActive'')) ORDER BY VIOXX_Number, VIOXX_LastName'
Select @Test

and get the following result:
SELECT VIOXX_LastName + ' + VIOXX_FirstName + ' + CONVERT(varchar(50), VIOXX_Number) AS PlaintiffsName, VIOXX_Number
FROM tblPlaintiff WHERE VIOXX_Number NOT IN(SELECT VIOXX_Number FROM tblCase_Plaintiff) OR
VIOXX_Number IN (SELECT

the latter part of my original text is not stored in the variable. Is there some limitation on the number of characters for a local variable in transact sql?

Any ideas? Thanks in advance.

View 2 Replies View Related

Text Local Variable

Jan 17, 2007

Hi All,

How to declare TEXT datatype as local variable . My data is getting truncated as it cross varchar(8000) characters.

Saju

View 1 Replies View Related

Local Variable Slows Down SP (?)

Dec 17, 2007

Hi guys I am sitting and testing som variants of this simple SP, and I have an question that I couldent answer with google or any thread in this forum.

Perhaps I am doing something really easy completly wrong here.

Why does the local variables in the first code segment slow down the overall execution of the procedure?
Dont mind the logic why I have them there are only testing som things out.

If i declare two variables the same way:
DECLARE @v INT
SET @v = 100

When I use it in a WHERE CLAUSE:
...WHERE [V] BETWEEN @v AND @x)
Is there any different then
...WHERE [V] BETWEEN 100 AND 200)

Cant figure this out, why does it hurt the performance so bad? As a C# guy its the same thing ?

Thanks in advance
/Johan

Slow

ALTER PROCEDURE [dbo].[spStudio_Get_Cdr]
@beginDate DATETIME = null,
@endDate DATETIME = null,
@beginTime INT,
@endTime INT,
@subscribers VARCHAR(MAX),
@exchanges VARCHAR(MAX) = '1:',
@beginDateValue int,
@endDateValue int
AS
BEGIN
SET NOCOUNT ON;

DECLARE @s INT
SET @s = @beginDateValue
DECLARE @e INT
SET @e = @endDateValue
print @s
print @e

DECLARE @exch TABLE(Item Varchar(50))
INSERT INTO @exch
SELECT Item FROM [SplitDelimitedVarChar] (@exchanges, '|') ORDER BY Item


DECLARE @subs TABLE(Item Varchar(19))
INSERT INTO @subs
SELECT Item FROM [SplitDelimitedVarChar] (@subscribers, '|') ORDER BY Item

SELECT [id]
,[Abandon]
,[Bcap]
,[BlId]
,[CallChg]
,[CallIdentifier]
,[ChgInfo]
,[ClId]
,[CustNo]
,[Digits]
,[DigitType]
,[Dnis1]
,[Dnis2]
,[Duration]
,[FgDani]
,[HoundredHourDuration]
,[Name]
,[NameId]
,[Npi]
,[OrigAuxId]
,[OrigId]
,[OrigMin]
,[Origten0]
,[RecNo]
,[RecType]
,[Redir]
,[TerId]
,[TermAuxId]
,[TermMin]
,[Termten0]
,[Timestamp]
,[Ton]
,[Tta]
,[Twt]
,[Level]
FROM
[dbo].[Cdr] AS C
WHERE
(C.[DateValue] BETWEEN @s AND @e)
AND
(C.[TimeValue] BETWEEN @beginTime AND @endTime)
AND
EXISTS(SELECT [Item] FROM @exch WHERE [Item] = C.[Level])
AND
(EXISTS(SELECT [Item] FROM @subs WHERE [Item] = C.[OrigId] OR [Item] = C.[TerId]))

END



Fast

ALTER PROCEDURE [dbo].[spStudio_Get_Cdr]
@beginDate DATETIME = null,
@endDate DATETIME = null,
@beginTime INT,
@endTime INT,
@subscribers VARCHAR(MAX),
@exchanges VARCHAR(MAX) = '1:',
@beginDateValue int,
@endDateValue int
AS
BEGIN
SET NOCOUNT ON;

DECLARE @exch TABLE(Item Varchar(50))
INSERT INTO @exch
SELECT Item FROM [SplitDelimitedVarChar] (@exchanges, '|') ORDER BY Item


DECLARE @subs TABLE(Item Varchar(19))
INSERT INTO @subs
SELECT Item FROM [SplitDelimitedVarChar] (@subscribers, '|') ORDER BY Item

SELECT [id]
,[Abandon]
,[Bcap]
,[BlId]
,[CallChg]
,[CallIdentifier]
,[ChgInfo]
,[ClId]
,[CustNo]
,[Digits]
,[DigitType]
,[Dnis1]
,[Dnis2]
,[Duration]
,[FgDani]
,[HoundredHourDuration]
,[Name]
,[NameId]
,[Npi]
,[OrigAuxId]
,[OrigId]
,[OrigMin]
,[Origten0]
,[RecNo]
,[RecType]
,[Redir]
,[TerId]
,[TermAuxId]
,[TermMin]
,[Termten0]
,[Timestamp]
,[Ton]
,[Tta]
,[Twt]
,[Level]
FROM
[dbo].[Cdr] AS C
WHERE
(C.[DateValue] BETWEEN @beginDateValue AND @endDateValue)
AND
(C.[TimeValue] BETWEEN @beginTime AND @endTime)
AND
EXISTS(SELECT [Item] FROM @exch WHERE [Item] = C.[Level])
AND
(EXISTS(SELECT [Item] FROM @subs WHERE [Item] = C.[OrigId] OR [Item] = C.[TerId]))

END

View 4 Replies View Related

T-SQL (SS2K8) :: Set A Local Variable

Feb 24, 2015

declare @filename varchar(255),
@path varchar(255),
@sql varchar(8000),
@sql3 varchar(8000),

[Code].....

i need to set a Apostrophe in front of dir and at the end

View 4 Replies View Related

Concatenate Local Variable

Jun 17, 2008

Hi guys,

I have a cursor that loops over a table and I use a local variable to append the column data. However when I try to print this variable, it comes as empty!

The code is

declare @testVar varchar(max)
<cursor loop begins>
set @testVar = @testVar + ',' + @SomeColName
<cursor loop ends>

print 'Value: ' + @testVar

I can't understand why the @testVar is empty, since the @SomeColName has a value in every iteration.

Thanks!

View 3 Replies View Related

Insert Local Variable Value

Dec 15, 2006

Is there an expression syntax for putting a local variable value into a text box like there is for putting a parameter value? I'm using the report builder via VS

View 4 Replies View Related

Create Variable To Store Fetched Name To Use Within BEGIN / END Statements To Create A Login

Mar 3, 2014

I created a cursor that moves through a table to retrieve a user's name.When I open this cursor, I create a variable to store the fetched name to use within the BEGIN/END statements to create a login, user, and role.

I'm getting an 'incorrect syntax' error at the variable. For example ..

CREATE LOGIN @NAME WITH PASSWORD 'password'

I've done a bit of research online and found that you cannot use variables to create logins and the like. One person suggested a stored procedure or dynamic SQL, whereas another pointed out that you shouldn't use a stored procedure and dynamic SQL is best.

View 3 Replies View Related

Drop Trigger With A Variable Trigger Name

Sep 20, 2007



Hi all in .net I've created an application that allows creation of triggers, i also want to allow the deletion of triggers.
The trigger name is kept in a table, and apon deleting the record i want to use the field name to delete the trigger

I have the following Trigger

the error is at

DROP TRIGGER @DeleteTrigger

I'm guessing it dosen't like the trigger name being a variable instead of a static name
how do i get around this?

thanks in advance

-- ================================================

-- Template generated from Template Explorer using:

-- Create Trigger (New Menu).SQL

--

-- Use the Specify Values for Template Parameters

-- command (Ctrl-Shift-M) to fill in the parameter

-- values below.

--

-- See additional Create Trigger templates for more

-- examples of different Trigger statements.

--

-- This block of comments will not be included in

-- the definition of the function.

-- ================================================

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

-- =============================================

CREATE TRIGGER RemoveTriggers

ON tblTriggers

AFTER DELETE

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

Declare @DeleteTrigger as nvarchar(max)

select @DeleteTrigger = TableName FROM DELETED



IF OBJECT_ID (@DeleteTrigger,'TR') IS NOT NULL

DROP TRIGGER @DeleteTrigger

GO

END

GO

View 7 Replies View Related

Using TEXT Datatype As Local Variable In SP Or UDF

Mar 12, 2003

I've created a stored procedure that converts an input string in richtext format (input as type TEXT) to plain text. I would like to be able to return this newly converted string, but I need to have some way of storing it in a local variable. My problem is that since I can't use the TEXT datatype as a local variable, I have no way of storing the large amounts of text I converted within the procedure. The VARCHAR(8000) just isn't large enough for my purposes. Anyone have any suggestions on how to go about doing this?

View 2 Replies View Related

IDENTITY - Using A Local Variable Value For A Seed Value

Mar 22, 1999

I'm trying to have an identity column seed value specified with a local variable value as follows, however it
doesn't allow me to do it (Says cannot use a variable name for a seed value).
Any ideas or suggestions?

DECLARE @idvalue int

SELECT @idvalue = max(accountid) + 1
FROM account

CREATE TABLE accounttemp
(Accountid int IDENTITY(@idvalue,1),
name char(10),
address char(10))

View 1 Replies View Related

Determining If A Local Variable Exists

Jun 11, 2007

Is there a way to determine if a local variable exists or not?

There's a parameter I often use in code called @guid_batch that is usually declared in the parameter of a stored proc, but when in debugging it would be nice to have it available without having to change code.

Is there something that I could do similar to the following


Code:


IF VARIABLE_ID('@guid_batch') IS NULL
BEGIN
DECLARE @guid_batch UNIQUEIDENTIFIER
SELECT @guid_batch = NEWID()
END



Thanks in advance,
-MBirchmeier

View 8 Replies View Related

Select @Local-Variable From TestTable.!!!?

Oct 7, 2007

Hi all
Imagine we have a table as follow:

Create Table Test(
Pkey int not Null identity Primary Key,
Name varchar(20) not null,
Famil varchar(30) not null
)

now is there anyway to Run following Script:

declare @FLDName varchar(10)
set @FLDName='Name'
Select @FLDName from Test

in the other hand is there anyway to use Local variables in a SELECT statements as above?
to tell you the truth , what really motivated me to do this , was being able to declare a User-Defined-Function and passing some fields as an String and then use them in a SELECT statement or any other T-Sql codes!!!

Could anyone help me.?
Kind Regards.

View 1 Replies View Related

Slecting A Table Using A Local Variable Name

Jul 23, 2005

I am new at sql so would appreciate some helpI have the name of a table in alocal variable is it possible to select thistableDECLARE @name sysnameSET @name = 'tblSniffedItems'PRINT @nameSELECT * FROM @nameI expected this wo work but I got the following error.@name is declare as far as I knowServer messageMust declare the variable '@name'.Thanks in advanceAndre

View 4 Replies View Related

ADO Errors After Changing SP To Use Local Variable

Mar 16, 2007

Changed stored procedure

[dbo].[spLogonName @pNewLogonName varchar(60) AS

SELECT * FROM .dbo.tblUser Where vcLogonName = @pNewLogonName

to

[dbo].[spLogonName @pNewLogonName varchar(60) AS

DECLARE @Local_pNewLogonName varchar(60)

SET @Local_pNewLogonName = @pNewLogonName

SELECT * FROM .dbo.tblUser Where vcLogonName = @Local_pNewLogonName

and started getting this error on the web page.

ADODB.Recordset error '800a0cb3'

Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype.

Does anyone know why this is happening? Nothing on the site has changed. If I change the sp back the errors go away. I'm trying to use local variables in all SP to avoid the slowness that can happen when using the parameter varibles.

View 2 Replies View Related

Retrieving To Local Variable From Select Top ...

Feb 29, 2008

Hi,

I€™ve got the following statement :

SELECT TOP(1) Date, Status FROM TABLE1 WHERE TEC_STATUS = 1 ORDER BY DATE DESC

I want to retrieve the status field to a variable, but I€™m not being able to do so€¦ I think its due to the TOP function€¦

I have tried those with no success:

SELECT @var2 = TOP(1) [Date], @var = [ Status] FROM TABLE1 WHERE TEC_STATUS = 1 ORDER BY DATE DESC
SELECT @var2 = [TOP(1) Date], @var = [ Status] FROM TABLE1 WHERE TEC_STATUS = 1 ORDER BY DATE DESC


Is there Any way I can do that ?

Thanks

View 1 Replies View Related

Dispaying A Local Variable In A Cell

Feb 25, 2008


hi,

i'm fairly new to visual studio and have a select statment which is displayed fine in my report, i have headings however of which one i'd like as a variable that i've declared and set

declare @MyVariable varchar(6)
set @MyVariable = "abcdef"


i know how to display a field in a cell like follows:
=Fields!Code.Value

or a parameter like follows:
=Parameters!month.Value

but how do i display my local variable called @MyVariable?

thanks in advance.. I've searched but can't find out how to do this and it's probably really obvious!!

kev

View 6 Replies View Related

SQL Query Results Into Local Variable

Oct 11, 2006

Hi,

I'm trying to put the results from a SQL query that returns only one filed but one or more rows into a local variable in a comma separated format.

Any help is appreciated.

Thanks.

View 2 Replies View Related

Text Datatype As Local Variables In Trigger

Dec 12, 2005

Friends,I would just like to know that why SQL Server doen't allow us to definea text data type local variable while creating trigger?I tried creating a text variable in a trigger as a local variable andit raises error."Implicit conversion from data type text to nvarchar is not allowed.Use the CONVERT function to run this query".For this i have to use convert function in MS SQL Server.-ThanksBhavin Vyas

View 1 Replies View Related

How Do I Move Sql Variable Values To Local Variables

Nov 2, 2006

Can someone show how to do this?I have a  SqlDataSource1,  and i have a SELECT * FROM Table1How would i get@ProdName@ProdNumber                        Into the following local variablesString  ProductNameInt       ProductNumber              I’m using C# and ASP 2.0 VWDThanks for Help1 

View 2 Replies View Related

Store Count Result In A Local Variable

Feb 10, 2000

Hi All,
I need to store the row count from two different servers (one 6.5 and one 7.0)
to compare by doing this:(in T-SQL)

declare @kount1 int, @kount2 int
exec master..xp_cmdshell 'isql -Sserver65 -d dbA -T -Q "select count(*) from tableA",no_output
exec master..xp_cmdshell 'isql -Sserver70 -d dbA -T -Q "select count(*) from tableA",no_output

How can I save the rowcount in @kount1, @kount2 respectively for comparison?
Appreciate your feedback.
David Nguyen

View 4 Replies View Related

@local Variable Column Data Type

May 23, 2001

In a stored proc, can you declare a local variable that is an existing column in a table & then based on other criteria, do an order by using the local variable?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved