SQL 2012 :: Table (with Zero Rows) Not Returning Any Result
Jun 26, 2014
I am trying to run queries on a table (table has zero rows). Inspite of giving 0 rows returned the query keeps on running and I have to cancel it. I tried inserting a dummy row into the table but even the insert operation is taking too long.Every query which I hit on the table just keeps on running without giving any result.
But this is not the case with other tables in the database.They are all running fine giving proper results. But this one table is behaving funny.
I've been tearing my hair out over this UDF. The code works within astored procedure and also run ad-hoc against the database, but does notrun properly within my UDF. We've been using the SP, but I do need aUDF instead now.All users, including branch office, sub-companies and companies and soon up the lines are in the same table. I need a function which returnsa row for each level, eventually getting to the master company all theway at the top, but this UDF acts as though it can't enter the loop andonly inserts the @userID and @branchID rows. I have played with theWHILE condition to no avail.Any ideas on what I am missing?(Running against SQL Server 2000)---------------------------------------------------ALTER FUNCTION udfUplineGetCompany (@userID int)RETURNS @upline table (companyID int, companyname varchar(100), infovarchar(100))ASBEGINDECLARE @branchID intDECLARE @companyID intDECLARE @tempID int--Insert the original user dataINSERT INTO @uplineSELECT tblusersid, companyname, 'userID'FROM tblusersWHERE tblusersid = @useridSELECT @branchID = tblUsers.tblUsersIDFROM tblUsersINNER JOIN tblUsersUsersLnkON tblUsers.tblUsersID = tblUsersUsersLnk.tblUsersID_ParentWHERE tblUsersUsersLnk.tblUsersID_Child = @userid--Up one levelINSERT INTO @uplineSELECT tblusersid, companyname, 'branchID'FROM tblusersWHERE tblusersid = @branchidSET @tempID = @branchIDWHILE @@ROWCOUNT <> 0BEGINSELECT @companyID = tblUsers.tblUsersIDFROM tblUsersINNER JOIN tblUsersUsersLnkON tblUsers.tblUsersID = tblUsersUsersLnk.tblUsersID_ParentWHERE tblUsersUsersLnk.tblUsersID_Child = @tempIDAND tblUsersId <> 6--Insert a row for each level upINSERT INTO @uplineSELECT tblusersid, companyname, 'companyID'FROM tblusersWHERE tblusersid = @companyIDSET @tempID = @companyIDENDRETURNEND
I wish to call a custom code function from a table control that would return rows of data to be displayed in the table. Is this possible?
Specifically, I'd like to pass a large text string to the function, have the function break the string into smaller strings, and then have the smaller strings displayed in the table. The number of lines returned may vary, depending on the original string passed in.
I started with an inline table returning function with a hard coded input table name. This works fine, but my boss wants me to generalize the function, to give it in input table parameter. That's where I'm running into problems.
In one forum, someone suggested that an input parameter for a table is possible in 2012, and the example I saw used "sysname" as the parameter type. It didn't like that. I tried "table" for the parameter type. It didn't like that.
The other suggestion was to use dynamic sql, which I assume means I can no longer use an inline function.
This means switching to the multi-line function, which I will if I have to, but those are more tedious.
Any syntax for using the inline function to accomplish this, or am I stuck with multi-line?
A simple example of what I'm trying to do is below:
Create FUNCTION [CSH388102].[fnTest] ( -- Add the parameters for the function here @Source_Tbl sysname ) RETURNS TABLE AS RETURN ( select @Source_Tbl.yr from @Source_Tbl )
Error I get is:
Msg 1087, Level 16, State 1, Procedure fnTest, Line 12 Must declare the table variable "@Source_Tbl".
If I use "table" as the parameter type, it gives me:
Msg 156, Level 15, State 1, Procedure fnTest, Line 4 Incorrect syntax near the keyword 'table'. Msg 137, Level 15, State 2, Procedure fnTest, Line 12 Must declare the scalar variable "@Source_Tbl".
Hi,I'm am looking for a little help. I need to create a SQL view whichjoins a few tables, and I need to return an average for a particularcolumn where a second duplicate ID exists...Heres an example of how the results could be returned...ID | Name | Order No. | Value---+------+-----------+---------5 | test | 1234 | 35 | test2| 1234 | 45 | test3| 1234 | 35 | void | 1235 | 55 | void2| 1235 | 65 | void3| 1235 | 55 | void4| 1235 | 7ID is my main join which joins the tablesName is a unique nameOrder No is the same for the different names, I only need to return onerow with this order no, and the first name (the rest are irrelevant)Value is the field which I wish to return as an average of all 3, 4 orhowever many rows is returned and share the same order no. This iswhere I get totally lost as I am pretty new to SQL. Can anyone provideany help on how I would go about limiting this query to the uniqueorder no's and returning the average of the value field, and I can takeit from there with my own tables.Thanks for your helpstr8
I am using SLQ Server 2008 R2. The database was designed by another company.
I have two tables: Client and Client_Location. In the Client table the pk is Client_ID. There is also a unique key: sys_Client_ID. Both the Client_ID and the sys_Client_ID fields exist as a foreign keys in the Client_Location table. However, the fields are not noted as unique in the Client_Location table. There are two fields in the Client_Location table that determine when the address was effective. They are from_dt and end_dt.
Multiple records have been loaded into the Client_Location table to track old as well as current addresses of clients.
I'm trying to run a report that will pull clients with a plan_id constraint from the Client table and join the Client_Location table to retrieve the current address of these clients.
My SQL is:
select distinct (a.client_id), a.cli_last AS Last_Name, a.cli_first AS First_Name, a.cli_middle AS Mid_Init, b.city AS City, b.county AS County, b.state AS State from ECBH.dbo.tbl_Client a inner join ECBH.dbo.tbl_Client_Location b on a.client_id = b.client_id inner join ECBH.dbo.tbl_client_insurance c on a.client_id = c.client_id inner join ECBH_TEST.dbo.tbl_GEF_County d on b.county = d.COUNTY_NAME where c.plan_id = 4 order by a.cli_last, a.cli_first
Because multiple records exist in the Client_Location table, the result set has duplicates. How can I pull only the results where the from_dt is most recent?
I hvae a stored procedure that has this at the end of it: BEGIN EXEC @ActionID = ActionInsert '', @PackageID, @AnotherID, 0, '' END SET NOCOUNT OFF
SELECT Something FROM Something Joins….. Where Something = Something now, ActionInsert returns a Value, and has a SELECT @ActionID at the end of the stored procedure. What's happening, if that 2nd line that I pasted gets called, 2 result sets are being returned. How can I modify this SToredProcedure to stop returning the result set from ActionINsert?
I am trying to produce a query result that will be using a Case statement to determine values based on scores in a table for each row. The result needs to be exported to be used to upload to a state reporting website. My problem is that the state requires in the CSV file that is uploaded a lot of fields that we do not actually have in the database table we are creating the result set from. After I receive my result set using the Case statement, is there a way to add additional columns that don't actually exist in a table so I can export directly from SQL?
So I have been trying to get mySQL query to work for a large database that I have. I have (lets say) two tables Table_One and Table_Two. Table_One has three columns: Type, Animal and TestID and Table_Two has 2 columns Test_Name and Test_ID. Example with values is below:
In Table_One all types come under one column and the values of all Types (Mammal, Fish, Bird, Reptile) come under another column (Animals). Table_One and Two can be linked by Test_ID
I am trying to create a table such as shown below:
This should be my final table. The approach I am currently using is to make multiple instances of Table_One and using joins to form this final table. So the column Bird, Reptile, Mammal and Fish all come from a different copy of Table_one.
For e.g
Select Test_Name AS 'Test_Name', Table_Bird.Animal AS 'Birds', Table_Mammal.Animal AS 'Mammal', Table_Reptile.Animal AS 'Reptile, Table_Fish.Animal AS 'Fish' From Table_One
[Code] .....
The problem with this query is it only works when all entries for Birds, Mammals, Reptiles and Fish have some value. If one field is empty as for Test_Two or Test_Three, it doesn't return that record. I used Or instead of And in the WHERE clause but that didn't work as well.
I feel like I'm missing something really simple here...
I'm trying to write an sp to return a list of countries alphabetically to populate a web drop-down list in a form. However, since most people using the form will be from USA, I want "USA" to appear as the first row, then the rest should be alphabetical, e.g. ("USA", "Afghanistan", "Albania"... "Zimbabwe")
I'm using a UNION query, but it's ordering the result set so that USA appears alphabetically, not as the first row. I'm not using an ORDER BY clause.
Here's the code I'm using:
CREATE PROCEDURE GetCountries AS
SELECT Country_Name FROM Countries WHERE Country_Name = 'USA'
UNION
SELECT Country_Name FROM Countries WHERE Country_Name <> 'USA'
GO
I also tried selecting into a temp table and doing a UNION that way, but got the same results.
This is a strange issue I'm encountering. I have a websphere application that calls a basic Stored Procedure in SQL Server 2000. The SP works fine. Now, if I create a TEMP table in the SP, I no longer get a resultset. No results are returned by the websphere. Even if I do not use the temp table... that is, I just create the temp table like so: SELECT region_code, OperGroup_Code, country_name, MajorGroup_Name, RptgEntity_Name, shell_code, CRC, ' ' as right_type INTO #tmpTShell FROM TShell WHERE 1=0
But I then grab my records using the query that works (querying another table), I still get NULL for records returned. Only when I comment out the above TEMP table creation do I get the results back in websphere. Another strange thing is that this works fin in SQL analyzer. WRegardless of whether I have the temp table creation commented out or not, it always works in the Query Analyzer whe I call the SP. It just seems to effect WebSphere or my java code in that it returns null if I create the temp table.
Has anyone ever experienced anthying like this? Any help would be greatly appreciated!
Created a stored procedure which returns Selected table from database. I pass variables, according to conditions
For some reason it is not returning any result for any condition
Stored Procedure
ALTER PROCEDURE dbo.StoredProcedure ( @condition varchar(20), @ID bigint, @date1 as datetime, @date2 as datetime )
AS /* SET NOCOUNT ON */ IF @condition LIKE 'all' SELECT CllientEventDetails.* FROM CllientEventDetails WHERE (ClientID = @ID)
IF @condition LIKE 'current_events' SELECT ClientEventDetails.* FROM ClientEventDetails WHERE (ClientID = @ID) AND (EventFrom <= ISNULL(@date1, EventFrom)) AND (EventTill >= ISNULL(@date1, EventTill))
IF @condition LIKE 'past_events' SELECT ClientEventDetails.* FROM ClientEventDetails WHERE (ClientID = @ID) AND (EventTill <= ISNULL(@date1, EventTill))
IF @condition LIKE 'upcoming_events' SELECT ClientEventDetails.* FROM ClientEventDetails WHERE (ClientID = @ID) AND (EventFrom >= ISNULL(@date1, EventFrom))
IF @condition LIKE '' SELECT CllientEventDetails.* FROM CllientEventDetails
RETURN
Also I would like to find out if I can put only "where" clause in if condition as my select statements are constants
I want to return all rows in table giftregistryitems with an additional column that holds the sum of column `amount` in table giftregistrypurchases for the respective item in table giftregistryitems.
What I tried, but what returns NULL for purchasedamount:
SELECT (SELECT SUM(amount) from giftregistrypurchases gps where registryid=gi.registryid AND gp.itemid=gps.itemid) as purchasedamount,* FROM giftregistryitems gi LEFT JOIN giftregistrypurchases gp on gp.registryid=gi.id WHERE gi.registryid=2
How can I achieve what I need?
Here's my table definition and data:
/****** Object: Table [dbo].[giftregistryitems] Script Date: 02-05-15 22:37:11 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[giftregistryitems]( [id] [int] IDENTITY(1,1) NOT NULL,
In a t-sql 2012 sql script, I have the following script, that only works for a few records since the value of TST.dbo.LockCombination.seq only contains the value or 1 in most cases. Basically for every join listed below, there should be 5 records where each record has a distinct seq value of 1, 2, 3, 4, and 5. Thus my goal is to determine how to add the missing rows to the TST.dbo.LockCombination where there are no rows for seq values of between 2 to 5. I would like to know how to insert the missing rows and then do the following update statement. Thus can you show me the sql on how to add the rows for at least one of the missing sequence numbers?
UDATE LKC SET LKC.combo = lockCombo2 FROM [LockerPopulation] A JOIN TST.dbo.School SCH ON A.schoolnumber = SCH.type JOIN TST.dbo.Locker LKR ON SCH.schoolID = LKR.schoolID AND A.lockerNumber = LKR.number JOIN TST.dbo.Lock LK ON LKR.lockID = LK.lockID JOIN TST.dbo.LockCombination LKC ON LK.lockID = LKC.lockID WHERE LKC.seq = 2
A normal select statement looks like the following:
select * from TST.dbo.Locker LKR JOIN TST.dbo.Lock LK ON LKR.lockID = LK.lockID JOIN TST.dbo.LockCombination LKC ON LK.lockID = LKC.lockID where LKR.number in (000,001,1237)
In case you need the ddl statements for the tables affected here are the ddl statements:
CREATE TABLE [dbo].[Locker]( [lockerID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, [schoolID] [int] NOT NULL, [number] [varchar](10) NOT NULL, [serialNumber] [varchar](20) NULL, [type] [varchar](3) NULL, [locationID] [int] NULL,
What is the threshold that warrants the need for creating an index on a table. I have a system with a TABLE that has only 500 rows, very static, but accessed many many times. The cardinality from the statistics of Actual vs. Estimate rows is off by only 5 rows. I have all clustered index scans. What I need to be looking for as I tune this DB. I am going through every stored procedure to finely tune the DB I am supporting.
Seasons greetings to everyone,A simple question. Could someone show me the syntax to produce multiple (2 or 3) result sets in a stored proc and how you access those sets from a c# program (ASP.NET)..Couldn't find a reference on Google, maybe I was asking the wrong question! Thanks for any help regardsDavej
I am finding it difficult to find an example that allows for insertion of additional rows into a table, without dropping the table I'm inserting into. Or inserting specific values. Like this example..
[URL] ....
I have 6 table I am formatting the data to conform to the final table as I'm inserting it into, but none of these examples gives me the example needed. I am using SQL 2012.
<code> SELECT CONVERT(VARCHAR(50),[FName]) + ' ' + CONVERT(VARCHAR(50),[LName]) AS [CustName] ,CAST('ALARMCOM' as nvarchar(8)) as VendorName ,CONVERT(VARCHAR(25),[CUSTOMER_CS_ACCOUNT_NUMBER]) AS [Cust_ID] ,CONVERT(VARCHAR(40),[Charge_Description])as [ChargeType] ,CASE
There is a valuable script out there that will take the rows from a table and display INSERT STATEMENTS.
Good thing is this script converts the data to HEXADECIMAL ( or some other ) and we don't have to worry about dealing with apostrophies embedded in varchar fields.
I have a very strange problem using SQL Server 2005
I have several machines running an application, the problem is that on all machines except one of them the size of the result set that gets returned when I execute the following query is dfferent:
Select * from custoemr where EmployeeID = 3
on three out of the four machine the size of the result set is 1000, where on the other machine the size of the result set is 250, No errors are generated..
Can someome please teel me how to preceed in resolving this issue..
Given one table, Table1, with columns Key1 (int), Key2 (int), and Type (varchar)...
I would like to get the rows where Type is equal to 'TypeA' and Key2 is Null that do NOT have a corresponding row in the table where Type is equal to 'TypeB' and Key2 is equal to Key1 from another row
I would like to return only the row where Key1 = 4 because that row meets the criteria of Type='TypeA'/Key2=NULL and does not have a corresponding row with Type='TypeB'/Key1=Key2 from another row.
I have tried this and it doesn't work...
SELECT t1.Key1, t1.Key2, t1.Type FROM Table1 t1 WHERE t1.Key2 IS NULL AND t1.Type LIKE 'TypeA' AND t1.Key1 NOT IN (SELECT Key1 FROM Table1 t2 WHERE t1.Key1 = t2.Key2 AND t1.Key1 <> t2.Key1 AND t2.Type LIKE 'TypeB')
I have resulting rows from a query similar to the following:
The data is coming from a single table that contains only one coverage code column and one coverage code date, but the end user wants the two coverage code types and dates combined into a single row. So the SELECT looks something like this:
SELECT [Employee ID] = emp.employee_id, [Coverage Code 1] = enr.coverage_code, [Coverage Date 1] = enr.coverage_date, [Coverage Code 2] = case when enr.product_type = 'Accident.Accident' then enr.coverage_code else NULL end,
[Code] ....
I basically want to merge the like Employee ID's together into a single row like the following:
I know I have done this before and it is probably pretty simple.
I want to return all rows in table giftregistryitems with an additional column that holds the sum of column `amount` in table giftregistrypurchases for the respective item in table giftregistryitems.
What I tried, but what returns NULL for purchasedamount:
SELECT (SELECT SUM(amount) from giftregistrypurchases gps where registryid=gi.registryid AND gp.itemid=gps.itemid) as purchasedamount,* FROM giftregistryitems gi LEFT JOIN giftregistrypurchases gp on gp.registryid=gi.id WHERE gi.registryid=2
How can I achieve what I need?
Here are my table definitions and data:
/****** Object: Table [dbo].[giftregistryitems] Script Date: 02-05-15 22:37:11 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[giftregistryitems]( [id] [int] IDENTITY(1,1) NOT NULL,
In my ETL job I would like to truncate stg table but before truncating stging table, I want to make sure that all the records are inserted in the data model. The sample is as below
create table #stg ( CreateID int, Name nvarchar(10) ) insert into #stg select 1, 'a' union all select 2, 'b' union all
[Code] ....
How can I check among these tables and make sure that all values are loaded into the data model and the staging table can be truncated.
I have a master table and i need to import the rows into the parent and child table.
Master table name is Flatfile_Inventory Parent Table name is INVENTORY Child Tables name are INVENTORY_AMOUNT,INVENTORY_DETAILS,INVENTORY_VEHICLE, Error details will be goes to LOG_INVENTORY_ERROR
I have 4 duplicate rows in the Flatfile_Inventory which i have already inserted in the Parent and child table.
Again when i run the query using stored procedure,its tells that all the 4 rows are duplicate and will move to the Log_Inventory_Error.
I need is if i have the duplicate rows in the flatfile_Inventory when i start inserting into the parent and child table the already inserted row have the unique ID i must identify it and delete that row in the both parent and chlid table.And latest row must get inserted into the Parent and child table from Flatfile_Inventory.
SELECT TOP (100) PERCENT dbo.Filteredfs_franchise.fs_franchiseid AS FranchiseId, dbo.Filteredfs_franchise.fs_brandidname AS Brand, dbo.Filteredfs_franchise.fs_franchisetypename AS [Franchise Type], dbo.Filteredfs_franchise.fs_franchisenumber AS [Franchise Number], dbo.Filteredfs_franchise.fs_transactiontypename AS [Transaction Type], dbo.Filteredfs_franchise.fs_franchisestatusname AS [Status Code],
[Code] ....
I need to pivot this so I can get one row per franchiseID and multiple columns for [Franchisee Name Entity] and [Franchise Name Individual]. Each [Franchisee Name Entity] and [Franchise Name Individual] has associated percentage of ownership.
This has to be dynamic, because each FranchiseID can have anywhere from 1 to 12 respective owners and those can be any combination of of Entity and Individual. Please, see the attached example for Franchise Number 129 (that one would have 6 additional columns because there are 3 Individual owners with 1 respective Percentage of ownership).
The question is how do I PIVOT and preserve the percentage of ownership?
I have deleted nearly 30 million rows from a table. But however when I used the sp_spaceused command to calculate the data occupied by the table I don't see any difference in the data size of the table. In fact the data has increased to few MBs after the deletion, but not much.
I have created a trigger that is set off every time a new item has been added to TableA.The trigger then inserts 4 rows into TableB that contains two columns (item, task type).
Each row will have the same item, but with a different task type.ie.
I have a table with PO#,Days_to_travel, and Days_warehouse fields. I take the distinct Days_in_warehouse values in the table and insert them into a temp table. I want a script that will insert all of the values in the Days_in_warehouse field from the temp table into the Days_in_warehouse_batch row in table 1 by PO# duplicating the PO records until all of the POs have a record per distinct value.
Example:
Temp table: (Contains only one field with all distinct values in table 1)
