Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







Using Cursor Variable As A Tablename


I am currently using a cursor to scroll through sysobjects to extract table names and then extracting relevant column names from syscolumns.

I then need to run the following script:

declare Detail_Cursor cursor for
select @colname, max(len(@colname)) from @table

The message I receive is "must declare variable @table".

Immediately prior to this script I printed out the result of @table which works fine so the variable obviously is declared and populated.

Can anyone let me know what I'm doing wrong or how I can get the same result.

Thanks


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Function With A Tablename As Variable
Dear all,

Can someone help me with the following function? I would like to use a table name as a variable.

Thanks in advance!

CREATE FUNCTION FAC_user.Overzicht_DTe (@tabel1 as nvarchar, @proces as nvarchar, @categorie as nvarchar)
RETURNS numeric AS
BEGIN
declare @aantal numeric

if @proces = 'Inhuizen'
begin
if @categorie = 'open_op_tijd'
begin
SET @aantal =(SELECT Count(@tabel1 + '.Contractnummer')
FROM @tabel1, Rapportageweek
WHERE@tabel1.Verwerkingsdatum is null
AND @tabel1.UiterlijkeVerwDatum >= Rapportageweek.Rapportagedatum
AND @tabel1.ItemType = 'ZVHG'
AND @tabel1.ItemType = 'ZVHN'
AND @tabel1.ItemType = 'ZVIG'
AND @tabel1.ItemType = 'ZVIN'
GROUP BY@tabel1.Maand, @tabel1.Jaar)
end

if @categorie = 'open_te_laat'
begin
SET @aantal =(SELECT Count(@tabel1 + '.Contractnummer')
FROM @tabel1, Rapportageweek
WHERE@tabel1.Verwerkingsdatum is null
AND @tabel1.UiterlijkeVerwDatum < Rapportageweek.Rapportagedatum
AND @tabel1.ItemType = 'ZVHG'
AND @tabel1.ItemType = 'ZVHN'
AND @tabel1.ItemType = 'ZVIG'
AND @tabel1.ItemType = 'ZVIN'
GROUP BY@tabel1.Maand, @tabel1.Jaar)
end

end

return @aantal

END

View Replies !   View Related
Using A Variable For Tablename In Select Statement?
I have a stored procedure that accepts the table name as a parameter. Is there anyway I can use this variable in my select statement after the 'from' clause. ie "select count(*) from @Table_Name"?
When I try that is says "Must declare the table variable @Table_Name". Thanks!

View Replies !   View Related
Can Is Pass Tablename In SQL Stmt As Variable
I am trying to create a stored procedure for automating Bulk inserting into tables
for one database to another.
Is there any way i can pass the table name as variable to insert and select stmt


thanks in advance

View Replies !   View Related
How Can You Use A Variable Tablename And Retrieve The Output From The Insert?
We are trying to create a unique key from a table with indentity set in the table. We will have a number of these tables. Therefore, we will be creating a stored procedure and passing thetable as a parameter. In this example we are setting the table.

When we run the the script, the output clause from the insert should give us a unique number from the given table in the temporary table. This example stores the output in a temporary table @tTemp.

How can you use a variable table name and retrieve the output from the Insert?


declare @tTestTable varchar (20)

set @tTestTable = 'mis.test_sequence'


--DECLARE @tTestTable TABLE ( sqVal [int] IDENTITY(1,1) NOT NULL, add_date datetime)
declare @testsql varchar (4000), @testseq int

DECLARE @tTemp table (mainpk int)

set @testsql = 'DECLARE @tTemp table (mainpk int) INSERT ' + @tTestTable + ' OUTPUT INSERTED.sqVal into @tTemp VALUES (getdate() ) SELECT @testseq=mainpk FROM @tTemp'

select @testsql

EXECUTE sp_executesql @testsql, N'@testseq int output,@tTemp table (mainpk int),@tTemp table (mainpk int) ',@tTemp,@tTemp,@testseq output,@tTemp

SELECT * FROM @tTemp



Please help
Thanks Tim.

View Replies !   View Related
Variable Type For Fetcing The Cursor Record In Tsql Cursor
what is the equivalent of the %rowtype in oracle for the tsql

View Replies !   View Related
T-SQL Cursor Variable..
Hello All,Is there a T-SQL equivalent of the PL/SQL cursor rowtype datatype ?Thanks in advance

View Replies !   View Related
Cursor/variable Help
I'm not sure about this one so if someone could help I'd appreciate this.

As shown below I've declared a variable name1 to be used in a while statement substituting for an object name in a select statement (2000 SP3a)
and throwing the shown error. Are variables allowed to be used to substitute for object names or is there another problem? Thanks.

View Replies !   View Related
How To Use Variable In A Cursor
if i have the cursor

cursor:

cursor for
select name
from sysobjects
where type='P' AND category='0'

and wants a variable before sysobjects which equals a table name, so itll be:

cursor for
select name
from @variable..sysobjects
where type='P' AND category='0'

how do i include a variable within a cursor statement

