Local Variables Problem

Jun 12, 2008

quote:list the first and the last name of the employee with a name of Thomas. Use local variables for the first name and the last name and the @@ROWCOUNT command.

SET NOCOUNT ON
DECLARE @variable varchar(50)
SET @variable = 'name'
SELECT @variable = fname + ' ' + lname
FROM employee
WHERE @variable = '%Thomas%'
IF @@ROWCOUNT > 0
PRINT 'Employee Name is ' + @variable
ELSE
PRINT 'Employee not found'

when i execute it, it said employee not found. what do i need to add or revise?

it should be:

quote:Employee Name is Argon Thomas.

View 5 Replies


ADVERTISEMENT

Regarding Local Variables

Sep 25, 2006

declare @Id integer
set @Id=1145
select '@'+'Id'

result: @Id

But I want the result to be 1145. Is it possible, If so please let me know how.

Thanks
Suresh

View 5 Replies View Related

Text Local Variables In TSQL SPs

Aug 1, 2007

how do i get text local variables in a stored procedures? cos i was thinking of doing a log to log what my SP did or not do. 

View 4 Replies View Related

Create Database Using Local Variables

Feb 4, 1999

I have an installation script and want to create a database based on some data in a table (config). Testing the script though I get a message:

Msg 170, Level 15, State 1
Line 12: Incorrect syntax near '@DBData'.

The relevant sql is:

declare @DBName varchar (40)
declare @DBData varchar (40)
declare @DBLog varchar (40)
declare @DBSize int
select @DBName = obj_txt from config where obj_nm='DBName'
select @DBData = @DBName + '_Data'
select @DBLog = @DBName + '_Log'
select @DBSize = obj_int from config where obj_nm='DBSize'
raiserror('Creating Database %s ....',0,1,@DBName) with nowait

Create Database @DBName
on @DBData = @DBSize
Log on @DBLog = @DBSize

I don't think there's anything wrong, apart from the fact I am using the local variables. Is this allowed on a Create Database statement? I haven't found anything in Technet that may help.

Here's hoping....
Thanks
Dan

View 1 Replies View Related

Local Variables In Case Statment

Nov 11, 2004

Hi

I am trying to use a global variable in a case when and am not getting the correct results. If I use static data, it works fine.

Here is a tableless example, which should return Shipper. Any ideas are appreciated.


Code:


declare @shipperGBS varchar(3000)
declare @sQuote char
set @sQuote = char(39)
set @shipperGBS = 'ACI,ADO,ALD,AMS,AWB'
set @shipperGBS = Replace(@shipperGBS, ',', @sQuote + ',' + @sQuote)
set @shipperGBS = @sQuote + @shipperGBS + @sQuote
select case when ('ACI' IN (@shipperGBS)) then 'Shipper' else 'Consignee' end as ClientCharge

View 1 Replies View Related

Set Local Variables When EXEC-ing A Sql String

Nov 23, 2006

Hey guys, needless to say I'm new at this.

What I'm trying to accomplish is to execute a SQL string via exec and inside it set the value of a local variable. I understand that I cannot do this the way I'm currently doing it because an Executed string runs in a scope of its own so local variables are invisible. And sure enough this is the error I get. So how do I make this work?

Code snip:




declare @ErrMessage as varchar(1000)
set @Sql = 'if exists ( select * from ' + @TargetTable + ' where (' + @ValueField + '=' + '''' + @NewValue + ''''
set @Sql = @Sql + ' and ' + @TagField + '= ' + '''' + @Tag + '''' + '))' + @CRLF
set @Sql = @Sql + 'set @ErrMessage = ''Insertion could not be performed. ' + @NewValue + ' is already an entry in the table. '''


So what I want is check if a certain table has entries...what table? I don't know, there are tens that this check will apply to. And if that tavle has an entry that satisfies the where clause then assign the appropriate error message to @ErrMessage.

I understand sp_executesql might do the trick because it allows passing local params back and forth.

Any ideas on how to make this work? I appreciate the effort.

View 3 Replies View Related

Local Variables In Stored Proc

Jul 23, 2005

