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
Hi,
Can I take the [index] result from a table and store it in a local var?
For example a table
declare @keyWords(
word varchar(40),
wordLen smallint
)
word len
---- ---
test 4
t 1
e 1
declare @index smallInt = 2
SET @keyWord = SELECT TOP @INDEX word FROM @KeyWords
This obviously is not correct for several reasons (TOP returns multiple results, TOP doesn't work with local var etc)
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
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.
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 RelatedI 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
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
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.
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 RelatedCan 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 RelatedI'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 RelatedFriends,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 RelatedIs there ANY data type other than varchar that is valid for localvariables and allows more than 8000 characters?
View 3 Replies View Related 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
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?
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
Hi guys,
My company is currently migrating from Interbase to SQL Server 2005. During the migration we have came across a rather peculiar issue and wondering if anyone can advise.
We have a table.. named "prospect" which holds client information
We have a stored procedure which hangs on the following statement.
DECLARE @surname char(25);
SET @surname='BLAH%';
SELECT *
FROM Prospect
WHERE c_surname LIKE @surname;
The above takes 28 seconds to run. The following statement returns a result inside a second.
SELECT *
FROM Prospect
WHERE c_surname LIKE 'BLAH%';
In Interbase, the original returned the answer within a second too. The schema in both database is the same.
The 1st statement does not use an index! The execution plan is different to the 2nd statement. I am aware I can create an index recommended by the Database Engine Tuning which solves the issue or specify the index to use in the original statement but why does the engine not use the correct index if there is a variable involved? I need to know as we have just started looking at the code.
Thanks,
Kalim
I got rid of the .rdl.data files, which apparently has been a successful fix for this problem in the past. Everything worked fine Thursday when I left (I was off Friday - Saturday), but today I get this error for all but one of the reports in the solution when I try to preview the report. BTW, the published stuff works fine. Thanks in advance for any help.
View 6 Replies View RelatedHi,
I'm running a merge replication on a sql2k machine to 6 sql2k subscribers.
Since a few day's only one of the merge agents fail's with the following error:
The merge process could not retrieve generation information at the 'Subscriber'.
The index entry for row ID was not found in index ID 3, of table 357576312, in database 'PBB006'.
All DBCC CHECKDB command's return 0 errors :confused:
I'm not sure if the table that's referred to in the message is on the distribution side or the subscribers side? A select * from sysobjects where id=357576312 gives different results on both sides . .
Any ideas as to what is causing this error?
Hi everyone,
When we create a clustered index firstly, and then is it advantageous to create another index which is nonclustered ??
In my opinion, yes it is. Because, since we use clustered index first, our rows are sorted and so while using nonclustered index on this data file, finding adress of the record on this sorted data is really easier than finding adress of the record on unsorted data, is not it ??
Thanks
Hi... I was hoping if someone could share me some thoughts with the issue that I am having at the moment.
Problem: When I run the package in my local machine and update local SS DB/table - new records writes OK in the table. BUT when I changed my destination meaning write record into another physical SS DB/table there is no INSERT data occurs. AND SO when I move/copy over that same package into another server (e.g. server that do not write record earlier) and run it locally IT WORKS fine too.
What I am trying to do is very simple - Add new records in a SS table using SSIS . I only care for new rows and not even changed rows.
Here is my logic -
1. Create Ole DB source to RemoteSERVER - using SELECT stmt
2. I have LoopUp component that will look for NEW records - Directs all rows that don't find match and redirect rows (error output).
3. Since I don't care for any rows that is matched in my lookup - I do nothing or I trash the rows
4. I send the error rows (NEW rows) into OleDB destination
RESULTS when I run the package locally and destination table is also local - WORKS FINE;
But when I run the package locally and destination table is in another Sserver (remote) - now rows is written.
The package is run thru BIDS manually so there is no sucurity restrictions attached to it.
I am not sure what I am missing. And I do not see error in my package either. It is not failing.
Thanks in advance!
Hi,
I accidentally deleted a table in my local server. How can I get back the table? I did not do it as a transaction!
Thanks in Advance
How do I use table names stored in variables in stored procedures?
Code Snippetif (select count(*) from @tablename) = 0 or (select count(*) from @tablename) = 1000000
I receive the error 'must declare table variable '@tablename''
I've looked into table variables and they are not what I would require to accomplish what is needed.
After browsing through the forums I believe I need to use dynamic sql particuarly involving sp_executesql. However, I am pretty new at sql and do not really understand how to use this and receive an output parameter from it(msdn kind of confuses me too). I am tryin got receive an integer count of the records from a certain table which can change to anything depending on what the user requires.
Code Snippet
if exists(Select * from sysobjects where name = @temptablename)
drop table @temptablename
It does not like the 'drop table @temptablename' part here. This probably wouldn't be an issue if I could get temporary tables to work, however when I use temporary tables i get invalid object '#temptable'.
Heres what the stored procedure does.
I duplicate a table that is going to be modified by using 'select into temptable'
I add the records required using 'Insert into temptable(Columns) Select(Columns)f rom TableA'
then I truncate the original table that is being modified and insert the temporary table into the original.
Heres the actual SQL query that produces the temporary table error.
Code Snippet
Select * into #temptableabcd from TableA
Insert into #temptableabcd(ColumnA, ColumnB,Field_01, Field_02)
SELECT ColumnA, ColumnB, Sum(ABC_01) as 'Field_01', Sum(ABC_02) as 'Field_02',
FROM TableB
where ColumnB = 003860
Group By ColumnA, ColumnB
TRUNCATE TABLE TableA
Insert into TableA(ColumnA, ColumnB,Field_01, Field_02)
Select ColumnA, ColumnB, Sum(Field_01) as 'Field_01', Sum('Field_02) as 'Field_02',
From #temptableabcd
Group by ColumnA, ColumnB
The above coding produces
Msg 208, Level 16, State 0, Line 1
Invalid object name '#temptableabcd'.
Why does this seem to work when I use an actual table? With an actual table the SQL runs smoothly, however that creates the table names as a variable problem from above. Is there certain limitation with temporary tables in stored procedures? How would I get the temporary table to work in this case if possible?
Thanks for the help.
I am trying to update one table when records are inserted in another table.
I have added the following trigger to the table “ProdTr” and every time a record is added I want to update the field “Qty3” in the table “ActInf” with a value from the inserted record.
My problem appears to be that I am unable to fill the variables with values, and I cannot understand why it isn’t working, my code is:
ALTER trigger [dbo].[antall_liter] on [dbo].[ProdTr]
for insert
as
begin
declare @liter as decimal(28,6)
[Code] ....
Hi!
What is the difference in performance if I use a Temp-table or a local-table variable in a storedprocedure?
Why?
//Daniel
Greetings -I'm using an Access front end to a SQL Server (2000) databas*e. Viaseveral steps, I create a temp table and manipulate the data* in it.Iwant to update the backend with this new data but my UPDATE *queryfailsas my temp table is local and the SQL database doesn't know *about it.There are no linked tables in the FE database.I have the following (DAO):Set Db = CurrentDbSet Qdf = Db.CreateQueryDef(TMP_QUERY_NAME)Qdf.connect = ConnectString()sqlString = "UPDATE tblRemote " & _"SET " & _"tblRemote.Some_Foo = tblLocal.Foo, " & _"FROM tblRemote INNER JOIN tblLocal " & _"ON tblRemote.Some_ID = tblLocal.Some_ID;"Qdf.sql = sqlStringQdf.ReturnsRecords = FalseQdf.Execute dbFailOnErrorIs there any way of doing this without adding a linked table*?Thanks, chris
View 2 Replies View Relatedplease explain the differences btween this logical & phisicall operations that we can see therir graphical icons in execution plan tab in Management Studio
thank you in advance
Hello,I am writing a function that uses two table variables. The structures ofboth are shown here:DECLARE @workdates TABLE (conflict CHAR(1),workdate SMALLDATETIME)DECLARE @existing TABLE (workdate SMALLDATETIME)I need to do an update on the first table:UPDATE @workdatesSET conflict = 'X'FROM @existing sWHERE workdate = s.workdateI am concerned that the unqualified 'workdate' in the WHERE clause willgive me an ambiguous column reference. Is this SQL statement valid?Thanks,Andrew
View 3 Replies View RelatedHey everyone,I read in a SQL Server book that you can now create a tablevariable.DECLARE @TMP TABLE (list of fields)Then you can you can use the statementINSERT INTO @TMPSELECT (whatever).I've tried it and it works. The book also says that youshould be able to pass these variables between storedprocedures and functions. Problem is, when I try todeclare the variable at the top of the procedure, thesyntax checker hates it.Anyone else out there try this out?SAM
View 1 Replies View RelatedWill reporting services allow the use of table variables in the SQL query?
View 1 Replies View RelatedI want to querry a table based on the input of a form, I have tables Monday, Tuesday ect., and want to display the records for Monday if the user selects Monday in a menu. I need to delcare a table name but am not sure how?
DELCARE @myVar char(20)
SET myVar = Request.Form("select2");
"SELECT * FROM @myVar"
I know user defined global table variables are not allowed in sql. I'm trying to avoid using temporty tables for speed reasons. I have a function in which a table variable is defined, and a function within that function that needs to call that table variable. Any ideas?
Thanks
I created a stored procedure like this:CREATE PROCEDURE SPASBEGINCREATE TABLE #T( C INT )INSERT INTO #T(C) VALUES (1)SELECT * FROM #TENDWhen I call it this way: EXEC SP, it works ok.But when I do it like this:SELECT * FROM OPENQUERY( MYSERVER, 'EXEC SP')I receive an error: Invalid object name '#T'Why?...*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 2 Replies View Related