Select Into Local Variable Within A IF NOT EXIST Test?

Feb 10, 2004

Hi all,

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.

Thanks!
Paul

View 5 Replies


ADVERTISEMENT

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

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

More Selecting Into Local Variable With Dynamic Select...

May 25, 2004

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

Thoughts?

View 8 Replies View Related

SELECT Statement That Assign Value To Local Variable

Nov 12, 2013

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.

How to modify this statement?

View 8 Replies View Related

How Do I Select A Column From Sp_spaceused Into A Local Variable?

Jul 20, 2005

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.

View 6 Replies View Related

How To Assign The SELECT Statement Output To A Local Variable?

May 7, 2008

 
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..?

View 7 Replies View Related

Test To See If A Column Exist.

Jan 22, 2007

Do you know of some SQL that I can test a table to see if a column exists or not inside a stored procedure?

What I have is a table that contains data for a 10 year history. I asked at the time and was told at the beginning of every new year the table would create 2 new columns so I dynamically set up some of my stored procedure to provide for that….well as of Jan 22 they have not updated them so I have 2 options:

1) hard code the first year in there and change it when they tell us

2) test for the field if it is there start the countdown to grab the rest of the historic info….if not check for the next year until it finds the field in the DB.



I’d rather do 2.

Thanks for any help.

View 2 Replies View Related

Test Replication On Local Copy?

Jun 15, 2008

Hi,

I am trying to test a replication or at least get the feel for it on my local copy before I set it up on the real server.

Can I do that by setting my local database as the publisher as well as the subscriber?

I am getting an error message but I am wondering if there are any settings I can change to make this work. Or any other ideas of how to see the replication tool before actually doing it live?

Error message:
"Server 'MyComputerName' is neither a Publisher nor a Distributor, or you do not have permission to access replication functionality on this server.

I might add that I am not only new to the project but also new to MS SQL Server.

Thank you!

View 1 Replies View Related

How To Copy Db From Test Server To Local Machine Running MSSQL2000 Per.

Jul 23, 2005

I am in a situation where I need to get a copy of test database that ison production server running MSSQL 2000 Standard to my local machinerunning MSSQL 2000 personel. I tried to use the copy wizard where itappears I get connected to the source server OK but when I try toindicate the destination server which is my local machine I get errorspoping up about cannot connect to (local) etc.I am NOT a DBA just a programmer trying to get a local test environmentup to be more productive.Lsumnler

View 2 Replies View Related

Question ... How To Test/verify Existence Of Local File Or Folder Using T-SQL (2005)?

Jan 25, 2008

Two questions actually ...

1) Need a simple routine or system function for testing for or verifying the existence of a file or folder on the local server's file system. Returning a simple boolean value or 1 or 0 would be fine.

2) Need a syntax and use description of the new "master.dbo.xp_create_subdir" function ... anyone have some documentation or links? MS technet and MSDN have nothing.

Thanx
EHammer

View 18 Replies View Related

Receive Files From Remote Site Only If File Does Not Exist In Local Directory

Jul 13, 2007

I need to only receive files via ftp when the file does not exist on my local machine. FIles are being added to the remote location on a weekly basis and they are being downloaded locally. I do not want to download all the files each time. Instead, just download what was not already downloaded. Is there a way to do this? I want to do this using SSIS / ftp task.



Thanks in advance.

View 4 Replies View Related

DTS Package, Global Variable, Test Vs Live?

Feb 1, 2007

Are global variable parameters in a DTS package specific to "only" that one package and do not effect other packages?

I need to add a variable to a test package but am worried that if I add this parameter it might affect the live production Table. There are two different servers on different sides of the firewall, there are two different databases and there are two tables but with different names, and there are DTS packages but they reference different tables in the query code.

So is it ok to add the global variable which would allow this DTS to delete recent records from the "test" version and it wont do anything to my live version?

View 1 Replies View Related

Variable Does Not Exist?

Feb 9, 2007

I have a variable scoped to the package named "Filecount".  When I try to reference it in a precedence constraint expression (@Filecount > 0) I get "The variable 'Filecount' was not found in the variables collection.  The variable might not exist in the current scope."

 

Anybody know why?  Thanks.

View 5 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

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

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

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

CLR Test Script SELECT Returns No Row Data

Sep 12, 2006

Hi,

The test.sql scripts I write to test CLR stored procedures run successfully, but when I want to display the resulting data in the database with a simple "SELECT * from Employee"

I get the result as:
Name Address
---- -------
No rows affected.
(1 row(s) returned)

But not the actual row is displayed whereas I would expect to see something like:

Name Address


---- -------

John Doe


No rows affected.


(1 row(s) returned)

I have another database project where doing the same thing displays the row information but there doesn't seem to be a lot different between the two.

Why no results in first case?

Thanks,
Bahadir

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