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
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!
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 the table 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'
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.
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!!
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.
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
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
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...
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????
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
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'
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
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:
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?
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.
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.
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
FETCH NEXT FROM myCursor INTO ...
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
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,
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 ?
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