View Replies !   View Related
Bind Variable In CURSOR
SQL Server 2000 SP4 with AWE hotfix. Windows 2003 SP1.I have a stored procedure which is not working the way I think itshould be.I have a CURSOR which has a variable in the WHERE clause:DECLARE get_tabs CURSOR local fast_forward FORSELECT distinct tablename, id, shcontig1dt, shcontig2dtFROM db_indWHERE dbname = @dbnameORDER BY tablenameIt won't return anything, even when I verify that @dbname has a valueand if I run the query in Query Analyzer with the value, it returnsrows:SELECT distinct tablename, id, shcontig1dt, shcontig2dtFROM db_indWHERE dbname = 'Archive'ORDER BY tablenameDB_Rpt_Fragmentation11575791622006-03-29 09:52:11.7772006-03-2909:52:11.823DtsAdtStdArchive_DataSourceType5175768822006-03-2909:52:11.8702006-03-29 09:52:11.887DtsADTstdArchiveNotUsed3575763122006-03-29 09:52:11.8872006-03-2909:52:12.103I've taken out most of the guts for simplicity, but here's what I'vegot:--CREATE TABLE dbo.db_ind--(--db_ind_tkintIDENTITY,-- id int NULL,-- tablename sysname NOT NULL,-- indid int NULL,-- indexname sysname NOT NULL,-- shcontig1dt datetime NULL,-- defragdt datetime NULL,-- shcontig2dt datetime NULL,-- reindexdt datetime NULL--)ALTER PROCEDURE IDR(@hours int)AS--SET NOCOUNT ON--SET ANSI_WARNINGS OFFDECLARE @tabname varchar(100),@indname varchar(100),@dbname varchar(50),@vsql varchar(1000),@v_hours varchar(4),@shcontig1dtdatetime,@shcontig2dtdatetime,@defragdtdatetime,@reindexdtdatetime,@idint,@indidint,@rundbcursorint,@runtabcursorint,@runindcursorintDECLARE get_dbs CURSOR local fast_forward FORSELECT dbnameFROM db_jobsWHERE idrdate < getdate() - 4or idrdate is nullORDER BY dbnameDECLARE get_tabs CURSOR local fast_forward FORSELECT distinct tablename, id, shcontig1dt, shcontig2dtFROM db_indWHERE dbname = @dbnameORDER BY tablenameDECLARE get_inds CURSOR local fast_forward FORSELECT indid, indexname, defragdt, reindexdtFROM db_indWHERE dbname = @dbnameAND tablename = @tabnameORDER BY indexnameOPEN get_dbsFETCH NEXT FROM get_dbsINTO @dbnameIF @@FETCH_STATUS = 0SELECT @rundbcursor = 1ELSESELECT @rundbcursor = 0SELECT @v_hours = CONVERT(varchar,@hours)--================================================== ================================================== =====--================================================== ================================================== =====--================================================== ================================================== =====WHILE @rundbcursor = 1BEGIN -- db whilePRINT '============================='PRINT @dbnamePRINT '============================='--================================================== ================================================== =====--================================================== ================================================== =====OPEN get_tabsFETCH NEXT FROM get_tabsINTO @tabname, @id, @shcontig1dt, @shcontig2dtIF @@FETCH_STATUS = 0BEGINPRINT 'table: ' + @tabnameSELECT @runtabcursor = 1endELSEBEGINPRINT 'not getting any tables! '-- <<<<< THIS IS WHERE IT HITSSELECT @runtabcursor = 0endWHILE @runtabcursor = 1BEGINPRINT @dbnamePRINT @tabname--================================================== ================================================== =====OPEN get_indsFETCH NEXT FROM get_indsINTO @indid, @indname, @defragdt, @reindexdtIF @@FETCH_STATUS = 0SELECT @runindcursor = 1ELSESELECT @runindcursor = 0WHILE @runindcursor = 1BEGINPRINT 'Index:' + @dbname + '.' + @tabname + '.' + @indnameFETCH NEXT FROM get_indsINTO @indid, @indname, @defragdt, @reindexdtIF @@FETCH_STATUS = 0SELECT @runindcursor = 1ELSESELECT @runindcursor = 0END-- 1st loop through indexesCLOSE get_inds--================================================== ================================================== =====--==========PRINT 'db.tab: ' + @dbname + '.' + @tabname--==========--================================================== ================================================== =====OPEN get_indsFETCH NEXT FROM get_indsINTO @indid, @indname, @defragdt, @reindexdtIF @@FETCH_STATUS = 0SELECT @runindcursor = 1ELSESELECT @runindcursor = 0WHILE @runindcursor = 1BEGINPRINT 'dbname: ' + @dbnamePRINT 'tabname: ' + @tabnamePRINT 'indname: ' + @indnameFETCH NEXT FROM get_indsINTO @indid, @indname, @defragdt, @reindexdtIF @@FETCH_STATUS = 0SELECT @runindcursor = 1ELSESELECT @runindcursor = 0END -- 2nd loop through indexesCLOSE get_inds--================================================== ================================================== =====FETCH NEXT FROM get_tabsINTO @tabname, @id, @shcontig1dt, @shcontig2dtIF @@FETCH_STATUS = 0SELECT @runtabcursor = 1ELSESELECT @runtabcursor = 0END-- loop through tablesCLOSE get_tabs--================================================== ================================================== =====--================================================== ================================================== =====PRINT 'Index Maintenence complete. Job report in[DB_Rpt_Fragmentation]'PRINT ''FETCH NEXT FROM get_dbsINTO @dbnameIF @@FETCH_STATUS = 0SELECT @rundbcursor = 1ELSESELECT @rundbcursor = 0END -- loop through databasesCLOSE get_dbsdeallocate get_dbsdeallocate get_tabsdeallocate get_inds--================================================== ================================================== =====--================================================== ================================================== =====--================================================== ================================================== =====GOAnd this is what I'm getting:=============================Archive=============================(0 row(s) affected)not getting any tables!Index Maintenence complete. Job report in [DB_Rpt_Fragmentation]......etc.Am I missing something obvious?Thank you for any help you can provide!!

