I 've have a stored procedure that compares fields across databases.
In order to do so it requires 2 values it acquires from 2 tables. The
search is based on the ID of the data owner and a subject:
In order to obtain those values I run a cursor accross my records and
SELECT THEM INTO 2 @variables, which then replace 222222 and PSY101
with dynamic values eg.
--define a cursor etc.etc.
WHILE @@cursor_fetch = 0
BEGIN
--do the cursor call INTO @vars
EXEC proc_evaluate_results @StudentId = @studentID, @Course =
@CourseCode
END
Now,the vars are being passed to the stored procedure and executed OK,
but the cursor gets stuck on the last record and continues to evaluate
it until stopped manually.
If I comment out the EXEC and replace it with eg. PRINT @Course + ' |
' + @CourseCode it runs fine, exiting after the last record.
I'm trying to execute a stored procedure within the case clause of select statement. The stored procedure returns a table, and is pretty big and complex, and I don't particularly want to copy the whole thing over to work here. I'm looking for something more elegant.
@val1 and @val2 are passed in
CREATE TABLE #TEMP( tempid INT IDENTITY (1,1) NOT NULL, myint INT NOT NULL, mybool BIT NOT NULL )
INSERT INTO #TEMP (myint, mybool) SELECT my_int_from_tbl, CASE WHEN @val1 IN (SELECT val1 FROM (EXEC dbo.my_stored_procedure my_int_from_tbl, my_param)) THEN 1 ELSE 0 FROM dbo.tbl WHERE tbl.val2 = @val2
SELECT COUNT(*) FROM #TEMP WHERE mybool = 1
If I have to, I can do a while loop and populate another temp table for every "my_int_from_tbl," but I don't really know the syntax for that.
I'm new to cursors, and I'm not sure what's wrong with this code, it run for ever and when I stop it I get cursor open errors
declare Q cursor for select systudentid from satrans
declare @id int
open Q fetch next from Q into @id while @@fetch_status = 0 begin
declare c cursor for
Select b.ssn, SaTrans.SyStudentID, satrans.date, satrans.type, SaTrans.SyCampusID, Amount = Case SaTrans.Type When 'P' Then SaTrans.Amount * -1 When 'C' Then SaTrans.Amount * -1 Else SaTrans.Amount END
From SaTrans , systudent b where satrans.systudentid = b.systudentid
I have a loop(while) statement I need to run inside a cursor statement. The loop creates records based on a frequency. The cursor and the loop work but the problem is that the cursor only reads the first record, runs the loop, but then ends. I am pasting the code below. Any help appreciated
declare dbcursor cursor for select uniq_id,account_id,created_by,encounter_id, start_date,date_stopped,sig_codes, ndc_id,modified_by from patient_medication where convert(datetime,start_date) = '10/20/2000' and date_stopped is not null open dbcursor fetch next from dbcursor into @uniqid,@account_id,@createid,@entcid, @sdate, @edate ,@sig_code, @ndcid, @modid while (@@FETCH_STATUS <> -1) begin select @freq = SIG.sig_frequency FROM SIG where SIG.SIG_KEY = @sig_code set @hfreq = @freq if @freq = 9 set @freq = 1 set @nodays = datediff(day, @sdate - 1, @edate) while @cnter < @nodays begin while @fcnter < @freq + 1 begin insert into PATIENT_MEDICATION_DISPERSAL_ (uniq_id,account_id, occurance_id, encounter_id, ndc_id, ddate, frequency, sig_code,disp_create_id, disp_mod_id) values (@uniqid,@account_id,@fcnter, @entcid, @ndcid, @sdate, @freq, @sig_code,@createid, @modid ) set @fcnter = @fcnter + 1 set @erdate = @sdate
END if @hfreq = 9 begin set @fcnter = 1 set @sdate = @sdate + 2 Set @cnter = @cnter + 2 end else begin set @fcnter = 1 set @sdate = @sdate + 1 Set @cnter = @cnter + 1 end end end close dbcursor deallocate dbcursor
I try to create a Sproc which will use a cursor to retrieve a few rows from a table. But the cursor part has given me problem. Here it is:
StudentInfo StudentID StudentName DeptID 101 John 10 102 Alex 10 103 Beth 20 ClassInfo ClassID DeptID 901 10 902 10 225 20 I want to create a Sproc which will retreive the student's classes in DeptID 10
Following is the Sproc and cursor:
use master go Create PROCEDURE [dbo].[getEnclishClasses] @StudentID int AS Declare @printInsertStatement nvarchar(100) ECLARE NewRowID int
Declare classCursor CURSOR FOR SELECT ClassID, DeptID FROM [myTest].dbo.ClassInfo WHERE DeptID=(SELECT DeptID FROM [myTest].dbo.StudentInfo WHERE StudentID=@StudentID)
DECLARE @ClassID INT DECLARE @DeptID INT
OPEN classCursor FETCH NEXT FROM classCURSOR INTO @ClassID, @DeptID WHILE (@@FETCH_STATUs=0) BEGIN PRINT 'SET @newID = Scope_Identity()' SET @printInsertStatement= (Select 'INSERT INTO [myTest].dbo.ClassInfo (ClassID, DeptID) Values(' +CONVERT(NVARCHAR (10), @ClassID) + ',' +CONVERT(NVARCHAR (2), @DeptID)+')' FROM [myTest].dbo.StudentInfo WHERE DeptID=(SELECT DeptID FROM [myTest].dbo.StudentInfo WHERE StudentID=@StudentID))
PRINT @printInsertStatement END CLOSE classCursor DEALLOCATE classCursor EXEC getEnclishClasses 101
Here is what I try to get (text with actual data from the table): SET @newRowID = Scope_Identity() INSERT INTO [myTest].dbo.ClassInfo VALUES(901, 10) SET @newRowID = Scope_Identity() INSERT INTO [myTest].dbo.ClassInfo VALUES(902, 10)
Here is what I had got (returning multiple lines, more than number of records I have): Msg 512, Level 16, State 1, Procedure getEnclishClasses, Line 19 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Thanks in advance for your help! Or is it a better way (not using a cursor). Each table has over 5,000 records.
Dear all,i'm facing a problem with my storedprocedure which happened when i ran my web application and reach to the point where my class invoke this storedprocedure,my SP contains a cursor that built his sql according to certain condition, so i put the "SET @cur Cursor For....." inside the if block (definitely i've declared it under AS keyword directly) and this SP is working well inside sql server(I've tested it), BUT when my ASP.net code invoke this SP it gives me the following error : "The Variable @cur does not currently have a cursor allocated to it" repeated as much as there are IF clauses in my SP,Please Help.Regards,
I have an application that needs to create invoices on a daily basis to multiple clients based on orders shipped that day. This is easy to do on the front-end. But how can I do this on the back end in SQL Server. I want to sort orders by Client ID and put all orders belonging to one customer on one invoice. When customer id changes, I change the Invoice ID. Is this possible in SQL? Xcog
Hi there, I need to create a stored procedure that I can pass in an arbitrarily long list of record IDs and have an SQL statement executed for each one. For instance, I want to be able to pass in 1, 2, 5, 78, 100, 216 and have it automatically execute: UPDATE table SET value='blah' WHERE id=1; UPDATE table SET value='blah' WHERE id=2; UPDATE table SET value='blah' WHERE id=5; UPDATE table SET value='blah' WHERE id=78; UPDATE table SET value='blah' WHERE id=100; UPDATE table SET value='blah' WHERE id=216 Can anyone please show me how I would structure a stored procedure like this? Specifically, how would I pass the list of IDs in as a parameter, and how would I iterate through them. Much appreciated!!
A Stored Procedure inserts a record in a base table. I want to add n records (n=1 to 5) into a related table. Another SP (tdAuthorityInsert) is already set up to insert one record into the related table.
So, two questions 1. What is the form of an iterative loop in a Stored Procedure? 2. How do you call a Stored Procedure from inside another?
I have a table with a max key value in it, and another table with a few rows in it. I'm trying to update the two new rows with key values that are iterative from the MAX value in the first table. Could anyone point me to some good LOOP...UPDATE, etc resources or pointers before I go postal?
::grins::
Example: first table has a key field, last value in it is say 1000. The second table has two records. They need keys too, and those keys need to start at the last value in the first table + 1, so 1001, and end on the last row of the second table, or, say 1002. I cannot figure out how to read the last value of the first table, and create an update loop to iteratively update the key value in the second table based on the max value of the first, and looping based on the number of records in the second, in this case 2.
I populate the first table with a litst of stores that offer all desired items.
I populate the second table with a list of vendors, the item is, and cost avaiable at each of the stores in the first table.
What I would like is to output all possible the store and vendor combos ordered by combined price.
So, for instance, I have 3 products, A B and C. Store X has A and B by vendor G, and A B and C by vendor H. I want the output to have all iterations of (Store, Product, Vendor, Price) grouped in order of total price. So...
X A G X B G X C H
X A G X B H X C H
X A H X B G X C H
X A H X B H X C H
ordered by each group's combined price.
For some reason, I can't get this straight in my head. Must need more coffee.
I have a synchronous script component and have added 5 fields to the output (field1, field2... field5). Can I iterate those fields?
In sudo language, I'd like to do:
Code Snippet
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) dim i as integer = 1 while i < 6 row.field[i] = "somevalue: " + i.ToString end while end sub
Somehow, I'd like to be able to do that without specifying each field individually:
For inserting current date and time into the database, is it more efficient and performant and faster to do getDate() inside SQL Server and insert the value OR to do System.DateTime.Now in the application and then insert it in the table? I figure even small differences would be magnified if there is moderate traffic, so every little bit helps. Thanks.
How do I change the color of the task icon back to green? I have and FEL with tasks in it that occessionally fail. The error is trapped to allow the container to continue processing. I would like to change the color of the icon back to green on the next successful iteration of a task but I haven't found a way to do it.
Just wonder whether is there any indicator or system parameters that can indicate whether stored procedure A is executed inside query analyzer or executed inside application itself so that if execution is done inside query analyzer then i can block it from being executed/retrieve sensitive data from it?
What i'm want to do is to block someone executing stored procedure using query analyzer and retrieve its sensitive results. Stored procedure A has been granted execution for public user but inside application, it will prompt access denied message if particular user has no rights to use system although knew public user name and password. Because there is second layer of user validation inside system application.
However inside query analyzer, there is no way control execution of stored procedure A it as user knew the public user name and password.
Looking forward for replies from expert here. Thanks in advance.
Note: Hope my explaination here clearly describe my current problems.
STATIC Defines a cursor that makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in tempdb; therefore, modifications made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not allow modifications
It say's that modifications is not allowed in the static cursor. I have a questions regarding that
Static Cursor declare ll cursor global static for select name, salary from ag open ll fetch from ll
while @@FETCH_STATUS=0 fetch from ll update ag set salary=200 where 1=1
close ll deallocate ll
In "AG" table, "SALARY" was 100 for all the entries. When I run the Cursor, it showed the salary value as "100" correctly.After the cursor was closed, I run the query select * from AG.But the result had updated to salary 200 as given in the cursor. file says modifications is not allowed in the static cursor.But I am able to update the data using static cursor.
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
I'm trying to implement a sp_MSforeachsp howvever 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
DECLARE DBCur CURSOR FOR SELECT U_OB_DB FROM [@OB_TB04_COMPDATA]
OPEN DBCur FETCH NEXT FROM DBCur INTO @DBNAME
WHILE @@FETCH_STATUS = 0 BEGIN
SELECT @SQLCMD = 'SELECT T0.CARDCODE, T0.U_OB_TID AS TRANSID, T0.DOCNUM AS INV_NO, ' + + 'T0.DOCDATE AS INV_DATE, T0.DOCTOTAL AS INV_AMT, T0.U_OB_DONO AS DONO ' + + 'FROM ' + @DBNAME + '.dbo.OINV T0 WHERE T0.U_OB_TID IS NOT NULL' EXEC(@SQLCMD) PRINT @SQLCMD FETCH NEXT FROM DBCur INTO @DBNAME
END
CLOSE DBCur DEALLOCATE DBCur
Part 2
SELECT T4.U_OB_PCOMP AS PARENTCOMP, T0.CARDCODE, T0.CARDNAME, ISNULL(T0.U_OB_TID,'') AS TRANSID, T0.DOCNUM AS SONO, T0.DOCDATE AS SODATE, SUM(T1.QUANTITY) AS SOQTY, T0.DOCTOTAL - T0.TOTALEXPNS AS SO_AMT, T3.DOCNUM AS DONO, T3.DOCDATE AS DO_DATE, SUM(T2.QUANTITY) AS DOQTY, T3.DOCTOTAL - T3.TOTALEXPNS AS DO_AMT INTO #MAIN FROM ORDR T0 JOIN RDR1 T1 ON T0.DOCENTRY = T1.DOCENTRY LEFT JOIN DLN1 T2 ON T1.DOCENTRY = T2.BASEENTRY AND T1.LINENUM = T2.BASELINE AND T2.BASETYPE = T0.OBJTYPE LEFT JOIN ODLN T3 ON T2.DOCENTRY = T3.DOCENTRY LEFT JOIN OCRD T4 ON T0.CARDCODE = T4.CARDCODE WHERE ISNULL(T0.U_OB_TID,0) <> 0 GROUP BY T4.U_OB_PCOMP, T0.CARDCODE,T0.CARDNAME, T0.U_OB_TID, T0.DOCNUM, T0.DOCDATE, T3.DOCNUM, T3.DOCDATE, T0.DOCTOTAL, T3.DOCTOTAL, T3.TOTALEXPNS, T0.TOTALEXPNS
my question is, how to join the part 1 n part 2? is there posibility?
I having a difficult time here trying to figure out what to do here.I need a way to scroll through a recordset and display the resultswith both forward and backward movement on a web page(PHP usingADO/COM)..I know that if I use a client side cursor all the records get shovedto the client everytime that stored procedure is executed..if thisdatabase grows big wont that be an issue?..I know that I can set up a server side cursor that will only send therecord I need to the front end but..Ive been reading around and a lot of people have been saying never touse a server side cursor because of peformance issues.So i guess im weighing network performance needs with the client sidecursor vs server performance with the server side cursor..I am reallyconfused..which one should I use?-Jim
This is on Sybase but I'm guessing that the same situation would happen on SQL Server. (Please confirm if you know).
I'm looking at these new databases and I'm seeing code similar to this all over the place:
if not exists (select 1 from dbo.t1 where f1 = @p1) begin select @errno = @errno | 1 end
There's a unique clustered in dex on t1.f1.
The execution plan shows this for this statement:
FROM TABLE dbo.t1 EXISTS TABLE : nested iteration. Table Scan. Forward scan. Positioning at start of table.
It's not using my index!!!!!
It seems to be the case with EXISTS statements. Can anybody confirm?
I also hinted to use the index but it still didn't use it.
If the existence check really doesn't use the index, what's a good code alternative to this check?
I did this and it's working great but I wonder if there's a better alternative. I don't really like doing the SET ROWCOUNT 1 and then SET ROWCOUNT 0 thing. SELECT TOP 1 won't work on Sybase, :-(.
SET ROWCOUNT 1 SELECT @cnt = (SELECT 1 FROM dbo.t1 (index ix01) WHERE f1 = @p1 ) SET ROWCOUNT 0
When I use EXEC in a stored procedure ( after building complex option logic) it produces an returns an error of 'Access denied' on the underlying tables. All objects are dbo owned and execute permission has been given to all users. Can ant one help? Rob
When using a SP for getting a recordset is there any issues with using exec like in: rs.open "exec spWhatever"... Should I use rs.open "spWhatever" or does it really matter performance wise on the SQL server?
declare @TableName Varchar(255);--Just For Testing---DELETE!! declare @Filename varchar(255); --Store Distinct filename declare @DSNo Varchar(255);-- Use 'set' to execute Var TableName declare @SumUnits Varchar(255); --Use 'set' to calculate sum of units declare @SumValue Varchar(255); Set @TableName = 'TrDs01' -- Testing Only--DELETE!!
------------------------Set Statements using @TableName Var------------------------------------------
Set @DSNo = 'select distinct DataSupplierNo from ' + @TableName Set @SumUnits = 'select sum(Units) from ' + @TableName Set @SumValue = 'Select sum(Value) from ' + @TableName
Insert into TransactionMaster([FileName],DataSupplierNo,ImportFileRecordID,FileLoadDate, UnitsSum,ValueSum,RecordCount)
Select(@Filename),(exec(DSNo)), ................
Just the Bold and underlined bit "exec(DSNo)"..... is this doable in some way? can i use exec to retrieve the value to insert to data supplier. As far as i know i have to do it like this because im using a variable as the table name...
I need help understanding the syntax of the "exec sql" statement.
i am looking at code that build an sql string such as
sql="exec SOMETHING Session("id")"
or something like that.
then, there is
conn.execute(sql)
My question is the "SOMETHING" in the sql statement...is what? I know it is user defined (object or variable or such), but what exactly is it? i look through the rest of the code and don;'t see SOMETHING defined elsewhere.
i am not sure if i am asking the question right. i don't understand what the SOMETHING is doing, or why it is there.
i understand the this statement will delete a record, but how does it handle "SurveyDelete", how does it know what the is when it is not defined anywhere else in the code?
Hi, I have an sql query like this :DECLARE @TableName varchar(200), @ColumnName varchar(200), @EmployeeID varchar(200), @Result varchar(200);SET @TableName = 'Customer';SET @ColumnName = 'First_Name';SET @CustomerID = 28;-- This line return ErrorSET @Result = EXEC ('select' + @ColumnName + ' from ' + @TableName + ' where Recid = ' + @CustomerID + '');Print @Result; I am pretty sure the SELECT statement in EXEC will only return 0 or 1 record. But how to capture result from EXEC? Thanks
