Transact SQL :: Declaring Cursor Causing Select Statements Included Within A Function Cannot Return Data To Client?
Sep 29, 2015
I cannot find the problem with this function.
ALTER function [Event].[DetermineTrackTime](@TrialID varchar(max)) returns int as
begin
Declare @ret int;
Declare @EnterVolumeTime int;
Declare @ExitVolumeTime int;
Declare @StartTrackTime int;
[code]....
I am getting the following error on line 75:
Select statements included within a function cannot return data to a client.
This is happening when declaring TrackUpdateCursor
The compiler has no problem with the VolumeTimesCursor. What is causing this and what can I do about it?
I have a few tables I am trying to join to create a report. Everything was working fine until I tried to add an aggregate Sum function to a column (MaxCap) in table ctfBarn.Â
Hello. I a SP with 2 select statements. If the first returns no results it does the second select. This works fine if I test it on the sql server. But when I try it in a webform using a sqldatasource it only returns a result if the first Select returns rows. If not It is an empty set (where I know it should not be. It returned resultrs on the SQL server). This is the code:1 ALTER PROCEDURE [SPName] 2 3 @SearchFor varchar(150) -- search string to compare with 4 5 AS 6 7 SELECT p.UserName, 8 9 (Select ImageName from ProfileImages Where IsMainImage = 1 And ProfileID = p.ProfileID) as ImageName, 10 11 (SELECT COUNT(*) FROM auditions WHERE UserID = p.ProfileID) AS AuditionCount 12 13 FROM Profiles p WHERE UserName = @SearchFor 14 15 IF (@@ROWCOUNT = 0) 16 17 Begin 18 19 SELECT p.UserName, 20 21 (Select ImageName from ProfileImages Where IsMainImage = 1 And ProfileID = p.ProfileID) as ImageName, 22 23 (SELECT COUNT(*) FROM auditions WHERE UserID = p.ProfileID) AS AuditionCount, 24 25 DIFFERENCE(p.UserName, @SearchFor) as Similarity 26 27 FROM profiles p WHERE SOUNDEX(p.UserName) = SOUNDEX( @SearchFor ) 28 29 ORDER BY Similarity 30 31 END 32 33 RETURN Now If i change the SP to test it to the code below, it does work in both the webform and on the sql server: 1 ALTER PROCEDURE [SPName] 2 3 @SearchFor varchar(150) -- search string to compare with 4 5 AS 6 7 DECLARE @RowCount int 8 SET @RowCount = 0 9 10 IF (@ROWCOUNT = 0) 11 12 Begin 13 14 SELECT p.UserName, 15 16 (Select ImageName from ProfileImages Where IsMainImage = 1 And ProfileID = p.ProfileID) as ImageName, 17 18 (SELECT COUNT(*) FROM auditions WHERE UserID = p.ProfileID) AS AuditionCount, 19 20 DIFFERENCE(p.UserName, @SearchFor) as Similarity 21 22 FROM profiles p WHERE SOUNDEX(p.UserName) = SOUNDEX( @SearchFor ) 23 24 ORDER BY Similarity 25 26 END 27 28 RETURNOnce again, Everything works as it should (for both versions), if I am testing it directly on the SQL server. Can anyone Help me on this? I am stumped and cannot find any answers about this.
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.
I need to do something relatively simple…I need to update a table using a cursor. (I may have to create astored procedure for doing this…)I need to declare an update cursor, fetch the cursor and update thedata (and presumably close the cursor and de-allocate it…The update query is as follows… Would anyone there know how todeclare the cursor for update and use it?UPDATE ASET A.Field1 =(SELECT B.Field1FROM B INNER JOIN A ON A.id = B.id)I need to know how to declare the cursor and fetch it.Can anyone give me an example of the code I need for the SQL Server?Thanks!
HI, WHILE DECLARING A CURSOR TO SELECT RECORDS FROM A TABLE WE NORMALLY WRITE :-
DECLARE CUR_NAME CURSOR FOR SELECT * FROM CLEANCUSTOMER
BUT SAY, IF I HAVE WRITTEN A SIMPLE PROCEDURE CALLED AS MY_PROC :-
CREATE PROCEDURE MY_PROC AS SELECT A.INTCUSTOMERID,A.CHREMAIL,B.INTPREFERENCEID,C.CHR PREFERENCEDESC FROM CLEANCUSTOMER A INNER JOIN TRCUSTOMERPREFERENCE03JULY B ON A.INTCUSTOMERID = B.INTCUSTOMERID INNER JOIN TMPREFERENCE C ON B.INTPREFERENCEID = C.INTPREFERENCEID ORDER BY B.INTPREFERENCEID
WHICH IS RUNNING FINE AND GIVING ME THE REQUIRED DATA WHILE EXECUTING THE PROCEDURE :-
EXEC MY_PROC
BUT IF I WANT TO CALL THIS PROCEDURE MY_PROC WHILE DECLARING A CURSOR :-
I AM USING :-
DECLARE CHK_CUR CURSOR FOR SELECT * FROM MY_PROC
WHICH IS GIVING AN ERROR "Invalid object name 'MY_PROC'."
AND IF I USE :-
DECLARE CHK_CUR CURSOR FOR EXEC MY_PROC
WHICH IS GIVING AN ERROR "Incorrect syntax near the keyword 'EXEC'".
AND IF I USE :-
DECLARE CHK_CUR CURSOR FOR CALL MY_PROC
WHICH IS GIVING AN ERROR "Incorrect syntax near 'CALL'. "
IS THERE ANY WAY BY WHICH I CAN FETCH RECORDS FROM THE STORED PROCEDURE? HOW DO I DECLARE THE PROCEDURE WHILE WRITING THE CURSOR PLS HELP.
I NEED THIS URGENTLY, I HAVE TO USE THE CURSOR TO FETCH THE RECORDS FROM THE SP,THAT'S HOW THEY WANT IT.I CAN'T HELP IT AND I DON'T KNOW HOW
I have a database with two tables (Table1 and Table2)
looking for something like:
Table1: +----+-------+----------+ | id  | Name | email   | +----+-------+----------+ |  1 | name1 | mail1   | |  2 | name2 | mail2   | |  3 | name3 | mail3   | |  4 | name4 | mail4   | |  5 | name5 | mail5   | |  6 | name6 | mail6   | +-- +-------+----------+
Table2: +----+------------+---------+------+ | id  | table1_ID | fee    | type | +---+-------------+---------+------+ |  1 |   1      | 20000  |   1 | |  2 |   3      | 1000   |   1 | |  3 |   1      | 10000  |   1 | |  4 |   3      | 1000   |   1 | |  5 |   5      | 500    |   1 | |  6 |   5      | 500    |   2 | |  7 |   1      | 60000  |   2 | |  8 |   2      | 1000   |   2 | +----+------------+---------+-------+
i want the query that return:
+--------+-------+---------------------------------+---------------------------------+--------+ | name  | email | a:sum(fee) where type=1  | b:sum(fee) where type=2  |  b/10  | +--------+-------+---------------------------------+---------------------------------+--------+ |name1 | mail1 |     30000             |    60000             |  6000| |name2 | mail2 |      0                |     1000              |  100   | |name3 | mail3 |      2000             |      0                |  0    | |name4 | mail4 |      0                |      0                |  0    | |name5 | mail5 |      500              |     500              |  50   | |name6 | mail6 |      0                |      0                |  0    | +--------+-------+---------------------------+---------------------------------------+---------+
I am not sure if i am looking correctly at the deadlocks but i see deadlocks between two select statements.These statements are being run through an application. Below is the table schema from where the select is being performed
CREATE TABLE [dbo].[CMS_LOCKS7]( [PARENTID] [int] NOT NU, Â --we have a non clustered index on this column [CHILDID] [int] NOT NULL, --we have a non clustered index on this column [ISMEMBER] [int] NOT NULL, -- we have a non clustered index on this column [ORDINAL] [int] NULL,-- we have a non clustered index on this column
I'm trying to return a query based on the dateadd function. I have a column in the database called date_added which is am successfully using the the DATEADD function above as date1. The Var1 variable I need to populate from the database too from a column called life_span which is an int data type. The error I get is An expression of non-boolean type specified in context where a condition is expected near select
My query is as follows: select guid, dateadd(day,life_span,date_added) as datepayday. From User_table
We were asked to create an SQL function to return a unit price based on various criteria. The function works fine except for the tiered pricing (use of BillingPriceTable) calculation. What we need to do is break up the total quantity passed to the function and return the total of prices found. In our example, we passed a quantity of 9,721 units and need to return a total price of 231.92 using the table below.
Low Qty    High Qty    Fee       Actual Qty       Price 0                 7500       0.025           7500          187.50 7501           15000       0.020           2221         44.42
Below is the table definition that we have to work with (ugghh).
What we have so far is shown below. The columns that start with bdxx are the "High Qty" values and the columns that start with prxx are the price for that quantity range. So, the current SELECT is shown below and it returns the price based on the entire qty of 9,721 and returns a unit price of 0.020 and should return 0.023857628
The current SELECT is shown below and is returning 0.020 which is the fee for the total rather than calculating the fee twice, once for the 0-7500 and again for the 7501-15000 (actually 7501-9721). Two things came to mind, one was a WHILE loop and the other was possibly a ranking function of some sort.Â
ALTER FUNCTION [dbo].[fn_GetPrice] ( @plincdvarchar(3), @pgrpcodevarchar(4), @pitmcodevarchar(4), @qtydecimal(10,1) = 1, @corpnbrvarchar(9) )
I had to enable identity_insert on a bunch of tables and I have already done that. Now I need to modify my insert into select * from statements to include column list names along with identity columns for select as well as insert statements. The DDL is same but they are both different databases.There are almost 100 tables that it needs to be modified. Is there a way we can generate scripts for insert and select for each individual table along with their column lists including the identity column?
I've to write a function to return a comma delimited values from a table columns
If a table has Tab1 ( Col1,Col2,Col3).
E.g. as below ( the columnName content I want to use as columns for my pivot table
CREATE FUNCTION [RPT].[GetListOfCol] ( @vCat NVARCHAR(4000) ) RETURNS @PList AS BEGIN SELECT @PList += N', [' + [ColumnName] +']' FROM [ETL].[TableDef] WHERE [IsActive] = 1 AND [Category] = @vCat RETURN; END;
I want out put to be as below, I am getting this output from the select query independently by declaring @Plist variable and passing @vcat value, now I want it to be returned from a function when called from a select query output ,Colum1,column2,....
I have a stored procedure that selects the unique Name of an item from one table.Â
SELECT DISTINCT ChainName from Chains
For each ChainName, there exists 0 or more StoreNames in the Stores. I want to return the result of this select as the second field in each row of the result set.
SELECT DISTINCT StoreName FROM Stores WHERE Stores.ChainName = ChainName
Each row of the result set returned by the stored procedure would contain:
ChainName, Array of StoreNames (or comma separated strings or whatever)
I have tables and a function as representated by the code below. The names  for objects here are just for representation and not the actual names of objects. Table RDTEST may have one or multiple values for RD for each PID. So the function GIVERD will return one or multiple values of RD for each value of PID passed to it.
When I run the following query, I get the required result except the rows for CID 500 for which PID is NULL in table T1. I want the rows for CID 500 as well with PID values as NULL.
SELECT  A.CID, A.ANI, A.PID, B.RD FROM T1 AS A CROSS APPLY GIVERD(A.PID) B
Hi i have a cursor in a Stored Procedure. The problem is that it's poiting to the first row and causing an infinite loop on it. How can i stop this and make it go to all rows. Here is my code.
Declare @CountTSCourtesy int Declare @WaiterName nvarchar(100), @CursorRestaurantName nvarchar (100) Declare waiter_cursor CURSOR FOR
SELECT new_waiteridname, new_restaurantname FROM dbo.FilteredNew_CommentCard Where new_dateofvisit between @FromDate and @ToDate and new_restaurantname = @Restaurant Open waiter_cursor FETCH NEXT FROM waiter_cursor into @WaiterName,@CursorRestaurantName While @@FETCH_STATUS=0
BEGIN Exec WaitersCountExCourtesy @WaiterName,@CursorRestaurantName
END Close waiter_cursor Deallocate waiter_cursor END
How do I set a variable to represent all of the data. For example using SELECT * will pull all of the data. Is there any symbol or way to declare and set a variable to do the same exact concept. In my query I have set many different variables which are used later on in my where clause but depending on what information I'm pulling from the data I don't wan the variable to have a specific value and instead pull all the data.
Using TSQL, I have a table that holds filenames of Pictures for products. Different products can be using the same picture. I need to select the filenames for a single product only if it does not exists for a different product.I have tried Where Exists (select FileName From Tbl where
Prod_Id = @var) AND NOT EXISTS(select FileName From Tbl where Prod_Id != @var) In the Select Statement.Â
I am using SQL 2014 RTM (may be it's time to upgrade).
I have the following view:
create view [dbo].[SiriusV_Max4SaleList] as select m.id as Max4SaleId, mt.[Description] as [TypeDescription], CAST(m.[type] as tinyint) as [Type], m.start_time as [StartTime], m.end_time as [EndTime],
[Code] ....
I am thinking I may want to remove CAST for department, category, item later on as I don't really care if these columns would be defined as key for my EF model, but I do want to search by these columns. Anyway, this is my current view.
I executed the following select statement once
select * FROM dbo.siriusv_max4saleList where department like 's%' or category like 's%' or item like 's%'
And I believe I got 29 rows initially. However, when I execute this statement now I'm getting just 13 rows. If I execute just the department like 's%' I am getting 0 rows although I can see in the first result a row where department has s in in.
I guess I keep it here since I've created the message already but now I figured out why I am not getting the expected result. I used the condition like 's%' and not like '%s%' which application is doing.
I am trying to insert a carriage return in the select statement after the web link where I had highlighted code in bold. When I insert a record into the table, I receive the email with the message body is in single line.I need the result to look like this in the message body:
ALTER TRIGGER [dbo].[SendNotification] ON [dbo].[TicketsHashtags] FOR INSERT AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from
I have a stored proc which evaluates a table of 'tasks' to return the task with the highest priority for a given user - at the same time, once that task has been found - I want to allocate that user to the task.Â
I have created a stored proc that works...however I'm sure this requirement /pattern is common & I would like some best practice for this pattern of update and select within a transaction.
Here is some sample data:
use tempdb; go if OBJECT_ID('Tasks', 'U') is not null drop table tasks; go create table tasks ( TaskId int identity primary key,
[Code] ....
And here's what my current stored proc looks like;
if OBJECT_ID('pGetNextTask', 'P') is not null drop proc pGetNextTask; go create proc pGetNextTask ( @UserID char(10), @TaskID int output )
I have a database which will be generated via script. However there are some tables with default data that need to exist on the database.I was wondering if there could be a program that could generate inserts statments for the data in a table.I know about the import / export program that comes with SQL but it doesnt have an interface that lets you copy the insert script and values into an SQL file (or does it?)
SELECT DATEDIFF(n , LAG(CAST(Date AS DATETIME) + CAST(Time AS DATETIME), 1) OVER ( ORDER BY Date, Time ), Â Â Â Â Â Â CAST(Date AS DATETIME) + CAST(Time AS DATETIME)) Â Â Â FROM [DataGapTest]
Gives the right output:
NULL 1 1 3548 0
However, when I put the statement in a function, I get only zeros as the output. It's as if the lag and current value are always the same (but they are not of course).
CREATE FUNCTION dbo.GetTimeInterval(@DATE date, @TIME time) RETURNS INT AS  BEGIN  DECLARE @timeInterval INT   SELECT @timeInterval = DATEDIFF(n , LAG(CAST(@Date AS DATETIME) + CAST(@Time AS DATETIME), 1) OVER ( ORDER BY Date, Time ),       CAST(@Date AS DATETIME) + CAST(@Time AS DATETIME))    FROM dbo.[DataGapTest]   RETURN @timeInterval  END
I tend to learn from example and am used to powershell. If for instance in powershell I wanted to get-something and store it in a variable I could, then use it again in the same code. In this example of a table order items where there are order_num, quantity and item_prices how could I declare ordertotal as a variable then instead of repeating it again at "having sum", instead use the variable in its place?
Any example of such a use of a variable that still lets me select the order_num, ordertotal and group them etc? I hope to simply replace in the "having section" the agg function with "ordertotal" which bombs out.
select order_num, sum(quantity*item_price) as ordertotal from orderitems group by order_num having sum(quantity*item_price) >=50 order by ordertotal;
I hope someone can answer this, I'm not even sure where to start looking for documentation on this. The SQL query I'm referencing is included at the bottom of this post.
I have a query with 3 select statements joined together like tables. It works great, except for the fact that I need to declare a variable and make it a table within two of those 3. The example is below. You'll see that I have three select statements made into tables A, B, and C, and that table A has a variable @years, which is a table.
This works when I just run table A by itself, but when I execute the entire query, I get an error about the "declare" keyword, and then some other errors near the word "as" and the ")" character. These are some of those errors that I find pretty meaningless that just mean I've really thrown something off.
So, am I not allowed to declare a variable within these SELECT tables that I'm creating and joining?
Thanks in advance, Andy
Select * from
(
declare @years table (years int);
insert into @years
select
CASE
WHEN month(getdate()) in (1) THEN year(getdate())-1
WHEN month(getdate()) in (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) THEN year(getdate())
END
select
u.fullname
, sum(tx.Dm_Time) LastMonthBillhours
, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) lasmosbillingpercentage
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
Month(tx.Dm_Date) = Month(getdate())-1
and
year(dm_date) = (select years from @years)
and tx.dm_billable = 1
group by u.fullname
) as A
left outer join
(select
u.FullName
, sum(tx.Dm_Time) Billhours
, ((sum(tx.Dm_Time))
/
((day(getdate()) * ((5.0)/(7.0))) * 8)) perc
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
tx.Dm_Billable = '1'
and
month(tx.Dm_Date) = month(GetDate())
and
year(tx.Dm_Date) = year(GetDate())
group by u.fullname) as B
on
A.Fullname = B.Fullname
Left Outer Join
(
select
u.fullname
, sum(tx.Dm_Time) TwomosagoBillhours
, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) twomosagobillingpercentage
My goal is to run a bunch of select statements from different tables in one database and have them all insert to the same columns/table in the new database. Do I need a new data source for each statement, or is there a way to run all the statements in one set seeing as they all have the same destination. I keep receiving the SQL statement improperly ended error when trying.
I can't understand why I get 2 different results on running with a Bracket I get 'NULL' and without a bracket I get the declared variable value which is 'Noname'
Below is Query 1:
Declare @testvar char(20) Set @testvar = 'noname' Select @testvar= pub_name FROM publishers WHERE pub_id= '999' Select @testvar
Out put of this query is 'Noname'
BUT when I type the same query in the following manner I get Null-------Please note that the only difference between this query below is I used brackets and Select in the Select@testvar statement
Declare @testvar char(20) Set @testvar = 'noname' Select @testvar=(Select pub_name FROM publishers WHERE pub_id= '999') Select @testvar
I am having issues getting this to work. I have the user login to a page to put in a request for vacation. When they login, I have a label that isn't visible that is equal to their User.Identity.Name. I select the user from the employee table where the username = the label User Identity Name and pull in the emp_id which is the primary key that identifies the user. I need to insert the request into the request table with the emp_id from the select statement, without showing the em_id on the screen. I tried using a hidden field and assigning the emp_id as the value, but it isn't working. Not sure if this is the best way to do this. Really new to ASP.NET 2.0 so I really appreciate any help. Thank you!
I have 2 tables each containing a material type. Table 1 contains material from their 3D application. Table 2 contains material with specific values that is not ours and we cannot rename or edit the data. I need a type of junction or mapping table that can connect the user material to the preset material. for example:
User Material = Wood-MDF Preset Material = MDF Panel
I figured that i would make this table with 3 fields (ID, UserMaterialID, PresetMaterialID).How would i then construct a query view / Stored procedure that would return the Preset data values based on the user material id?
I am trying to use FOR XML under SQL Server 2014 to write out a large XML data set. I want it to look like
<CVS_Member_Add_Change> Â Â <RecordType>3</RecordType> Â Â <Carrier>1266</Carrier> Â Â <MultiBirthCode>0000000</MultiBirthCode> Â Â <MemberType></MemberType>
[Code] ....
That's how it looks when you click on the results of a small subset of the query. Â Just what I want. Â Unfortunately when you try to right click and save it you getÂ