View Replies !   View Related
Set Value For Variable In A Declared Cursor
Hi,

I have a problem on setting the value for the variable in a declared cursor. Below is my example, I have declared the cursor c1 once at the top in a stored procedure and open it many times in a loop by setting the variable @str_var to different values. It seems the variable cannot be set after the cursor declared. Please advise how can I solve this issue.

------------------------------------------------------------------------
DECLARE @str_var VARCHAR(10)
DECLARE @field_val VARCHAR(10)

DECLARE c1 CURSOR LOCAL FOR
SELECT field1 FROM tableA WHERE field1 = @str_var


WHILE (Sometime TRUE)
BEGIN

....

SET @str_var = 'set to some values, eg. ABC123, XYZ123'

OPEN c1

FETCH c1 INTO @field_val

WHILE (@@fetch_status != -1)
BEGIN

PRINT @field_val
...

FETCH c1 INTO @field_val
END

CLOSE c1

END

DEALLOCATE c1

----------------------------------------------------------------------

Thanks a lots,
Vincent

View Replies !   View Related
Cursor Variable Problem
My code below is supposed to pull the destination and then work out 2 calculations per month on each destination. The results returns the string '@destination' instead of the value from the table and also the month value is returned completely wrong. My main problem though is the getting the correct destination to return. If anybody has any pointers I would be very grateful. This is driving me mad!!
Thanks a lot in advance

Here some sample code:


DECLARE @Destination char(50)
DECLARE AVGCallsToDest INSENSITIVE scroll CURSOR

FOR
SELECT destination from table1

OPEN AVGCallsToDest
FETCH First FROM AVGCallsToDest
INTO @Destination

WHILE (@@FETCH_STATUS = 0 )
BEGIN

