__________________________________________________ _
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]
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?
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.
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.
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.
* 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.
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?
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]))
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!
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
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.
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?
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?
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))
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
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!!!
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
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.
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
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!!
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
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
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?
