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
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
Apr 7, 2008
Is it possible to set the value for several variables from one select query?
View 1 Replies
View Related
Apr 7, 2008
Is it possible to set the value for several variables from one select query?
View 4 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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