exec("Insert into NewTable(Card, [Month], NumberOfCalls, AverageLength, Destination)
select 'Phone Card' as 'Card', DATEPART(mm,adetdate) as 'Month',count(*) as 'NumberOfCalls',
avg(bdur)/60 as 'AverageLength', "" +@Destination+ "" as 'Destination'
from table1 (nolock)
whereDATEPART(mm,adetdate) = 5 and
DATEPART(yyyy,adetdate) = DATEPART(yyyy,getdate()-1)
group by DATEPART(mm,adetdate),bdur
order by Month")


FETCHNext FROM AVGCallsToDest
INTO@Destination
End
CLOSE AVGCallsToDest
DEALLOCATE AVGCallsToDest

View Replies !   View Related
How To Iterate Through Table Variable Without Using Cursor
Hi,
I need a small help. In my stored procedure, i create a table variable and fill it with records based on my query. The ID field within the table is not continous and can have any value in increasing order .e.g. The ID sequence may be like 20, 33, 34, 59, 78, 79... I want to iterate through each record within the table but without using a Cursor. I want to use a loop for this purpose. There are many articles pointing out how to iterate through records in a table variable but that requires the IDs to be continous which is not possible in my case. Can anyone help me solve this problem...
Any help is appreciated...

View Replies !   View Related
Variable As Column In Cursor Select
I can't seem to get a cursor to work when I'm passing in a variable for a column name of the select statement. For example:

declare @col varchar(50)

set @col = 'Temperature'

declare notifycurs cursor scroll for
select @col from Table

Obviously this won't work correctly (since the result will simply be 'Temperature' instead of the actual float value for temperature). I tried to use quotes for the entire statement with an EXEC
(ie. exec('select '+@col+' from Table' )
but that gave me an error.

Is there a way to pass in a variable for a column name for a curor select statement????

View Replies !   View Related
Variable As Field Name In CURSOR FOR UPDATE
I'm trying something like:

UPDATE tbl SET @varFieldName = @varValue

The procedure runs, and when I PRINT @varFieldName, it looks fine, but the table isn't getting updated, and no errors, wierd.

I have the CURSOR open for update, but I didn't list the field names, that shouldn't be a problem, as all fields should be updateable then.

To get the field name, I :

SET @varFieldName = 'SomeChars' + LTRIM(STR(asmallint)) + 'SomeMoreChars'

Thanks,
Carl

View Replies !   View Related
Adding Variable To SELECT For CURSOR
I'm trying to build a select statement for a CURSOR where part of the SQL statement is built using a variable.
The following fails to parse:

Declare Cursor1 Cursor
For
'select table_name from ' + @database + '.Information_Schema.Tables Where Table_Type = ''Base Table'' order by Table_Name'
Open cursor1

That doesn't work, I've also tried using an Execute() statement, no luck there either. Any ideas or suggestions are greatly appreciated.

View Replies !   View Related
Cursor Declared With Variable In Where Clause
When I execute next query on sqlserver 6.5 nested in stored procedure I can see that 'open testCursor' selected rows using new value of @var. When I execute query on sqlserver 7.0 I can see that 'open testCursor' selected rows using value of @var before 'declare ... cursor'. Is there any way to force sqlserver 7.0 to proccess cursor like it did it before.

select @var = oldValue

declare testCursor cursor
for select someColumns
from someTable
where someColumn = @var

select @var = newValue

open testCursor

fetch next from testCursor into @someColumns

Thank's in advance.

Mirko.

View Replies !   View Related
How To Read The Rows In A Cursor Variable
Hi,

I have a dynamic query that returns its values in a cursor variable.
How do I read each row from this cursor in a loop ?
Eg.:
use AdventureWorks
go
DECLARE @sqlnvarchar(4000),
@paramsnvarchar(4000),
@tables_cursorcursor,
@db_namenvarchar(50),
@table_namenvarchar(4000),
@schema_namenvarchar(50);

set @db_name = 'AdventureWorks';
set @schema_name = 'Production';
set @table_name = 'BillOfMaterials, Product';
set @sql =
' select a.name table_name ' +
' from ' + @db_name + '.sys.tables a join ' + @db_name + '.sys.schemas b ' +
' on (a.schema_id = b.schema_id) ' +
' where b.name= @schema_name1 ' +
' and @table_name1 is null ' +
' order by 1; '
SELECT @params = N' @table_name1 nvarchar(3000) ,' +
N' @schema_name1 nvarchar(100) ,' +
N' @cursor cursor output'

EXEC sp_executesql @sql, @params, @table_name,@schema_name , @tables_cursor OUTPUT

View Replies !   View Related
Storing The Result Into A Variable W/o Cursor
Hello All,

A select statement returns one column with more than one rows.Is there a way to store it in sql Variable without using a Cursor.

Thank you in advance

View Replies !   View Related
Variable Assignment In Cursor Declaration
Hi,

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.

thanks

View Replies !   View Related
Possible To Fetch Next From Cursor Into Table Variable?


Hello,

I have searched the net for an answer but could not find one. When I declare a table variable
and then try to insert fetched row into the table variable like:




Code Snippet
declare @table table (col1 nvarchar(50), col2 nvarchar(50))
declare curs for
select * from sometable
open curs
fetch next from curs into @table






it does not work. any help would be great.

thnx

View Replies !   View Related
How To Include Variable In CURSOR SQL Filter Clause?
After trying every way I could come up with I can't get a filter clauseto work with a passed variable ...I have a cursor that pulls a filter string from a table (works OK),then I want to use that filter in a second cursor, but can't get thesyntax ...@bakfilter is equal to "MISV2_db_%.BAK" before I try to open and fetchfrom the second cursor. Here is the cursor declaration:DECLARE curFiles CURSOR FORSELECT FileName, FileDateFROM DataFileWHERE (((Active)=1) AND ((FileName) LIKE '@bak_filter'))ORDER BY FileDate DESCWhat do I need to do to get it to use the string contained in@bak_filter?Thanks in advance, Jim

View Replies !   View Related
Output Of Select Stament Into Variable Within Cursor
Within a cursor that I am building I would like to execute a select statement built from a varchar variable as such:

SELECT @BuildDBPageUsed = 'SELECT sum(reserved) FROM ' + @DatabaseName + '.dbo.sysindexes WHERE segment <> 2'

Next I execute the statement, which is now in the variable @BuildDBPageUsed. Such as:

EXEC (@BuildDBPageUsed)

The resulting output I need to set to another variable @DBPageUsed which is integer. I have been unsuccessful in finding the correct set of commands to do this. How should I build the command to input the results of the EXEC (@BuildDBPageUsed) into the integer variable @DBPageUsed?

*Thanks* for any help in this matter.
Brad

View Replies !   View Related
In Cursor: Help To Perform Proper Comparison Using Variable
Hi All,

I failed to find record when using variable in cursor in WHERE clause:

ID is uniqueidentifier field in the table
DECLARE @EncounterID uniqueidentifier
........
WHERE ID = @EncounterID -> this does not work, though @EncounterID is set properly and can see its value in debugger

WHERE ID = 'E3AE2C5B-06F2-4A3C-A3A4-7D6CC43DE012' -> this works fine and record found

Tried to CAST(@EncounterID as char(40)) but still no luck.

I would greatly appreciate any advise hot to make it working.

Thank you very much in advance

Roman

View Replies !   View Related
Question About Assigning Cursor Variable Data Types...
Hi,
I'm just curious about something...when assigning a datatype to a cursor variable in ORACLE, you can use the keyword TYPE to automatically grap the datatype from the associated column (or use ROWTYPE to create a cursor variable to represent an entire row in a table). It's so much easier and if the datatype of one of the cursor columns is altered, the associated cursor variable assumes the new datatype.

Is there a way to do this in SQL Server 7.0 ?

many thanks :)

View Replies !   View Related
Combing In A Cursor, A Select Statement With The WHERE Clause Stored In A Variable
Hi
I am ramesh here from go-events.com
I am using sql mail to send out emails to my mailing list


I have difficulty combining a select statement with a where clause stored in a variable inside a cursor

The users select the mail content and frequency of delivery and i deliver the mail

I use lots of queries and a stored procedure to retrieve thier preferences. In the end i use a cursor to send out mails to each of them.

Because my query is dynamic, the where clause of my select statement is stored in a variable. I have the following code
that does not work

For example

DECLARE overdue3 CURSOR
LOCAL FORWARD_ONLY
FOR SELECT DISTINCT Events.E_Name, Events.E_SDate, Events.E_City, Events.E_ID FROM Events, IndustryEvents + @sqlquery2
OPEN overdue3

I get an error message at the '+' sign
which says, cannot use empty object or column names, use a single
space if necessary

How do I combine the select statement with the where clause?

Help me...I need help urgently

View Replies !   View Related
Dynamic Query, Local Cursor Variable And Global Cursors
Hi all.



I am stuck in a bit of a conundrum for quite a while now, and I hope someone here will help me figure this one out.



So, first things first: let me explain what I need to do. I am
designing a web application that will allow users to consult info
available in a SQL2000 database. The user will enter the search
criterea, and hopefully the web page will show matching results.



The problem is the results shown aren't available per se in the DB, I
need to process the data a bit. I decided to do so on the SQL Server
side, though the use of cursors. So, when a user defines his search
criteria, I run a stored procedure that begins by building a dynamic
sql query and creating a cursor for it. I used a global cursor in order
to do so. It looked something like this:



SET @sqlQuery = ... (build the dinamic sql query)

SET @cursorQuery = 'DECLARE myCursor CURSOR GLOBAL FAST_FORWARD FOR ' + @sqlQuery

EXEC @cursorQuery

OPEN myCursor

FETCH NEXT FROM myCursor INTO ...

CLOSE myCursor

DEALLOCATE myCursor



This works fine, if there's only one instance of the
stored procedure running at a time. Should another user connect to the
site and run a search while someone's at it, it'll fail due to the
atempt to create a cursor with the same name.



My first thought was to make the cursor name unique, which led me to:

...

SET @cursorName = 'myCursor' + @uniqueUserID

SET @cursorQuery = 'DECLARE '+ @cursorName + 'CURSOR FAST_FORWARD FOR ' + @sqlQuery

EXEC @cursorQuery

...



The problem with this is that I can't do a FETCH NEXT FROM @cursorName since
@cursorName is a char variable holding the cursor name, and not a
cursor variable. So to enforce this unique name method the only option
I have is to keep creating dynamic sql queries and exucting them. And
this makes the sp a bitch to develop and maintain, and I'm guessing it
doesn't make it very performant.



So I moved on to my second idea: local cursor variables. The problem with
this is that if I create a local cursor variable by executing a dynamic
query, I can't extract it from the EXEC (or sp_executesql) context, as
it offers no output variable.


I guess my concrete questions are:


Is it possible to execute a dynamic sql query and extract a (cursor) variable from it?Is it possible to populate a local cursor variable with a global cursor, by providing the global cursor's name?Can I create a local cursor variable for a dynamic sql query? How?



Anybody sees another way arround this?Thanks in advance,

Carlos

View Replies !   View Related
Variable In CURSOR Sql Statement (was &"Please Help Me&")
Hi All,

What i am trying to do is concatenate variable "@Where" with CURSOR sql statement,inside a procedure . But, it doesn't seem to get the value for
the @Where. (I tried with DEBUGGING option of Query Analyzer also).

=============================================
SET @Where = ''
if IsNull(@Input1,NULL) <> NULL
Set @Where = @Where + " AND studentid='" + @input1 +"'"

if isnull(@Input2,NULL) <> NULL
Set @Where = @Where + " AND teacherid =' " + @Input2 +"'"

DECLARE curs1 CURSOR SCROLL
FOR SELECT
firstname
FROM
school
WHERE
school ='ABC' + @where
=============================================
Please check my SQL Above and Could somebody tell me how can I attach the VARIABLE with CURSOR sql statement ?

Thanks !

View Replies !   View Related
How To Use @tablename
i need to develop a stored procedue, in ehich i have to use variable table name.. as Select * from @tableName but i m unable to do so..
it says u need to define @tablename

heres da code
CREATE PROCEDURE validateChildId
(
@childId int,
@tableName varchar(50),
@fid int output
)
AS
(
SELECT @fid=fid FROM @tableName
where @childId= childId
)

if @@rowcount<1
SELECT
@fid = 0
GO

View Replies !   View Related
Tablename
Hi,
i just migrated an database from oracle to sql server 2005 with the migration tool from microsoft (v3). the migration tool works only with uppercase table and column names, but i need them in lower case. is there a way to modify the names of tables and columns with t-sql to lower case?
Thx
Frank

View Replies !   View Related
&<Tablename&> . . &<ColumnName&>
Hi,
What is the difference between
<Tablename> . . <ColumnName> and <Tablename> . <ColumnName>
this syntax we are using in Storedprocedures.
In SQL 2000, <Tablename> . . <ColumnName> is not working

Eg:
Tablename =a
Column Name = b
Difference between a..b and a.b

Can anyone let me know.

TIA,
Ravi

View Replies !   View Related
Using Parameter In SP As Tablename
Does anyone know the syntax to reference a table in a stored procedure using a parameter? I'm trying to make a stored procedure that will "SELECT INTO" a table as the passed parameter. I keep getting an syntax error when I use this:

CREATE PROCEDURE make_table
@tablename varchar(20)
AS
SELECT * FROM old_table
INTO @tablename

I've tried it several ways, but I can't get it to work, and I haven't found any examples of this anywhere. Thanks in advance for any tips.

View Replies !   View Related
SET IDENTITY_INSERT Tablename ON
I use IDENTITY(1,1)
and the help says:
SET IDENTITY_INSERT tablename ON

But if the table is an variable(virtual) table how to make it work?


Like this:
DECLARE @TEMPtable table ( ID int Identity(1,1),invar nvarchar(255)


It does not work with
SET IDENTITY_INSERT @TEMPtable ON

I get this

'Incorrect syntax near '@TEMPtable '



View Replies !   View Related
SET IDENTITY_INSERT Tablename ON
I know there has already been a thread on this, but I want to push some about it.

In SQL Server, there is a command "SET IDENTITY_INSERTtablename ON".

That allows you to update IDENTITY columns, or do INSERTs that include IDENTITY columns. Although a work-around was given for this in the other thread (reset the IDENTITY seed to the desired value before each INSERT), that is a major pain.

In my database, I have a table that tracks charitable donors. They have a donornum, that is an IDENTITY column, and a year. Together, the donornumand the yearform the primary key. Each year end, a copy is made of all donor records in one year, to form the next year's donors. They have to keep the same donornum, but they get a new year value of course. Adding new donors uses the donornum normally, incrementing the IDENTITY donornum value.

The advantage of this is that you can match up one year's donors with the previous year's, by joining on donornum. But I need there to be separate records, so they can have separately updated addresses, annual pledge amounts, etc.

Is there any way the SET IDENTITY_INSERT feature can be added to SQL Everywhere, or some other approach can be found that is less laborious than the existing work-around? (The problem with it is that if you have hundreds of donors to copy, you have to do one ALTER TABLE to reset the identity seed for each donor insert for the new year.)

Thanks.

View Replies !   View Related
Select * From Tablename Where Column1 In ()
Hi to all ,           I write the query like belwodeclare @userid nvarchar(20)set @userid ='6,8'print @useridselect * from user_master where user_id in (@userid)Here user_id  is int datatype, when i execute this query then meet the error as  6,8Msg 245, Level 16, State 1, Line 4Syntax error converting the nvarchar value '6,8' to a column of data type int. How to do this.? Anybody answer me,... 

View Replies !   View Related
Dynamic Tablename Issue
Dear all:
I have a storedprocedure for db loader form some temp tables.
my storedprocedure as the following:

declare c cursor
for
select * from @tablename
open c
fetch c into ....

but I get an error for declare cursor for dynamic tablename,

did sql server has some BIF to evaluate the variable for table name?

thanks for your kindly assistance.

regards,

Stanley Huang

View Replies !   View Related
Invalid Object Name #tablename#
Hello, I am quite frustrated at this point.

I have used DTS to transfer objects from one SQL Server to another.

The DTS operation goes as planned, and all tables, SP & views are there....
Accessing the database returns this result when used in ASP Script:

"Microsoft OLE DB Provider for SQL Server error '80040e37'

Invalid object name #tablename#

('tablename' being any table in the database)
The source database is identical to the destination database, and the script works fine on the original database/website.

Please help. Ive been at this for a week and cant find where this could possibly fail.

Thanks.

Steve

View Replies !   View Related
How Do I Select Tablename In Subquery
hi all how are you today i am not good because i can't set this variable in this query please read this thanks

create table aaa1 (id_no int, name varchar(20))
go
create table aaa2 (id_no int, name varchar(20))
go

insert into aaa1 values (1,'rahmi')
insert into aaa1 values (2,'atilganer')
insert into aaa1 values (3,'hasan')

insert into aaa2 values (4,'rahmi')
insert into aaa2 values (5,'atilganer')
insert into aaa2 values (6,'hasan')

/* declaring any numeric variable*/
declare @id_no_var int
/* and set variable to max table name's (aaa2 table in this example)
id_ no column*/

set @id_no_var =
(select max(id_no) from
(select max(name) insqlhelp from
sysobjects where name like 'aa%') insqlhelp2 )


this query return:

Server: Msg 207, Level 16, State 3, Line 3
Invalid column name 'id_no'.

this error returned in second

View Replies !   View Related
How Do I Select Tablename In Subquery
hi all how are you today i am not good because i can't set this variable in this query please read this thanks

create table aaa1 (id_no int, name varchar(20))
go
create table aaa2 (id_no int, name varchar(20))
go

insert into aaa1 values (1,'rahmi')
insert into aaa1 values (2,'atilganer')
insert into aaa1 values (3,'hasan')

insert into aaa2 values (4,'rahmi')
insert into aaa2 values (5,'atilganer')
insert into aaa2 values (6,'hasan')

/* declaring any numeric variable*/
declare @id_no_var int
/* and set variable to max table name's (aaa2 table in this example)
id_ no column

note :insqlhelp and insqlhelp2 is an alias for tablename query (recommended from sql help
*/

set @id_no_var =
(select max(id_no) from
(select max(name) insqlhelp from
sysobjects where name like 'aa%') insqlhelp2 )


this query return:

Server: Msg 207, Level 16, State 3, Line 3
Invalid column name 'id_no'.

this error returned because max(id_no) column is absent

if you are run
select * from
(select max(name) insqlhelp from
sysobjects where name like 'aa%') insqlhelp2

this query return that

insqlhelp ---------------
aaa2

(1 row(s) affected)

this mean that my table name query returned table name but i cant use this name in any other query (i think because of daclaring alias "insqlhelp, insqlhelp2")

other way is

set to any other text variable to table name,
and concetanate to query,
and execute with exec

but in this way you cant set to my int variable

please help me thanks for all

hra

View Replies !   View Related
Pass &<tablename&> As Parametre In StoredProcedure
Hi Experts,I m using a stored procedure:set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER PROCEDURE [dbo].[SP_Table_SELECT](    @TableName VarChar)ASset nocount onset ansi_warnings offDECLARE @sql varchar(2000)SET @sql = 'SELECT* FROM (' +@TableName + ')'EXEC (@sql)when I run It it shows return value = o and Query Completed with ErrorsThere are data in My Table. Help Plzzzz!!!!!! 

View Replies !   View Related
Declaring A Tablename In A Sproc As A Parameter
HiCan someone please shed some light as to how this can be done. With the below sql statement as you can see I don't want to declare the table name but would rather the table name be passed in via a parameter. I've tried declaring it as a varchar parameter but it doesnt seem to like it if I don't add a valid table name. Any ideas how this can be done. Thanks.select * from @tableName where condition = condition

View Replies !   View Related
Sql_help Tablename (Wrong Result)
Hi All,

I am facing a unique problem, In my DB all tables which has nvarchar datatype columns. When i see any table in EM design mode it shows length of navrchar datatype column correct.

But if i see same table through QA using sp_help tablename
then it will show length of my nvarchar column just double.

when i see all my nvarchar columns in syscolumns it will display the length of my nvarchar columns just double then actual.

i dont know where exactly the problem. Because of that my tester are getting wrong table information through my data dictionary whic i created using sysobects,syscolumns,sysproperties.

can anybody tell where is the problem exaqctly ?

View Replies !   View Related
Passing Tablename To Proc From Trigger
--------------------------------------------------------------------------------

I want to fire a trigger which calls a stored proc, passing the name of the table the trigger is defined on to the proc without having to hardcode.

Can it be done ?

trigger x on table y
for update
as
declare @table_name
select @table_name = get underlying table_name 'y' from somewhere
exec stored proc (@table_name)

where do I find the name of the table the current trigger is defined on ?

thanks

View Replies !   View Related
Creating A Tablename Based On A Date
Hi,

I am new to SQL Server, and this may be a silly problem .. but here goes!

I need to create tables based on the date ie FEB2000 for EOM reports, I thought I may be able to do it by

1) Check if Table Exists, if so fop it
2) Recreate Table
3) Populate it with data

