Hi all,
Is it possible to retrieve a particular row from a result set? For eg if my query returns 5 rows and i want to just retrieve the 3rd row from the result....is it possible? If yes...can someone tell me the syntax for it....would appreciate the gr8 help...
Thanks,
SQL Novice
I am using SQL Server 2005.
I have a table with records like this:
Date EmpID ADCode ADAmount
----------------------------------------------------------------------
01-Jul-07 101 GPF 150
01-Jul-07 102 GPF.ADV 100
01-Jul-07 103 GPF 250
01-Jul-07 103 GPF.ADV 200
01-jul-07 104 GPF 300
I want to show results like this using a single SQL query:
Date EmpID GPF GPF.ADV
-----------------------------------------------------------
01-Jul-07 101 150 0
01-Jul-07 102 0 100
01-Jul-07 103 250 200
01-Jul-07 104 300 0
I tried:
select PaySlipDate,EmpID,
case ADCode when 'GPF' then ADAmount else 0 end GPF,
case ADCode when 'GPF.ADV' then ADAmount else 0 end 'GPF.ADV'
from EmpSalaryRecord
It is showing like this:
Date EmpID GPF GPF.ADV
-----------------------------------------
01-Jul-07 101 0 0
01-Jul-07 101 150 0
01-Jul-07 102 0 100
01-Jul-07 103 0 0
01-Jul-07 103 250 0
01-Jul-07 103 0 200
It is showing multiple records of each employee for each date. First a record with GPF and GPF.ADV both zero and then records with values. I want a single record for each date and employee.
Hi I'am practically new in C#, so I want to ask you guys some question.Let say I have this query:"Select EmployeeID, EmpName from PI_Employee"How can I retrieve the result from EmployeeID column and EmpName column and put it into collections of array, so I can call the array and use it again in another function. Can I possibly do that in C# ? if so, how ? please help me guys, I'm on the edge of nervous wreck in here so I could use a little help, any kinds of help. Thanks. Best Regards.
View 2 Replies View RelatedHow to retrieve only nth row as a result of execution of one query?
For example:
Lte the table be:
SNo SudentName Marks
001 Ashok kumar 67
002
Anderson
70
003 Alfred
60
004 Ameeruddin
65
005 Kalyan
Kumar.
69
Now the Query is: How is the 3rd ranker. The answer must be Ashok kumar.
How to write this query in SQL Server.
I have two tables that have no relation. However, both have a column which has a field of nvarchar(50) that I want to retrieve together in one operation and bind to a DropDownList in a sorted fashion. So, what I'm trying to achieve is this:
1. SELECT name FROM table1
2. SELECT name FROM table2
3. Join the two results together and order them alphabetically
4. Return the result set
I'm not sure how to do this or even if it's possible. Ideally I'm hoping it can be done in a stored proc.
Can I retrieve a result set from a sp into a variable within a Execute SQL Task?
View 23 Replies View RelatedHi guys
Is there anyone who was able to successfully retrieve a full result set? I'm really having troubles getting the result after executing my query. Its really even hard to get sample codes over the net.
Please help guys.
Thanks in advance.
kix
We have a sort of complex user structure in the sense that depending on the type of user the data resides in different tables. Therefor I needed a stored procedure that finds out what table to look for a certain column in. Below is such a stored procedure and it works like it should but my problem is that I don't know how to retrieve the result (which should be a string so can't use RETURN).
I've tried using an OUTPUT variable but since I just run EXEC (@statement) in the end I can't really set an output variable the common way (as in EXEC @outputVariable = PMC_User_GetUserValue(arg1, arg2..)) or can I?
I have also tried to use SELECT to catch the result somehow but no luck and Google didn't help either so now I'm hoping for one of you... Notice that you don't have to bother about much of the code except for the end of it where I want it to return somehow or figure out a way to call this stored procedure and retrieve the result.
Thanks in advance
ripern
-- Retrieves the value of column @columnName for credential id @credID
ALTER PROCEDURE [dbo].[PMC_User_GetUserValue]
@credID int,
@columnName nvarchar(50)
AS
DECLARE @userDataTable nvarchar(50)
DECLARE @userDataID int
DECLARE @statement nvarchar(500)
SET @statement = ' '
SET @userDataID =
(SELECT PMC_UserMapping.fk_userDataID
FROM PMC_UserMapping
INNER JOIN PMC_User ON PMC_UserMapping.fk_user_id = PMC_User.id
WHERE PMC_User.fk_credentials_id = @credID)
SET @userDataTable =
(SELECT PMC_UserType.userDataTable
FROM PMC_UserType
INNER JOIN PMC_UserMapping ON PMC_UserType.id = PMC_UserMapping.fk_usertype_id
INNER JOIN PMC_User ON PMC_UserMapping.fk_user_id = PMC_User.id
WHERE PMC_User.fk_credentials_id = @credID)
SET @statement = 'SELECT ' + @columnName + ' AS columnValue FROM ' + @userDataTable + ' WHERE id=' + convert(nvarchar, @userDataID)
-- Checks whether the given column name exists in the user data table for the given credential id.
IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=@userDataTable AND COLUMN_NAME=@columnName )
BEGIN
EXEC (@statement)
END
Goal: My request is the retrieve the return result from sp_Test as 8, 2, 4, 1 ,3 (take a look at picture 1) based on the chronological list from User-Defined Table Type dbo.tvf_id.
Problem: When I execute the stored procedure I sp_Test I retrive the list that is from 1 to 8. I don't know how to do it?
Information: I'm using SQL server 2012
create table datatable (id int,
name varchar(100),
email varchar(10),
phone varchar(10),
cellphone varchar(10),
none varchar(10)
);
insert into datatable values
[Code] .....
I need to send the result of a procedure to an update statement.Basically updating the column of one table with the result of aquery in a stored procedure. It only returns one value, if it didnt Icould see why it would not work, but it only returns a count.Lets say I have a sproc like so:create proc sp_countclients@datecreated datetimeasset nocount onselect count(clientid) as countfrom clientstablewhere datecreated > @datecreatedThen, I want to update another table with that value:Declare @dc datetimeset @dc = '2003-09-30'update anothertableset ClientCount = (exec sp_countclients @dc) -- this line errorswhere id_ = @@identityOR, I could try this, but still gives me error:declare @c intset @c = exec sp_countclients @dcWhat should I do?Thanks in advance!Greg
View 4 Replies View Related
I have an Execute SQL Task that executes "select count(*) as Row_Count from xyztable" from an Oracle Server. I'm trying to assign the result to a variable. However when I try to execute I get an error:
[Execute SQL Task] Error: An error occurred while assigning a value to variable "RowCount": "Unsupported data type on result set binding Row_Count.".
Which data type should I use for the variable, RowCount? I've tried Int16, Int32, Int64.
Thanks!
Hi all,
I copied the following code from Microsoft SQL Server 2005 Online (September 2007):
UDF_table.sql:
USE AdventureWorks;
GO
IF OBJECT_ID(N'dbo.ufnGetContactInformation', N'TF') IS NOT NULL
DROP FUNCTION dbo.ufnGetContactInformation;
GO
CREATE FUNCTION dbo.ufnGetContactInformation(@ContactID int)
RETURNS @retContactInformation TABLE
(
-- Columns returned by the function
ContactID int PRIMARY KEY NOT NULL,
FirstName nvarchar(50) NULL,
LastName nvarchar(50) NULL,
JobTitle nvarchar(50) NULL,
ContactType nvarchar(50) NULL
)
AS
-- Returns the first name, last name, job title, and contact type for the specified contact.
BEGIN
DECLARE
@FirstName nvarchar(50),
@LastName nvarchar(50),
@JobTitle nvarchar(50),
@ContactType nvarchar(50);
-- Get common contact information
SELECT
@ContactID = ContactID,
@FirstName = FirstName,
@LastName = LastName
FROM Person.Contact
WHERE ContactID = @ContactID;
SELECT @JobTitle =
CASE
-- Check for employee
WHEN EXISTS(SELECT * FROM HumanResources.Employee e
WHERE e.ContactID = @ContactID)
THEN (SELECT Title
FROM HumanResources.Employee
WHERE ContactID = @ContactID)
-- Check for vendor
WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc
INNER JOIN Person.ContactType ct
ON vc.ContactTypeID = ct.ContactTypeID
WHERE vc.ContactID = @ContactID)
THEN (SELECT ct.Name
FROM Purchasing.VendorContact vc
INNER JOIN Person.ContactType ct
ON vc.ContactTypeID = ct.ContactTypeID
WHERE vc.ContactID = @ContactID)
-- Check for store
WHEN EXISTS(SELECT * FROM Sales.StoreContact sc
INNER JOIN Person.ContactType ct
ON sc.ContactTypeID = ct.ContactTypeID
WHERE sc.ContactID = @ContactID)
THEN (SELECT ct.Name
FROM Sales.StoreContact sc
INNER JOIN Person.ContactType ct
ON sc.ContactTypeID = ct.ContactTypeID
WHERE ContactID = @ContactID)
ELSE NULL
END;
SET @ContactType =
CASE
-- Check for employee
WHEN EXISTS(SELECT * FROM HumanResources.Employee e
WHERE e.ContactID = @ContactID)
THEN 'Employee'
-- Check for vendor
WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc
INNER JOIN Person.ContactType ct
ON vc.ContactTypeID = ct.ContactTypeID
WHERE vc.ContactID = @ContactID)
THEN 'Vendor Contact'
-- Check for store
WHEN EXISTS(SELECT * FROM Sales.StoreContact sc
INNER JOIN Person.ContactType ct
ON sc.ContactTypeID = ct.ContactTypeID
WHERE sc.ContactID = @ContactID)
THEN 'Store Contact'
-- Check for individual consumer
WHEN EXISTS(SELECT * FROM Sales.Individual i
WHERE i.ContactID = @ContactID)
THEN 'Consumer'
END;
-- Return the information to the caller
IF @ContactID IS NOT NULL
BEGIN
INSERT @retContactInformation
SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType;
END;
RETURN;
END;
GO
----------------------------------------------------------------------
I executed it in my SQL Server Management Studio Express and I got: Commands completed successfully. I do not know where the result is and how to get the result viewed. Please help and advise.
Thanks in advance,
Scott Chang
HI,
I ran a select * from customers where state ='va', this is the result...
(29 row(s) affected)
The following file has been saved successfully:
C:outputcustomers.rpt 10826 bytes
I choose Query select to a file
then when I tried to open the customer.rpt from the c drive I got this error message. I am not sure why this happend
invalid TLV record
Thanks for your help
Ali
As the topic suggests I need the end results to show a list of shows and their dates ordered by date DESC.
Tables I have are structured as follows:
SHOWS
showID
showTitle
SHOWACCESS
showID
remoteID
VIDEOS
videoDate
showID
SQL is as follows:
SELECT shows.showID AS showID, shows.showTitle AS showTitle,
(SELECT MAX(videos.videoFilmDate) AS vidDate FROM videos WHERE videos.showID = shows.showID)
FROM shows, showAccess
WHERE shows.showID = showAccess.showID
AND showAccess.remoteID=21
ORDER BY vidDate DESC;
I had it ordering by showTitle and it worked fine, but I need it to order by vidDate.
Can anyone shed some light on where I am going wrong?
thanks
I am trying to code a WHERE xxxx IN ('aaa','bbb','ccc') requirement but it the return values for the IN keyword changes according to another column, thus the need for a CASE function.
WHERE GROUP.GROUP_ID = 2 AND DEPT.DEPT_ID = 'D' AND WORK_TYPE_ID IN ( CASE DEPT_ID WHEN 'D' THEN 'A','B','C' <---- ERROR WHEN 'F' THEN 'C','D ELSE 'A','B','C','D' END )
I kept on getting errors, like
Msg 156, Level 15, State 1, Line 44Incorrect syntax near the keyword 'WHERE'.
which leads me to assume that the CASE ... WHEN ... THEN statement does not allow mutiple values for result expression. Is there a way to get the SQL above to work or code the same logic in a different manner in just one simple SQL, and not a procedure or T-SQL script.
I'm using a subquery to return a delivery charge line as a column in the result set. I want to see this delivery charge only on the first line of the results for each contract. Code and results are below.
declare @start smalldatetime
declare @end smalldatetime
set @start = '2015-03-22 00:00' -- this should be a Sunday
set @end = '2015-03-28 23:59' -- this should be the following Saturday
select di.dticket [Contract], di.ddate [Delivered], di.item [Fleet_No], di.descr [Description], dd.min_chg [Delivery_Chg], dd.last_invc_date [Delivery_Invoiced],
[code]....
In this example, I only want to see the delivery charge of 125.00 for the first line of contract HU004377. For simplicity I have only shown the lines for 1 contract here, but there would normally be many different contracts with varying numbers of lines, and I only want to see the delivery charge once for each contract.
help strange result whan i do this
Code Snippet
SELECT unit_date, unit, ISNULL(NULLIF ((unit + DATEDIFF(mm, GETDATE(), unit_date)) % 4, 0), 4) AS new_unit
FROM dbo.empList
i try to get next unit value to next month
why i get this -1
on date
01/01/2008
1
-1
unit_date unit new_unit
01/02/2008
2
1
01/02/2008
1
4
01/01/2008
1
-1
01/02/2008
1
4
21/01/2008
1
-1
21/01/2008
1
-1
01/02/2008
1
4
TNX
HI Chaps
very simple question this time
Is there any method availabe to retrieve AM or PM value from date, IN TSQL
hoping will get answer shortly
regards,
Anas
I am using an SQLDataSource to add a product, this works fine, but I would like to know what syntax is used to retrieve the product ID in this case which is return by the SPROC
Thanks
Steve
I need to retrieve points column from my database for the specific user that is signed on and sum all of them. How can I do it using the Gridview and also code in vb for a label? Thanks.
View 4 Replies View RelatedI am trying to populate some controls on a web page with values retrieved from a sql server database recordset. The text type controls work fine. However I have a Check Box on my form. I get a runtime error when I try to write into it. So tried wrting the value into a text control and was suprised to see the value retrieved was "S00817". Heres the relevant line of code
Message.Innerhtml=MyDataset.tables(0).Rows(0)(12)
I would expect this to come up with a 1 or a 0. I get S00817 if the database record holds a 1 or a 0. So what is going on here?
Here is my sql procedure:
ALTER PROCEDURE dbo.SoftWareShow /* ( @parameter1 int = 5, @parameter2 datatype OUTPUT ) */ @SoftID uniqueidentifierAS SELECT [SoftID], [SoftName], [SoftJoinDate], [SoftSize], [SoftMode], [SoftRoof], [SoftHome], [SoftDemo], [SoftFirstClassID], [SoftSecondClassID], [SoftDesc], [SoftReadCount], [SoftDownCount],ltrim(rtrim([SoftUrlOne])) SoftUrlOne, ltrim(rtrim([SoftUrlTwo])) SoftUrlTwo, ltrim(rtrim([SoftUrlThree])) SoftUrlThree, ltrim(rtrim([SoftUrlFour])) SoftUrlFour FROM [SoftWare] WHERE ([SoftID] = @SoftID) RETURNwhere I retrieve data using sqldatasource, an error appear. how can do ?
i have a SQL backend and some of the data in my tables is more or less configuration data and some times i only want to pull one row of data which i can do just fine with a SQL query but whats the best method to pull it.
assume i have a query like this.
Select A, B, C from Table1 where ID=xyz
whats the easiest way to get A, B and C in to string variables?
I know i can use the sqldatasource control but i just feel there is too much overhead there.
whats your suggestions?
Thanks
Justin
I want to do something similar to ExecuteScalar in ADO.net but instead in T-SQL.Basically I want to do a query that will return the first value in the table queried and put it into a variable. How do I do this?Thanks in advance.
View 3 Replies View RelatedCould you help me out?
I am interested in retrieving certain record among outputs.
For example, if I use the following sql,
select * from info order by name asc
====================================
then I can retrieve 25 rows from info table.
What I want to do is that I want to retrieve 15th record only among possible 25 records.
How can I solve this problem?
Thanks in advance.
Hello!
is it possible to have the count(*) to display 0 when there is no matching hits for that n_id?
my query looks like this but only displays the n_id and it's respective count(*) when count(*) is more than 0...
select n_id, count(*) from tblTable
where nSomething > nSomethingElse AND nSomething IS NOT NULL
group by n_id
any idaes?
Hi all,
I want to retrieve the second maximum value of a column data present in SQL table.Please help....
A
----
10
25
23
15
here I want 23 as the result.
Hi folks,
Does anyone know how to retrieve the date when data has been inserted
thnx in advance
i have a table called tblpictures which look something like this..
filename|ID
----------
1 |p1
2 |p1
3 |p2
4 |p2
5 |p3
is there a way to retrieve only 1 row for each ID? how will the select statement looks like?? please help me..
hi gurus,
how to get " using bcp
e.g
select '"'+name+'"' from sysobjects
results
"name"
"name1"
I want to get the same using bcp ,so that i can populate into a file
something like
set @cmd='BCP "select '"' +name +'"' ,uid from sysobjects" QUERYOUT "' + 'vrs.txt' + '" -w -T -S -t , '
-- Executing the BCP Command
EXEC MASTER..XP_CMDSHELL @Cmd
I have a question, one user made mistake that she delete one record from a database. How can i retrieve this only one record. I just know how to restore the database.
Thanks.
Can any one please tell me how to retrieve values from timestamp column.
I am getting 1900-01-01 00:02:09.457. It is not storing current timestamp when record is created or modified.
Is there anything I need to set it up.
thanks
Hi, all.
I tried to get result of sp.
Dim rst As Recordset
Set rst = New ADODB.Recordset
rst.Open sp, cnn
doesn't get result.
when i call rst.EOF, it thows error: Can do this since rst is closed...
I found it's problem of sp which is little complex.
But, still I think it should work!
My question is how can I get the returned value from following sp in VB?
--- return list of tables that needed to update
--- list is one string separated by '&' delimeter
--- @listOfUpdateTime: [TableName=UpdateTime]&[...] eg) tblDeptList=12/25/2004&tblHoliday=12/24/2004&...
CREATE procedure spGetListOfTableToDownLoad
@listOfUpdateTime varchar(500)
as
SET NOCOUNT ON
Declare @listOfTable varchar(300), @item varchar(300)
Declare @tbl varchar(50), @uptime datetime
Declare @list varchar(500)
Declare @sep varchar(1)
SET @list = ''
SET @sep = '&'
DECLARE cur CURSOR FAST_FORWARD FOR
SELECT * FROM fnSplit(@listOfUpdateTime, @sep)
OPEN cur
FETCH NEXT
FROM cur
INTO @item
Declare @re bit, @tp varchar(50)
WHILE @@FETCH_STATUS = 0
BEGIN
-- get tablename, update time
Declare cur2 CURSOR FAST_FORWARD FOR
SELECT * FROM fnSplit(@item, '=')
OPEN cur2
FETCH NEXT FROM cur2 INTO @tbl
Print @tbl
FETCH NEXT FROM cur2 INTO @tp
print 'tp:' + @tp
SET @uptime = CAST(@tp as datetime)
print @uptime
-- @re =1: true, 0: false
EXEC spIsUpdate @tbl, @uptime, @re output
IF @re = 1
SET @list = @list + @tbl + @sep
CLOSE cur2
DEALLOCATE cur2
FETCH NEXT
FROM cur
INTO @item
END
if LEN(@list) > 0
SET @list = LEFT(@list, Len(@list)-Len(@sep))
CLOSE cur
DEALLOCATE cur
SELECT @list as Result
SET NOCOUNT OFF
GO
__________________
--- PARAM:: @tbl: table name,
--- @uptime : update time (passed from local db) that will be compared on HQ table
--- return 1 if Max(UpdateTime) of @table > @uptime
--- otherwise return 0
CREATE Procedure spIsUpdate
@tbl varchar(50), @uptime datetime, @result bit output
as
BEGIN
Declare @bit bit
DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)
declare @uptimeHQ datetime
/* Build the SQL string once.*/
SET @SQLString = N'SELECT @tp = MAX(UpdateTime) FROM ' + @tbl
SET @ParmDefinition = N'@tp datetime OUTPUT'
EXECUTE sp_executesql @SQLString, @ParmDefinition
,@uptimeHQ OUTPUT
If @uptimeHQ > @uptime
SET @result = 1
ELSE
SET @result = 0
--RETURN @bit
END
GO