Hi,
I have a employee attendance table, and Im trying to return the dates on which an employee is absent. There are no table entries for an employee who is absent on a particular day. So can anyone please help on how i can return the dates on which an employee is absent.
Any help will be greatly appreciated,
Thanks,
Najla.
I want to write a stored procedure that takes two three paramters based on a case statement determining which values are null something like this
Select InformationDate From thisTAble
Where
If @dateValue IsNot @Null
Informationdate = @dateValue
Else
Where
In this second where cluase I want to be able to pull out all the results based on a date range and i am not sure how the syntaz would go
InfomationDate IsBettween @daterangeFrom @dateRangeTwo
Any help on this i hope i was clear...thank you!
I'm using SS 2012.
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".
The input table can have several thousand rows.
I have a table that has hotel guests and their start stay date and end stay date, i would like to insert into a new table the original information + add all days in between.
CREATE TABLE hotel_guests
(
[guest_name] [varchar](25) NULL,
[start_date] [date] NULL,
[end_date] [date] NULL,
[comment] [varchar](255) NULL
[code]...
We currently have an application whereby the user will come along sign up add some information about them, choose what interests / hobbies they're in to and then click save.After completing the sigh up they're able to search for other individuals within locations (look at this functionality as a dating site) We currently have a stored procedure whereby when the user clicks Advance Search they fill in all the fields they filled in when signing up and when they click search we return all users that match the data the user has selected. This part is working correctly.
The issue im having is extending this stored procedure to take a user defined table type which has two columns UserId and ActivityId both columns are nullable.This user defined table type is referred to in two places, when the user signs up and when they do an advance search.When the user does an advance search he / she can choose a list of hobbies / interests this will then be passed in to the database and I will join on my User_Activities table to find the users that have the Activities passed in.When I join on this table I get duplicate records purely because one user may have 5 - 10 activities depending on how active the individual is. My current stored procedure looks like this
@Ambition int = null,
@Body int = null,
@Diet int = null,
@Drinking int = null,
@Ethnicity int = null,
@Exercise int = null,
@HeightFrom int = null,
[code]...
So my question is how can I select all users that match the criteria passed in, as well as finding the users that match the activities passed in (the user can either match all of them or a minimum of 1). Also my User_Activities has two columns UserId and ActivityId identical to the user defined table type
I am experimenting with using CDC to track user changes in our application database. So far I've done the following:
-- ENABLE CDC ON DV_WRP_TEST
USE dv_wrp_test
GO
EXEC sys.sp_cdc_enable_db
GO
-- ENABLE CDC TRACKING ON THE AVA TABLE IN DV_WRP_TEST
USE dv_wrp_test
[Code] ....
The results shown above are what I expect to see. My problem occurs when I use our application to update the same column in the same table. The vb.net application passes a Table Valued Parameter to a stored procedure which updates the table. Below is the creation script for the stored proc:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
if exists (select * from sysobjects where id = object_id('dbo.spdv_AVAUpdate') and sysstat & 0xf = 4)
drop procedure dbo.spdv_AVAUpdate
[Code] ....
When I look at the results of CDC, instead of operations 3 and 4, I see 1 (DELETE) and 2 (INSERT) for the change that was initiated from the stored procedure:
-- GET CDC RESULTS FOR CHANGES TO AVA TABLE
USE dv_wrp_test
GO
SELECT *
FROM cdc.dbo_AVA_CT
GO
--RESULTS SHOW OPERATION 1 (DELETE) AND 2 (INSERT) INSTEAD OF 3 AND 4
--__$start_lsn__$end_lsn__$seqval__$operation__$update_maskAvaKeyAvaDescAvaArrKeyAvaSAPAppellationID
--0x0031E84F000000740008NULL0x0031E84F00000074000230x02119Test26NULL
--0x0031E84F000000740008NULL0x0031E84F00000074000240x02119Test36NULL
--0x0031E84F00000098000ANULL0x0031E84F00000098000310x0F119Test36NULL
--0x0031E84F00000098000ANULL0x0031E84F00000098000420x0F119Test46NULL
Why this might be happening, and if so, what can be done to correct it? Also, is there any way to get the user id associated with the CDC?
currently need to re-create an aggregate table in a proc every night to aggregate purchases broken down by person/store in groups of 3, 6 12 etc months.finding the performance of it is very slow as it covers 500,000 million rows.The query looks like
SELECTCusID(int)
, StoreID(int)
, SUM(L3M) as Last3Months
, SUM(L6M) as Last6Months
, SUM(L9M) as Last9Months
[code]...
I need to make changes to this because it is using a BETWEEN on a datetime column. I was wondering though, if anyone else has made agg tables like this before an found a better way of doing them?
How would I find the last read/write dates for all the tables within a database.
View 6 Replies View RelatedI have the following information in a table. What I would like to do is pull out all the visits for each customer that are less than 30 days apart.
Customer# VisitDate
9082012-07-28 00:00:00.000
9082013-09-20 00:00:00.000
9082013-12-23 00:00:00.000
9082014-01-10 00:00:00.000
9082014-01-27 00:00:00.000
9082014-02-16 00:00:00.000
9082014-05-21 00:00:00.000
9082014-05-30 00:00:00.000
9082014-10-01 00:00:00.000
9082015-02-28 00:00:00.000
9082015-03-22 00:00:00.000
9272012-02-16 00:00:00.000
9272014-12-14 00:00:00.000
9272014-12-23 00:00:00.000
I have a table with appdt as first appointment date and the another record for the same customer# has follow up appointment.
Each customer is uniquely identified by a customer#
I need to find out if the customer came back after 200 days or more when the first appointment date was between jan12014 and Aug 31 2014. I am only interested in first follow up appointment after 30 days or more.
How can i do that in a query?
I have three tables:
"PaymentsLog"
"DatePeriod"
"PaidOrders"
As per below
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PaymentsLog](
[Code] ....
Is there a way to look at the DatePeriod table and use the StartDtae and EndDate as the periods to be used in the select statement and then cursor through each date between these two dates and then insert the data in to the PaymentsLog table?
In a table-valued UDF, does the UDF use a table variable or a temp table to form the resultset returned?
I cannot create a measure that returns results for dates that do not exist in the fact table despite the fact that the components included in the measure contain valid results for these same dates.Creature a measure that counts the number of days where the "stock qty" is below the "avg monthly sales qty for the last 12 months" (rolling measure).Here is the DAX code I have tried for the measure (note that filter explicitly refers to the date table (called Calendar) and not the fact table):
StkOutCnt:=CALCULATE (
COUNTROWS ( VALUES ( Calendar[DateKey] ) ),
FILTER (
Calendar,
[Stock qty] < [Avg Monthly Sales Qty L12M@SKU]
)
)
Below you can see the sub measures (circled in red) are giving results for all days in the calendar.Highlighted in yellow are dates for which the StkOutCnt measure is not returning a result. Having investigated these blank dates, I am pretty confident that they are dates for which there are no transactions in the fact table (weekends, public holidays etc...).why I am getting an "inner join" with my fact table dates despite the fact that this is not requested anywhere in the dax code and that the two sub measures are behaving normally?
can sql server do this ?
table 1 that check table 2 and adding missing dates
this my employee table
table 1
table Employee on work
------------------------
empid basedate shift
----------------------------
12345678 01/04/2007 1
12345678 02/04/2007 1
12345678 03/04/2007 1
12345678 04/04/2007 1
12345678 05/04/2007 1
12345678 06/04/2007 1
12345678 07/04/2007 1
12345678 08/04/2007 1
12345678 09/04/2007 1
12345678 10/04/2007 1
98765432 20/04/2007 1
98765432 21/04/2007 3
98765432 22/04/2007 3
98765432 23/04/2007 5
98765432 25/04/2007 4
98765432 26/04/2007 4
98765432 27/04/2007 4
98765432 28/04/2007 4
98765432 30/04/2007 4
-----------------------------------------------------------------------------------
and i need to see the missing dates lkie this
in table 2
------------------------------------------------------
table 2 (adding missing dates with zero 0)
table Employee_all_month
------------------------
empid basedate shift
----------------------------
12345678 01/04/2007 1
12345678 02/04/2007 1
12345678 03/04/2007 1
12345678 04/04/2007 1
12345678 05/04/2007 1
12345678 06/04/2007 1
12345678 07/04/2007 1
12345678 08/04/2007 1
12345678 09/04/2007 1
12345678 10/04/2007 1
12345678 11/04/2007 0
12345678 12/04/2007 0
12345678 13/04/2007 0
12345678 14/04/2007 0
12345678 15/04/2007 0
12345678 16/04/2007 0
12345678 17/04/2007 0
12345678 18/04/2007 0
12345678 19/04/2007 0
12345678 20/04/2007 0
.................................and adding missing dates with zero 0 until the end of the month
.................................
12345678 31/04/2007 0
98765432 01/04/2007 0
98765432 02/04/2007 0
98765432 03/04/2007 0
98765432 04/04/2007 0
98765432 05/04/2007 0
98765432 06/04/2007 0
98765432 07/04/2007 0
98765432 08/04/2007 0
98765432 09/04/2007 0
..............................and adding missing dates with zero 0 only whre no dates in this month
.......................
98765432 20/04/2007 1
98765432 21/04/2007 3
98765432 22/04/2007 3
98765432 23/04/2007 5
98765432 25/04/2007 4
98765432 26/04/2007 4
98765432 27/04/2007 4
98765432 28/04/2007 4
98765432 30/04/2007 4
TNX
I am having a problem in creating query for this exciting scenario.
Table A
ID ItemQtyCreatedDatetime
W001 CB112014-06-03 20:30:48.000
W002 CB112014-06-04 01:30:48.000
Table B
IDItemQtyCreatedDatetime
A001 CB112014-06-03 19:05:48.000
A002 CB112014-06-03 20:05:48.000
A003 CB112014-06-03 21:05:48.000
A004 CB112014-06-04 01:05:48.000
A005 CB112014-06-04 02:05:48.000
I would like to return the nearest date of Table B in my table like for
ID W001 in table B should return ID A002 CreatedDatetime: 2014-06-03 20:05:48.000
ID W002 in table B should return ID A004 CreatedDatetime: 2014-06-04 01:05:48.000
There's an SP that in effect returns a table -- it loops and executesstatements like SELECT @field1, @Field2, @Field2Can I capture its results in a table? I know that if it were a FUNCTIONthat returned a table, that would be simple:INSERT ResultsTable SELECT * FROM dbo.Function (@Param1, @Param2)But how to do that if the code is a procedure rather than a function?
View 1 Replies View RelatedHi, I'm new to the forums, so hopefully I'm at the right place with my question...
Suppose I have a user-defined type in C#, which stands for simple point in 2D (let's call it Point). The type consists of two integer fields that represent the X and Y coordinate of the point. To make things a little more complicated, I want to store my values compressed using an algorithm, so I can't directly access X and Y without using a decompression algorithm.
I also have a table (PointTable), it's only column is called Data and is of type Point.
Now, writing and executing a simple query like
SELECT Data.ToString() FROM PointTable
is no problem at all.
What I want to do, is to be able to retrieve a table filled with the X and Y coordinates, so something like this:
SELECT PointX, PointY FROM GetTable()
where GetTable creates a table with columns PointX and PointY.
The function GetTable() should be implemented in C#, and work something like this: iterate through all Point values, decompress them, and create a new row from each Point.
I hope my intentions are clear and thanks for your help in advance!
I have a multi-level folders table, named folders with attributes of folder_id, parent_id and user_id, and have another table, users, contains all the user_id. I need to list all the users for each folder which has parent_id = 0 and its all sub-folders' users into one table. I have created a function to return a table with folder_id and user_id for one single folder. However, I don't know how to use this function to get the sub-folder's users and merge them together as one single table.
Here is my function:
CREATE FUNCTION [dbo].[FolderUsers] (@fid int) RETURNS Table AS Return (select folder_id, f.user_id from folders f, users u where f.user_id = u.user_id and folder_id = @fid)Go
where @fid is the top folder with parent_id = 0 at here, the next level sub-folder's parent_id would be = @fid.
I am thinking to have recursive call from the parent_id = @fid that returns another table and have to concatenate to the called table. I have been thinking of store procedure, "insert into" and so on, but don't know how to implement it.
Do you have any good inspiration for me? Thank you in advance!
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
create FUNCTION [dbo].[search](@art varchar,@cd varchar,@tra varchar,@gen varchar,@cdate datetime,@label varchar)
RETURNS @result TABLE(Artist varchar(100),CDTitle varchar(100),Track varchar(100),CDtype
varchar(100),CDDate datetime, Label varchar(100))
AS
BEGIN
IF @art <>'/'
INSERT INTO @result SELECT dbo.CD.CDCoverURL AS ' ', dbo.CD.CDTitle AS 'CD Title',cd.cdtype as
'Section', convert(varchar,cd.cddate,106) as 'Release Date', dbo.Label.Label,
dbo.Shelf.Shelf
FROM artist,cd,label,shelf,cdtrack,artisttrack,track
WHERE artist.artistid=artisttrack.artistid and cd.cdid=cdtrack.cdid and
track.trackid=cdtrack.trackid and label.labelid=cd.labelid and
shelf.shelfid=cd.shelfid and artisttrack.trackid=track.trackid
and artist.artist=@art
Group by dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate,
dbo.Label.Label, dbo.Shelf.Shelf
if @cd <>'/'
insert into @result SELECT dbo.CD.CDCoverURL AS ' ', dbo.CD.CDTitle AS 'CD Title',cd.cdtype as 'Section', convert(varchar,cd.cddate,106) as 'Release Date', dbo.Label.Label, dbo.Shelf.Shelf
FROM artist,cd,label,shelf,cdtrack,artisttrack,track
where artist.artistid=artisttrack.artistid
and cd.cdid=cdtrack.cdid and track.trackid=cdtrack.trackid and label.labelid=cd.labelid
and shelf.shelfid=cd.shelfid and artisttrack.trackid=track.trackid and cd.cdtitle=@cd
Group by dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate, dbo.Label.Label, dbo.Shelf.Shelf
if @tra <> '/'
insert into @result SELECT dbo.CD.CDCoverURL AS ' ', dbo.CD.CDTitle AS 'CD Title',cd.cdtype as 'Section', convert(varchar,cd.cddate,106) as 'Release Date', dbo.Label.Label, dbo.Shelf.Shelf
FROM artist,cd,label,shelf,cdtrack,artisttrack,track
where artist.artistid=artisttrack.artistid
and cd.cdid=cdtrack.cdid and track.trackid=cdtrack.trackid and label.labelid=cd.labelid
and shelf.shelfid=cd.shelfid and artisttrack.trackid=track.trackid and track.track=@tra
Group by dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate, dbo.Label.Label, dbo.Shelf.Shelf
if @gen <>'/'
insert into @result SELECT dbo.CD.CDCoverURL AS ' ', dbo.CD.CDTitle AS 'CD Title',cd.cdtype as 'Section', convert(varchar,cd.cddate,106) as 'Release Date', dbo.Label.Label, dbo.Shelf.Shelf
FROM artist,cd,label,shelf,cdtrack,artisttrack,track
where artist.artistid=artisttrack.artistid
and cd.cdid=cdtrack.cdid and track.trackid=cdtrack.trackid and label.labelid=cd.labelid
and shelf.shelfid=cd.shelfid and artisttrack.trackid=track.trackid and cd.cdtype=@gen
Group by dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate, dbo.Label.Label, dbo.Shelf.Shelf
if @cdate<>'01/01/1900'
insert into @result SELECT dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate, dbo.Label.Label, dbo.Shelf.Shelf
FROM artist,cd,label,shelf,cdtrack,artisttrack,track
where artist.artistid=artisttrack.artistid
and cd.cdid=cdtrack.cdid and track.trackid=cdtrack.trackid and label.labelid=cd.labelid
and shelf.shelfid=cd.shelfid and artisttrack.trackid=track.trackid and cd.cddate=@cdate
Group by dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate, dbo.Label.Label, dbo.Shelf.Shelf
if @label<>'/'
insert into @result SELECT dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate, dbo.Label.Label, dbo.Shelf.Shelf
FROM artist,cd,label,shelf,cdtrack,artisttrack,track
where artist.artistid=artisttrack.artistid
and cd.cdid=cdtrack.cdid and track.trackid=cdtrack.trackid and label.labelid=cd.labelid
and shelf.shelfid=cd.shelfid and artisttrack.trackid=track.trackid and label.label=@label
Group by dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate, dbo.Label.Label, dbo.Shelf.Shelf
return
end
---------------------------------------------------------------------
upon running executing this function with valid values i am not getting any results.
anything is wrong?
thank you,
Hi guys,
I have to work with some configuration data that is stored in rows as a comma separated values. Something like this:
Key1
A,1,Z,0;B,2,Y,9;C,,8,X;
Key2
Alpha,101;Beta,102;
Each group of data is separated by a semicolon and each value by a comma. The quantity of values may vary from one key to the other. Over this values sometimes I need to run some selects, so I went with the idea to get it as a table using CLR.
There I find the first problem: I didn't find a way to return a "variable" column with a CLR function, I had to create a SP. Ok, now I execute spGetConfigurationAsTable 'Key1' and I can obtain something like this:
A
1
Z
0
B
2
Y
9
C
3
X
8
But I'm faced with a second problem: How can I run a query over this? I didn't find a way to run a query over an output of a SP. And I can't INSERT the result into a temporary table because I cannot CREATE the table static (remember the columns may differ from one configuration to the other).
So, it seemed a pretty simple task and a neat solution, but I'm kinda stuck. Is there a way to run a query over the SP output? Or is there a way to have a variable table output from a CLR UDF?
Here is the code of the CLR SP I use to obtain the data, and also the (non-working) CLR user defined function.
THANKS!
Code Snippet
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void spGetConfigurationAsTable(string Key)
{
SqlConnection conn = new SqlConnection("Context Connection=true");
string SqlCmd = string.Format("SELECT Value FROM Configuracion WHERE [Key] = '{0}' ", Key);
SqlCommand cmd = new SqlCommand(SqlCmd, conn);
conn.Open();
string Value = Convert.ToString(cmd.ExecuteScalar());
if (Value.Length > 0)
{
char SeparatorRow = ';';
char SeparatorColumn = ',';
if (Value.Split(SeparatorRow)[0].Split(SeparatorColumn).Length > 35)
return;
StringBuilder SqlCreate = new StringBuilder("DECLARE @Output TABLE (");
for (int i = 0; i < Value.Split(SeparatorRow)[0].Split(SeparatorColumn).Length; i++)
{
SqlCreate.AppendFormat("[{0}] varchar(50),", Convert.ToChar(65 + i));
}
SqlCreate.Remove(SqlCreate.Length - 1, 1);
SqlCreate.AppendLine(");");
StringBuilder SqlInsert = new StringBuilder();
foreach (string row in Value.Split(SeparatorRow))
{
if (row.Length > 0)
{
SqlInsert.Append("INSERT INTO @Output VALUES (");
// busca las diferentes "columns" ~ Charly
foreach (string column in row.Split(SeparatorColumn))
{
SqlInsert.AppendFormat("'{0}',", column);
}
SqlInsert.Remove(SqlInsert.Length - 1, 1);
SqlInsert.AppendLine(");");
}
}
string SqlSelect = "SELECT * FROM @Output;";
cmd.CommandText = SqlCreate.ToString() + SqlInsert.ToString() + SqlSelect;
SqlDataReader reader = cmd.ExecuteReader();
SqlContext.Pipe.Send(reader);
reader.Close();
reader.Dispose();
}
conn.Close();
conn.Dispose();
cmd.Dispose();
}
};
Code Snippet
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static IEnumerable fGetConfigurationAsTable(string Key)
{
SqlConnection conn = new SqlConnection("Context Connection=true");
string SqlCmd = string.Format("SELECT Value FROM Configuracion WHERE [Key] = '{0}' ", Key);
SqlCommand cmd = new SqlCommand(SqlCmd, conn);
conn.Open();
string Value = Convert.ToString(cmd.ExecuteScalar());
conn.Close();
conn.Dispose();
cmd.Dispose();
DataTable dt = new DataTable();
if (Value.Length > 0)
{
char SeparatorRow = ';';
char SeparatorColumn = ',';
if (Value.Split(SeparatorRow)[0].Split(SeparatorColumn).Length > 35)
{
// throw exception
}
string ColumnName;
for (int i = 0; i < Value.Split(SeparatorRow)[0].Split(SeparatorColumn).Length; i++)
{
ColumnName = string.Format("[{0}] varchar(50),", Convert.ToChar(65 + i));
dt.Columns.Add(ColumnName, Type.GetType("System.String"));
}
foreach (string row in Value.Split(SeparatorRow))
{
if (row.Length > 0)
{
dt.Rows.Add(row.Split(SeparatorColumn));
}
}
}
return dt.Rows;
}
};
I have a table tblFriends:UserCodeOwner FriendCode5 545 1235 4785 49054 5123 5478 5478 500490 5490 500500 478500 490500 600600 500As you can see I store each relation twice, i've done that because now I can create a clustered index on UserCodeOwner for faster searching.What I want is the following:I want to count how many steps it takes to get from one user to another via their relations and also via which usercode it goes.so: from 5 to 123 is one step.5-123from 5 to 500 is 2 steps via either 478 or 490I want to get a result like this:5-478-500and5-490-500from 5 to 600 is 3 steps:5-478-500-6005-490-500-600Does anyone have a good start for me on how im able to return such results and how I can search through the table most effecively?
View 3 Replies View RelatedI have two tables. In tblUsers is all userdata. In table tblMoreInfo is some info on a user (0-15 records).Now I want to select tblUsers.username and tblUsers.usercode and the matching top 1 row tblMoreInfo.schoolname (top 1 based on tblMoreInfo.createdate) IF IT EVENT EXISTS from tblMoreInfo. If no matching record exists I want the value NULL to be returned for the schoolname...In all cases tblUsers.username must be unique (and I cant use the distinct keyword as im already using the row_number() function.Any help?Thanks!
View 1 Replies View RelatedI am trying to created a stored procedure/query to return all fields from any of my 43 look-up tables in my database. I have all of my look-up table names listed in a VB grid. I want the user to select a particular table, click edit (which pass's the table name) and have one stored procdure return all of the field in the table and populate them in a second VB form with the fields listed in a new grid.
Can one pass the "SQL string" to excute/ create a temp table.... A regular SP requires a specife table name to query from. Is there a way to do this without creating a temp table
Thanks for the help
EM.
I need to do something sort of like the DESCRIBE function in MySQL. I need to return the table structure, AND the first row from each column sort of as an example of the data in each column.
then i would just need to run this query on each of my tables...
any ideas?
How to return an entire table from stored procedure ????
thanx in advance
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
View 2 Replies View RelatedHi
I have a dataset, a sql query, that selects a bunch of opportunities. The result consist of close date, values, names, owners, topics and so on.
I have put the result in a matrix and the. The matrix will grow to the right with the months columns. Only the months that have an opportunity in them will be shown. (estimated close date is the date were I select from).
So, for example when there are opportunities in Jan, Feb and Aug in 2008 only those 3 months will show the data that corresponds to each opportunity.
Now, how can I make my query to show all the empty months as well? I want a matrix that shows Jan, Feb, Mar, €¦ Nov and Dec, even if they don€™t have any opportunities in them.
Kind Regards
Sp which inserts inforamtion into a table works fine. The trigger on that table which then inserts information into another table works fine. Only problem is that the SP will not return anything to Visual Basic.. Anyone know how to fix it?
SPbob
INsert into b values(1,1,2,2)
select 0
(this is a cut up version of the sp just to show about the way it is formated)
Please help.
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.
Hello again,
I am using UNION to return these 2 tables:
TableA
QID Q
1 Name?
2 Age?
3 Phone?
TableB
QID Q
1 DogName?
2 CatName?
When I use the following query;
SELECT * FROM TableA UNION SELECT * FROM TableB
I get the following return:
QID Q
1 whateverquestions
1
2
2
3
but I wish it to return in order of table invoke:
QID
1
2
3
1
2
Is that possible? (by not changing QID)
Cheers,
James
i want to have a user defined function or stored procedure(i don't know which one should be used, anyway!) which returns false if the table has no records, with the table name comes with a parameter.
could you give a little code sample?
thanx
I am trying to get the schema of database using the getschema method. However, the schema that is being returned does not include the description. I have added in table and column descriptions for some of my tables and columns but the dataset returned does not include the description column.
Any idea on how to get the description to be output?
Dim testCn As System.Data.OleDb.OleDbConnection
testCn = New System.Data.OleDb.OleDbConnection(step2.GetRevEngConn.ConnectionString)
Dim testDS As DataTable
testCn.Open()
testDS = testCn.GetSchema("TABLES")
testds.writexml("c:schematables.xml")
testDS = testCn.GetSchema("COLUMNS")
testDS.writexml("C:schemacol.xml")
testCn.Close()
- <Columns>
<TABLE_CATALOG>XDMDB_LAPTOP</TABLE_CATALOG>
<TABLE_SCHEMA>dbo</TABLE_SCHEMA>
<TABLE_NAME>PropertyValue</TABLE_NAME>
<COLUMN_NAME>property_value</COLUMN_NAME>
<ORDINAL_POSITION>6</ORDINAL_POSITION>
<COLUMN_HASDEFAULT>false</COLUMN_HASDEFAULT>
<COLUMN_FLAGS>230</COLUMN_FLAGS>
<IS_NULLABLE>true</IS_NULLABLE>
<DATA_TYPE>130</DATA_TYPE>
<CHARACTER_MAXIMUM_LENGTH>0</CHARACTER_MAXIMUM_LENGTH>
<CHARACTER_OCTET_LENGTH>0</CHARACTER_OCTET_LENGTH>
<CHARACTER_SET_CATALOG>master</CHARACTER_SET_CATALOG>
<CHARACTER_SET_SCHEMA>dbo</CHARACTER_SET_SCHEMA>
<CHARACTER_SET_NAME>iso_1</CHARACTER_SET_NAME>
<COLLATION_CATALOG>master</COLLATION_CATALOG>
<COLLATION_SCHEMA>dbo</COLLATION_SCHEMA>
<COLLATION_NAME>SQL_Latin1_General_CP1_CI_AS</COLLATION_NAME>
<COLUMN_LCID>1033</COLUMN_LCID>
<COLUMN_COMPFLAGS>196609</COLUMN_COMPFLAGS>
<COLUMN_SORTID>52</COLUMN_SORTID>
<COLUMN_TDSCOLLATION>CQTQADQ=</COLUMN_TDSCOLLATION>
<IS_COMPUTED>false</IS_COMPUTED>
</Columns>
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.