How do you declare and then SELECT a value for a local variable withinstored procedure, increment the value and then use in an Insertstatement? ThanksAny sites that explain this syntax for SQL Server 2000? Thankshals_leftCREATE PROCEDURE [dbo].[InsertQualUnit]@QualRef tinyint,@UnitRef tinyint,@UnitGroupRef tinyint,// this needs to be a local var not an output param, how ?@UnitPosition tinyint OutputAS// Assign a value to the the variable from a SELECT query, how ?SELECT @UnitPosition= SELECT MAX(UnitPosition) FROM tblUnitGroupWHERE QualRef=@QualRef AND UnitRef=@UnitRef ANDUnitGroupRef=@UnitGroupRef// inc the value@UnitPosition+=1// Use the new value in another SQL statementINSERT INTO tblQualUnits ( QualRef, UnitRef, UnitGroupRef ,UnitPosition )VALUES ( @QualRef, @UnitRef, @UnitGroupRef , @UnitPosition)GO

View 4 Replies View Related

Is It Possible To Index Local Table Variables?

May 7, 2008

I have a function that returns a table of matching names. For the most part it returns under 5,000 results which doesn't cause too much lag joining to the search report data (which is indexed). But sometimes there might be 10,000 + search name results. I find this and higher can cause excessive lag joining. Is it possible to index this result table from the function? Or any other suggestions?

THANKS

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

Local Variables In User Defined Functions

Mar 24, 2004

I'm having a problem declaring variables in UDFs. Are they allowed? Can someone send me some syntax to see what I am doing wrong?

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

SQL Server Data Types For Local Variables

Jul 20, 2005

Is there ANY data type other than varchar that is valid for localvariables and allows more than 8000 characters?

View 3 Replies View Related

Stored Procedure - Local Variables Show As NULL

Mar 18, 2008

 I have a stored procedure where I gather some data and then insert the data into a table variable.  I then attempt to go through each row of the table variable, asign the values to local variables to be inserted into other tables.  However, the local variables show as NULL.BEGIN
DECLARE @tblcontact table
(
SOKey int,
Cntctkey varchar(60),
Cntctownerkey int,
LASTNAME varchar(32),
FIRSTNAME varchar(32),
WORKPHONE varchar(32),
EMAIL varchar(128),
processed int DEFAULT 0
)

INSERT INTO @tblcontact (SOKey, Cntctkey, Cntctownerkey, LASTNAME, FIRSTNAME, WORKPHONE, EMAIL)
SELECT ...

DECLARE @ID int,
@sokey int,
@cntctkey int,
@cntctownerkey int,
@name varchar(65),
@email varchar(128),
@phone varchar(32)

WHILE EXISTS (SELECT * FROM @tblcontact WHERE processed = 0)
BEGIN
SELECT @ID = MIN(SOKey) FROM @tblcontact WHERE processed = 0

SELECT @cntctkey = (CAST(LTRIM(REPLACE(Cntctkey,'CN',' '))AS int)),@cntctownerkey = Cntctownerkey, @name = FIRSTNAME + ' ' + LASTNAME, @phone = WORKPHONE, @email = EMAIL, @sokey = SOKey
FROM @tblcontact
WHERE @ID = SOKey AND @cntctkey <> '43778'

INSERT INTO tciContact (Cntctkey, Cntctownerkey, CreateType, EMailAddr, EmailFormat, EntityType, ExtUser, Name, Phone, UpdateCounter)
VALUES (@cntctkey, @cntctownerkey, '0', @email, '3', '401', '0', @name, @phone, '0')

UPDATE tsoSalesOrder
SET Cntctkey = @cntctkey, UserFld4 = 'temp'
WHERE SOKey = @sokey

UPDATE @tblcontact
SET processed = 1 WHERE @ID = SOKey
END
END  

View 4 Replies View Related

Memory Usage For Local Variables In Stored Procedures

Jul 3, 2003

I would like to know if there is a penalty for Varchar variables in stored procedures if I declare them Varchar(8000) instead of Varchar(1000).
I have a lot of variables and sometimes the content will be more them 1000 characters.
Is memory only allocated for the the actual contents or for the complete declared length?

View 2 Replies View Related

Stored Proc: Query Vals To Local Variables

Aug 18, 2004

I am brand spankin new to stored procedures and don't even know if what I want to do is possible. From everything I've read it seems like it will be. I have a table, punchcards. In this table are all the punch in/out times for a week. I want to create a stored proc to calculate how many hours a punchcard entry is.

