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?
insert into #t(branchnumber) values (005) insert into #t(branchnumber) values (090) insert into #t(branchnumber) values (115) insert into #t(branchnumber) values (210) insert into #t(branchnumber) values (216)
[code]....
I have a parameter which should take multiple values into it and pass that to the code that i use. For, this i created a parameter and temporarily for testing i am passing some values into it.Using a dynamic SQL i am converting multiple values into multiple records as rows into another variable (called @QUERY). My question is, how to insert the values from variable into a table (table variable or temp table or CTE).OR Is there any way to parse the multiple values into a table. like if we pass multiple values into a parameter. those should go into a table as rows.
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.
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'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!!
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?
__________________________________________________ _ 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'm trying to be slick, but so far am just all wet *snicker*
I've got a table with rows that may or may not exist, and am trying to retrieve a column if an associate row DOES exist.
For argument's sake, my PIndex table looks like: PID int CreateDate smalldatetime CloseID float
Here is my select logic that I thought would save an extra select to load my local variable if the row is actually in the table...
DECLARE @CloseID float
IF NOT EXISTS (SELECT @CloseID = CloseID FROM PIndex WHERE ((PID = '14') and (CreateDate = '2004-02-06'))) SET @CloseID = 100
The SET afterwards is just to initialize the variable if it can't be had from an existing row in the table.
The trouble is, that it fails to compile with the following error: >>>>> Line 3: Incorrect syntax near '='.
Any insights? My goal is to use a single select to load the value into my local variable if the associated row exists, or to set my local variable to 100 if it doesn't.
Not wishing to derail the other recent thread on loading a local variable, I've posted this query (hee,hee,hee...I kill me) on a separate thread...though I think I am trying to do something similar...that is to build a dynamic select statement, but return a count of the rows it finds/doesn't find to a local variable...using the (amazingly timely) responses above, I tried this:
Note that the local variables @TargetDate and @TLevel are necessary because they are being passed into the procedure as variables....
DECLARE @SQLCmd varchar(256) DECLARE @TargetDate smalldatetime DECLARE @TLevel int DECLARE @n int SET @TargetDate = '2004-05-24' SET @TLevel = 1
SET @SQLCmd = 'SELECT @n = count(*) FROM EventLog WHERE ((CONVERT(varchar(10), [Date], 101) = ''' + CONVERT(varchar(10), @TargetDate, 101) + ''') AND (MsgLevel = ' + CONVERT(varchar(3), @TLevel) + '))' exec (@SQLCmd) if @n > 0 print 'yep' else print 'nope'
and, it's TRYING to work...but apparently the local variable @n is not recognized in the execution of the dynamic statement, as this is the output: Server: Msg 137, Level 15, State 1, Line 1 Must declare the variable '@n'. nope
I am trying to figure out a way to retrieve a field value and assign it to a local variable with out destroying the whole structure of my T-SQL statement.
Here is the code:
DECLARE @AVERAGE_WHOLESALE_PRICE VARCHAR(20) DECLARE @ORDERBY VARCHAR(20) SELECT TOP 1 @AVERAGE_WHOLESALE_PRICE = P.NPT_PRICEX, CASE NPT_TYPE WHEN '07' THEN 1 WHEN '09' THEN 2
[Code] ....
The error message is Msg 141, Level 15, State 1, Line 3 A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.
I want to look at the size of the current database, so I can create a newone if it gets too big (we are working around the 2gb MSDE limit for ourcustomers).I would like to do something like this:DECLARE @size INTEGERexecute BLOB0000.dbo.sp_spaceusedand make @size = the database_size column value that sp_spaceused returns.Any way to do this?Thanks.
/*Given*/CREATE TABLE [_T1sub] ([PK] [int] IDENTITY (1, 1) NOT NULL ,[FK] [int] NULL ,[St] [char] (2) NULL ,[Wt] [int] NULL ,CONSTRAINT [PK__T1sub] PRIMARY KEY CLUSTERED([PK]) ON [PRIMARY]) ON [PRIMARY]GOINSERT INTO _T1sub (FK,St,Wt) VALUES (1,'id',10)INSERT INTO _T1sub (FK,St,Wt) VALUES (2,'nv',20)INSERT INTO _T1sub (FK,St,Wt) VALUES (3,'wa',30)/*Is something like the following possible.The point is to change the value of the variableinside the query and use it in the calculated field.This doesn't compile of course, but is therea way to accomplish the same thing?*/DECLARE @ndx intSET @ndx = 1SELECT(a.FK+ (CASE WHEN @ndx > 0THEN (SELECT @ndx = b.WtFROM _T1sub bWHERE b.Wt = a.Wt)ELSE 0 END)) as FKplusWTFROM _T1sub a/*Output would look like this:*/FKplusWT-----------112233/*I know, I can get this output just by addingFK+WT. This is not about that.This is about setting vars inside a query*/thanks, Otto Porter
I am using a local variable to capture datetime and then select records from another table by making use of the above local variable result. But the query is running too slow when I use the local variable
declare @a smalldatetime select @a=last_run_time from job_status where job_des='sample'
SELECT * FROM History WHERE CHANGE_DATE> @a
Instead of the above select statement if I use the below statement it is returning results quickly. Can someone help me in tuning the above query. SELECT * FROM History WHERE CHANGE_DATE> '5/23/2008 6:22:00.000 AM '
History table has columns ( case number, change_date, change_desc). I have two indexes defined on the above table one is on case_number and the other on change_date.I tried using force index but still the query is running slow.
In my program i have function that will get one value from Database. Here i want to assign the output of the sql query to a local variable. Its like select emp_id into Num from emp where emp_roll=222; here NUM is local variable which was declared in my program. Is it correct.? can anyone please guide me..?
I'm trying to do something like this in SQL Server: <code> CREATE PROCEDURE sp_insert_proc ( @item1 as int, @item2 as varchar(50), ) DECLARE @LocalVariable AS varchar(50) SET @LocalVariable = dbo.sp_storedprocedure INSERT INTO Table ( Column1, Column2, Column3, ) VALUES ( @Item1, @Item2, @LocalVariable ) </code> Is this possible? How can I return the results of the stored procedure to a variable so I can use those results
I have a dynamic query which returns me a result and I want to capture that value to make further use of it in the same code. Is that possible?? exec ('select col_nm from table_name'). i want the result of this query to be captured. DP
Is there a way to assign a dynamic query result to a local variable?
declre @sqlString nvarchar(4000), @minEventDate datetime, @databaseName varchar(25) selct @databaseName = 'customer_12345' (actually, a cursor loop will assign the database name dynamically, here just to simplify the situation)
Though the select min(eventDate) from customer_12345.dbo.tblABC returns a date, ex. '02/01/2004 12:35 pm', however, the printed @minEventDate is always with Null value. It mean, the value was never correctly assigned to the local variable.
As an alternate way, I am using temp table to insert it with the query result and then assign to the local variable. Since I have many local variables to try to get the min, max, count for around 10 fields, perfer a way to direct assign to the local variable instead of 10 temp tables.