Unfortunately I'm still on step 1 <sad look>

--->

CREATE PROCEDURE TEST AS

DECLARE @TableName varchar(50)

SET @Tablename = 'FEB2000'

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @Tablename)
DROP TABLE @tablename
GO

<---

I would of course hand the table name as a variable, but for testing purposes used a set variable.

Thanks in Advance

View Replies !   View Related
Ouput Parameter In &#34;select * From @tablename&#34;
Hi all.
I want something like this :
create proc myProc ( @num int ,@name varchar(80))
as
select @num = max(field1) from @name


but there is a problem that the @name is a varchar and the error is :
Server: Msg 170, Level 15, State 1, Procedure sp_myProc, Line 3
Line 3: Incorrect syntax near '@name'.

I tried
select @num = max(field1) from object_id(@name)
and the error was the same

is ther another way than :
exec ("declare @num int select @num = max(field1) insert into anotherTable values( @num) " ) ?
thx
Eyal Peleg

View Replies !   View Related
Query For Exists Dbname..tablename...
I have a table that lists available dbs.

num(int) region(vchar) dbname(vchar)
1 |west |us_west
2 |east |us_east
3 |central |us_central
4 |cleanup |cleanup
5 |dana |test_setup


I want to look for the existance of the calendar table in all db's that start with "us".