Thats the dream.

The reality is that I can't even get a tinyint from a table to load to a variable and be printed out. I am using sql server 8.

Here is what I have as of this moment for my sp.


ALTER PROCEDURE usp_CalculatePunchcard
AS
DECLARE @dtPP DateTime
SET @dtPP = (SELECT thursday_in1
FROM punchcards
WHERE (punchcard_id = 1))
/*
Also tried....
SELECT @dtPP=thursday_in1
FROM punchcards
WHERE (punchcard_id = 1)
*/

PRINT @dtPP

RETURN
/*
for some reason i can't use GO ... even though every
document i've read on stored procedures has used GO
and none use RETURN
*/


The only output this is producing is ' Running dbo."usp_CalculatePunchcard". '

Any help would be greatly appreciated as I am about to kick someone/something.

Thanks

View 2 Replies View Related

Local SQL Server Instances Not Showing Up On Local Servers Tab Of Management Studio Logon Screen

Oct 2, 2007

We have a 64-bit VM server running SQL Server 2005. The SQL Server on this particular VM server has 6 local instances installed. On the Management Studio logon screen I can type the full name of the local instance and connect to it, however if I press the drop down in the Server name field, choose Browse and select the Local Servers tab there is nothing listed under Database Engines.

Any idea why the 6 local instances don't show up under Database Engines? This is preventing me from installing a vendor application because their installer looks for local SQL Server instances on this server, but if SQL Server won't even show the local instances then the installer doesn't see them either.

Any help is greatly appreciated.

Thanks,
Craig

View 3 Replies View Related

Execute DTS 2000 Package Task Editor (Inner Variables Vs Outer Variables)

Sep 4, 2006

Hi,

I am not comfortable with DTS 2000 but I need to execute a encapsulated DTS 2000 package from a SSIS package. The real problem is when I need to pass SSIS variables to DTS 2000 package. The DTS 2000 package have 3 global variables that I can identify on " Execute DTS 2000 Package Task Editor - Inner Variables ". I believe the SSIS variables must be mapped on " Execute DTS 2000 Package Task Editor - OuterVariables ". How can I associate the SSIS variables(OuterVariables ) to "Inner Variables"? How can I do it? Much Thanks.

João





View 8 Replies View Related

How To Design A Package With Variables So That I Can Run It By Dos Command Assigning Values To Variables?

Jan 24, 2006

Hi,

I would like to design a SSIS package, which have couple of variables. It loads a xls file specified in a variable [varExcelFileFullPath] .

I will run it by commands: exec xp_cmdshell 'dtexec /SQL ....' (pls see an example below).

It seems it does not get the values passed in for those variables. I deployed the package to a sql server.

are there any grammar errors here? I copied it from dtexecui. It worked inside Dtexecui not in dos command.

exec xp_cmdshell 'dtexec /SQL "LoadExcelDB" /SERVER test /USER *** /PASSWORD ****

/MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW

/LOGGER "{6AA833A1-E4B2-4431-831B-DE695049DC61}";"Test.SuperBowl"

/Set Package.Variables[User::varExcelFileName].Properties[Value];"TestAdHocLayer"

/Set Package.Variables[User::varExcelWorkbookName].Value;"Sheet1$"

/Set Package.Variables[User::varExcelFileFullPath].Value;"D: estshareTestAdHocLayer.xls"

/Set Package.Variables[User::varDestinationTableName].Value;"FeaturesTmp"

/Set Package.Variables[User::varPreSQLAction].Value;"delete from FeaturesTmp"

'



thanks,



Guangming

View 2 Replies View Related

Not Able To Connect To The Local Database With (local) As Server Name

Jun 7, 2006

I am facing a problem in connecting to the local database with server name as (local).

I have installed SQL Server 2005 in my machine. When I try to connect to the SQL server with the server name as SUNILKUMAR I am able to connect but when I try to connect to the same server with the server name as (local) I am not able to connect. SUNILKUMAR is my machine name and SQL server is running locally.

if anyone can help me what is the problem in this case it is highly appriciated.

View 7 Replies View Related

Need Help - Local Synchronization Between SQL Mobile And Local SQL Database

Dec 21, 2005

Hi Everyone

I am at the stage of architecting my solution