declare @dbname

select region ,dbname from ports..tbldbs where dbname like 'us_%'
... fetch next into
@dbname
....

I don't have it shown here, but I have been able to include @dbnme into the following problem query, replacing dbo with +dbname+'..[calenar...

**The problem is doing something and being able to set the true false or value of a var....

set @checkstr = 'if exists (select *
from sysobjects
where id = object_id(N''['+'dbo].[CALENDAR]'')
and OBJECTPROPERTY(id, N'+'''IsUserTable'''+') = 1)'

exec (@checkstr)

** end here's the problem

I want to change this from informational in the QA to actually be able to do something, now that I've said "If exists...." do something or not.

*** This is my outer loop/cursor, the inner does a calendar query and the inner-most does a per-record update of my sales table based on calendar forecasted sales data. Not all db's starting with "us" have a calendar table, I want to skip these.

But I've not found a way to set a var or other wise. In essense I want to loop through this table.

TIA

JeffP...

View Replies !   View Related
Stored Procedures, Parameter, Tablename
Hello,

I would like to pass into a stored procedure the name of a table. Following code does not work.
USE pubs
GO
CREATE PROC test1
@tableName varchar(40)
AS
SELECT * FROM @tableName
EXEC test1 @tableName='authors'

I would appreciate it very much if someone could help me with this.

Thanks



View Replies !   View Related
Dynamic Cursor Versus Forward Only Cursor Gives Poor Performance
Hello,I have a test database with table A containing 10,000 rows and a tableB containing 100,000 rows. Rows in B are "children" of rows in A -each row in A has 10 related rows in B (ie. B has a foreign key to A).Using ODBC I am executing the following loop 10,000 times, expressedbelow in pseudo-code:"select * from A order by a_pk option (fast 1)""fetch from A result set""select * from B where where fk_to_a = 'xxx' order by b_pk option(fast 1)""fetch from B result set" repeated 10 timesIn the above psueod-code 'xxx' is the primary key of the current Arow. NOTE: it is not a mistake that we are repeatedly doing the Aquery and retrieving only the first row.When the queries use fast-forward-only cursors this takes about 2.5minutes. When the queries use dynamic cursors this takes about 1 hour.Does anyone know why the dynamic cursor is killing performance?Because of the SQL Server ODBC driver it is not possible to havenested/multiple fast-forward-only cursors, hence I need to exploreother alternatives.I can only assume that a different query plan is getting constructedfor the dynamic cursor case versus the fast forward only cursor, but Ihave no way of finding out what that query plan is.All help appreciated.Kevin

View Replies !   View Related
Could Not Complete Cursor Operation Because The Set Options Have Changed Since The Cursor Was Declared.
I'm trying to implement a sp_MSforeachsphowvever when I call sp_MSforeach_worker
I get the following error can you please explain this problem to me so I can over come the issue.


Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 31

Could not complete cursor operation because the set options have changed since the cursor was declared.

Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 32

Could not complete cursor operation because the set options have changed since the cursor was declared.

Msg 16917, Level 16, State 1, Procedure sp_MSforeach_worker, Line 153

Cursor is not open.

here is the stored procedure:


Alter PROCEDURE [dbo].[sp_MSforeachsp]

@command1 nvarchar(2000)

, @replacechar nchar(1) = N'?'

, @command2 nvarchar(2000) = null

, @command3 nvarchar(2000) = null

, @whereand nvarchar(2000) = null

, @precommand nvarchar(2000) = null

, @postcommand nvarchar(2000) = null

AS

/* This procedure belongs in the "master" database so it is acessible to all databases */

/* This proc returns one or more rows for each stored procedure */

/* @precommand and @postcommand may be used to force a single result set via a temp table. */

declare @retval int

if (@precommand is not null) EXECUTE(@precommand)

/* Create the select */

EXECUTE(N'declare hCForEachTable cursor global for

SELECT QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME)

FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_TYPE = ''PROCEDURE''

AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME)), ''IsMSShipped'') = 0 '

+ @whereand)

select @retval = @@error

if (@retval = 0)

EXECUTE @retval = [dbo].sp_MSforeach_worker @command1, @replacechar, @command2, @command3, 0

if (@retval = 0 and @postcommand is not null)

EXECUTE(@postcommand)

RETURN @retval



GO


example useage:


EXEC sp_MSforeachsp @command1="PRINT '?' GRANT EXECUTE ON ? TO [superuser]"

GO

View Replies !   View Related
TableName Property In Conditional Multiple Recordset
Hi everyone,

I'm using a stored procedure in SQL Server to generate multiple recordsets. The recordset are however generated conditionally. create procedure GetData
(
@a bit
@b bit
)

Select * from Customers

IF(@a=1)
Select * from Products

IF(@b=1)
Select * from Details ------------Issue:The adapter returns the table named as Table, Table1(optional) and Table2(optional). It might be that Table1 is products or Table1 is Details.How can I map my tablename property in this scenario where the sequence of tables is not known.   

View Replies !   View Related

Copyright 2005-08 www.BigResource.com, All rights reserved