My goal is to develop the system on a windows application and pda

There is a central server which will create a publication called inventory

The laptops which host the windows application will be subscribers to the central server using merge replication

The client now wants the PDA using SQL Mobile to synchronize with the local subscirber database on the laptop using active sync. They dont want to do it via WIFI to the IIS Server at the central server

I have been reading for days and I am still unsure whether this is possible to do.

I know Appforge provide a conduit for palm to access synchronization but not local sql databases

I would appreciate your help immensley

View 7 Replies View Related

Moving A SQL Server 2000 Database From A Local Drive To Another Local Drive

Jan 31, 2008

Being a very novice SQL Server administrator, I need to ask the experts a question.

How do I go about moving a database from 1 drive to another? The source drive (C is local to the server, but the target drive (E is on a Storage Area Network (SAN), although it is still a local drive for the server. I want to move the database from C: to E:. Can someone provide me with instructions?

Thanks,
Rick

View 4 Replies View Related

Failed To Generate A User Instance Of SQL Server Due To Failure In Retrieving The User's Local Application Data Path. Please Make Sure The User Has A Local User Profile On The Computer. The Connection Will Be Closed

Dec 7, 2006

This is my first time to deploy an asp.net2 web site. Everything is working fine on my local computer but when i published the web site on a remote computer i get the error "Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path. Please make sure the user has a local user profile on the computer. The connection will be closed" (only in pages that try to access the database)
Help pleaseee

View 3 Replies View Related

@@ Variables

Jul 13, 2007

What is @@variables means in sql server?

View 3 Replies View Related

Using LIKE With Variables

Jan 21, 2004

Is there a way to use the LIKE keyword with variables like below?

DECLARE @Name CHAR(10)
SET @Name = 'MyName'


SELECT * FROM table
WHERE my_name LIKE @Name

This won't work, but you get the idea of what I want. Any thoughts?
Thanks,

View 1 Replies View Related

DTS Variables..?

Jun 4, 2004

Is there a way to pass variables off to DTS by ADO.NET?
Such as a FileName to export to and/or a parameter for the export query?

View 7 Replies View Related

SQL HELP......IF's With Variables

Jun 8, 2005

Anyone know how to write the portion in red in a stored procedure?LEFT OUTER JOIN TITLE AS T ON (POS.TITLE_ID = T.TITLE_ID)JOIN DISTRICT_LOCATIONS AS DL ON (POS.DISTRICT_LOCATION_ID = DL.DISTRICT_LOCATION_ID)WHERE POS.PRIMARY_IND = 1 IF @DISTRICT_LOCATION_ID != 'All' BEGIN  and DL.DISTRICT_LOCATION_ID = @DISTRICT_LOCATION_ID                ENDIF @ATTENDANCE_STATUS_ID!= 'All' BEGIN  and AST.ATTENDANCE_STATUS_ID= @ATTENDANCE_STATUS_ID                ENDUNION ALLSELECT DISTINCT 5 AS TAG ,3 AS PARENT ,convert(varchar,getdate(),101) as [ACTIVITY_REPORT!1!REPORT_DATE] ,AX.ACTIVITY_CLASS_ID AS [ACTIVITY!2!ACTIVITY_CLASS_ID] ,NULL AS [ACTIVITY!2!ACTIVITY_NAME]

View 2 Replies View Related

T-SQL Variables

Feb 16, 2000

Hi,

I am testing SQL Server 7.0. In Stored Proc I try to select a database which name is stored in the variable @databasename.
I get the error that it can't open a database @databasename.
Can I declare the database object in T-SQL?

Thanks

View 1 Replies View Related

Using Variables In T-SQL

Nov 2, 2000

I have this script:
.....
USE master
go

/* Get Name of Server & declare variables */
declare @sname varchar(30)
declare @db1 varchar(30)
declare @db2 varchar(30)
declare @db3 varchar(30)
declare @dbf1 varchar(30)
declare @dbf2 varchar(30)
declare @dbf3 varchar(30)

select @sname = rtrim(substring(srvname,5,30)) from sysservers

print 'The name of this server is: ' + @sname

Set @db1 = @sname + 'database1'
Set @db2 = @sname + 'database2'
Set @db3 = @sname + 'database3'
Set @dbf1 = @db1 + 'RL_log'
Set @dbf2 = @db2 + 'RL_log'
Set @dbf3 = @db3 + 'RL_log'

print @db1
print @dbf1
go

ALTER DATABASE @db1 MODIFY FILE (NAME = @dbf1, MAXSIZE = UNLIMITED)
go
ALTER DATABASE @db2 MODIFY FILE (NAME = @dbf2, MAXSIZE = UNLIMITED)
go
ALTER DATABASE @db3 MODIFY FILE (NAME = @dbf3, MAXSIZE = UNLIMITED)
go
.....

When I run it, I get the following errors:
.....
The name of this server is: KANSASCITY
KANSASCITYdatabase1
KANSASCITYdatabase1RL_log
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '@db1'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '@db2'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '@db3'.
.....

Don't let the line numbers fool you. They refer to the number of lines since the last 'go' in the script. As you can see, the @db1 and @dbf1 variables are evaluating correctly.

WHAT I AM TRYING TO ACCOMPLISH:
I am attempting to change the setting of the Transaction Log to grow to fill up the entire disk. I do not wish to limit the space at this time. I have approximately 200 servers to manage and this script would be most useful in managing them, if it only worked.

Should I be using a different function to change the settings on the Transaction Log? Something other than ALTER DATABASE?

Thank you (in advance)

View 2 Replies View Related

Using Variables In DDL

Mar 28, 2003

Hi

I think this is an easy question.

Is it possible to use a variable in a create database statement

i.e.

declare @db_name varchar(20)
select @db_name='new_db'


IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = @db_name)
DROP DATABASE @db_name

CREATE DATABASE [@db_name] ON (NAME = @db_name, FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQLdataew_DB_Data.MDF' , SIZE = 300, FILEGROWTH = 10%) LOG ON (NAME = N'new_DB_Log', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQLdataImport_Utility_DB_Log.LDF' , SIZE = 30, FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO

Also, if the above is possible, how can i pass a database name to the script if I am calling it from a batch file (using osql).

Thanks,

Jim

View 2 Replies View Related

Using Variables In T-SQL

Jul 26, 2000

I want to change database in my SQL-script like this:
DECLARE @DB_NAME varchar(30)
SELECT * FROM @DB_NAME.dbo.TableName,
but it is syntactically incorrect.
I could use
USE (@DB_NAME)
but I write Stored Procedure, it is not allowed using
USE statement in any Stored Procedures.

View 2 Replies View Related

Variables

Oct 31, 2005

Hey folks,

i'm trying to make a variable inside of a loop do this.

@a = 1
@b = Variable + @a

and then inside of the loop @a incriments by 1, so i'd get

variable1
variable2
...etc
but I can't get the variable to work right...

Any help would be great!
Caden

View 2 Replies View Related

Variables In MS SQL

Apr 14, 2006

I have a MS SQL server DB which stores tables and queries and a MS Access DB which acts as an interface and stores Forms.
I am a MS Access Developre and this is my first MS Sql Server DB. I would like to know how can i pass a variable which is an object in a Form (In MS Access DB) to a query in MS SQL?

In MS Access( i mean mdb file which all tables, queries and forms are in one mdb file)it is very easy, for example if you would like to pass idfld from Form of frminfo to a query you can say: Where idname like " & Forms!frminfo!idfld , but it does not work on MS SQL.
Would you please tell me how can i use a variable in MS SQL query?

Thank
Ebi

View 8 Replies View Related

DTS And Variables

Jan 29, 2007

I hope this is the place for questions about DTS.

I'm working on a DTS package that runs against development databases. At some point I'll move it to qual for testing, and I'll have to change names of servers and credentials for some of the connections. Can I do that with global variables? BOL aren't very helpful about this.

To be more specific. I'll have one "master" package that will be kicking off other packages. I've found in help that I can use dtsrun to pass variables - so I'm assuming that DBAs moving packages from one environment to another will adjust parameters passed by dtsrun to the "master" package. But how does the "worker" package reference global variable from the "master" package? And how do I reference global variable inside the "worker" package so that it's value is used for any of the properties I see when clicking on "disconnected properties" (like server name or user name)?

Is there some sort of tutorial out there that explains use of global variables?

Thanks in advance for any and all leads

View 9 Replies View Related







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