I think this is simple, but I don't know how to do it.I have a table with 13 decimal type columns. I want to return columns in my query where the value of the column is greater than 0. What I think is to do a nested select within my WHERE statement, but that seems clunky, is there a better way?
View 12 Replies
I have the following simple stored procedure:
ALTER PROCEDURE GetadiByPhiNo
@PhiNo int
SELECT adirefno, adidescription
FROM aditable
WHERE adiphino = @PhiNo
Here is an example of data returned:
1 Skin diesease
2 Cough
3 Cough
4 Rash
You notice "Cough" is in the adidescription column twice. I would like to return only one row for adidescription with the same value. So what I want returned is this:
1 Skin diesease
2 Cough
4 Rash
I have an update function in my data layer which is defined as:
public int UpdateRBTable(parameters ...) This calls a SQL Server Stored Procedure to perform an update function on the database.
The process does its job for updating the table. However, the stored procedure has a return value (which indicates how many rows were updated), but this return value is not returned to the application. The application always shows that a zero was returned from the stored procedure.
i am making relation between two datatables and m getting this error
in this ds.Tables(0).Columns("In_ID") datatype is string
ds.Tables(1).Columns("row_id") datatype is integer
how i do the type casting here to make dataset relation
ds.Relations.Add("Rel_1", ds.Tables(0).Columns("In_ID"), ds.Tables(1).Columns("row_id"), True)
I want to take my return value from sql server to c#.
my code
SQL SERVER:
ALTER procedure [dbo].[userss](@email varchar(100),@password varchar(25),@name varchar(250),@DOB varchar(20),@acc_vari int,@ip_address varchar(50),@vari_code varchar(7) )
as
begin
declare @uid int
select @uid=uid from users where email='12we'
if(@uid>0)
return 0
else
insert into users values(@email,@password,@name,@DOB,@acc_vari,@ip_address,@vari_code )
end
In this i want to take return value "return 0 " from sql server to c#.
I am new to SQLite hence the question:I am trying to execute these statement to insert a record into the table and get the ID of the newly inserted column. There is blahID column defined as auto-increment, PK.I want to get the value of @ReturnValue below:
[Code]....
[Code]....
It dies in setting parameter direction:newSqlParam.Direction = ParameterDirection.ReturnValue
I have an application that I am working on. It displays a list of employees that were born on the current month/day. I do not have a seperate column in the database for firstname and last name. The database was already in place when I started here. I need to be able to display only the last name of the users. The name are formatted like Doe, John A. Would I only be able to display up to the comma in the name? My current SQL Query is below.
[Code]....
how to return two ids from the same table, i need to create a cursor, it has to use two
EID details of different LID of the same table.
[Code]....
The above query is wrong, it is getting duplicate rows, so i need to optimize it ti return the two values in two different variables in cursor.
I have written an insert stored procedure which is called by my business logic layer, I want to return the new ID to the function once it has executed but i'm not sure how.
My SP uses RETURN SCOPE and when I have tested this it works but how I get that into a variable in the BLL function I don't know.
I am new to Sql server 2005. I have a table in which contains many data. I need to take particular data. For example, I want to take value of 110652.795813712 from FTEBASEPAY column. So i have wrote the sql statment like in below. SELECT * From tblEmployees where FTEBASEPAY='53842.7782655718' But i am not able to get the particular value. Manually i have seen the tblEmployees table, in which contains the particular data ('53842.7782655718'). When i execute the above select statement, there is no result for it. What i have to for overcome this issue?I have to give one more information, the FTEBASEPAY dataType is float in tblEmployees table.
View 4 RepliesHere is the deal. I need to create a Stored Procedure that has 2 SELECT statements. Now in ASP.NET, I know that a DataSet can have multiple tables and the DataAdapter can do just that. I am stuck with a scenario where I need to get a value from the first SELECT statement which will later be used in the second SELECT statement. I am creating a SP that passes one parameter (@AnswerID). Which should give me 2 resultsets. See below SELECT statements.
[Code]....
So how do I get the QuestionID from the first SELECT statement?
Basically I am creating a Questionnaire that has Question Dependacies. So from the above SQL statements I would like to display the Question Title with it's relevant answer options but depending on the previous Answer selected hence the @AnswerID parameter.
The reason why I want to use 2 SELECT statements is because I don't want to have 2 roundtrips to th server. So in my code the DataAdapter should return 2 resultsets and fill the DataSet.
If there is any another solution that can prevent 2 roundtrips to the server, I would most definitely like to know how to do it.
I not getting the return value from stored procedure that I have written so how to get that plzz its urgent I have return the procedure as ALTER PROCEDURE [dbo].[sp_AddPaySlip]
View 2 RepliesI have faced the peculiar case in Sql server's Inner join. I have 2 tablesFor example:Table Name: A and B I have shown the below code.
Select
* From A
Inner
Join B
ON A.JobCode
=B.JobLookupID
This select statement return 500000 records but both table did not have that much recordsI have shown the Records in every table:A=19396 and B= 25366 records.How it will return more then 5 lakh records in above select statemenet?. Please let me know if any one faced this issue earlier?
I am generating links for emails within a class in the model of my MVC site and need to obtain the URL address of the host server the site is running on.
How would I get this?
i have update sp for users table ,,, i want to store in "Transactions Table" which exactly the fields updated and what is the old value and new value ,, just the updated !!look at this sp code : [Code]....
my question is :'@@column-name' i want to return the parameter for example ---> @user_name ,,, but how ?
i always got the empty string in form data posting. can you describe what the hell happened in my code? always got the empty string to insert to SQL server even it is drop down list selected value is empty. Is this the error in visual studio cause sometimes i can get the value and sometimes can't (inconsistent!!!)
Protected Function sqlparameter_insert_newscat() As SqlParameter()
Dim sqlparam(4) As SqlParameter
Dim ddlstatus As Boolean
If rbActive.Items(0).Selected = True Then
[code]...
using sql server 2005. I have table setup as follows
Table: Orders
Col: CustomerID
Col: OrderID
Col: OrderDate
so a given Customer can have many orders in this table however I only want to return the last order (using OrderDate) for each customer. how do you do this? I assume that I need to group by customerID and then use max on the OrderDate but not sure of the exact syntax.
Long story short, it would be a lot easier for me to just return two values (from a many-column table, like combining columns) than to combine them in my asp project. I'm just having many problems with binding to a dropdownlist, and it would be easier not having to guess what index the column is I need :)
So, let's just say my table, "Building", has 5 columns - BuildingID, PracticeID, PracticeName, Address, & Zip (there are many more in my real table).
I already have a stored procedure that is "SELECT * FROM Building ORDER BY PracticeName". What I would like, is something that returns column values together with literal characters, like:
PracticeID - PracticeName (Address) AS First
I know I could use "SELECT PracticeName AS Name FROM Building" to simply change the name of the column I'd receive, but I want to add literal characters ( "-" and "(" and ")" and " ") with specific columns I choose.
Since I have about 20 columns in my table and I only want about 6, I don't need to SELECT *, but only those 6. That's easy enough. But since I would still like these six columns combined, I'm not sure what to do.
Again, what I want is to return something like:
PracticeID - PracticeName (Address) AS First BuildingID - Zip AS Second the "-" and "()" and " " need to be included and returned from SQL, not when I retrieve them)
I know this has to be easy. I'm just not sure of the best method to use. Should I be using ViewData?
Table 1 has columns:
ID
Name
TypeID
Table 2 has columns:
TypeID
TypeDescription
The tables are linked by a key on the TypeID column. I want to display:
Table1.ID
Table1.Name
Table2.TypeDescription
Is there a way to row two and three of the result set be returned so that the
following query:
[Code]....
how to return list of values(single column values) from stored procedures in sqlserver 2005?
View 4 RepliesIn my asp.net page I pass paremeters to a stored procedure and then inside a try blog the foloving line: affectedRows = ssqldbsorce.insert
never returns expected result from stored procedure.
Inside stored procedure event if I explicitely write the following line "return 5" for example, it never returns 5, only -1, 1, 2
For example here a very simple one without output parameters(I still should be able to return a value without output parameters, right?):
ALTER PROCEDURE [dbo].[createUser]
@regCode varchar(10)
AS
SELECT * FROM dbo.reg_code WHERE reg_code=@regCode
....
---other statements, and for the sake of the example lets return 5
RETURN 5
Is there something wrong with doing it thi way, here is also a tutorial, according to which it should work just fine: [URL]
Is there another way maybe in the assp.net page to capture the value in the variable from a stored procedure.
Using SQL Server 2005.
I need to return the year only from a datetime field and then group by that field, does anyone know the syntax for this?
I still have plenty to learn about SQL... I'm pretty sure what I want to do is possible, but I'm getting lost in the syntax.
I have two tables:
TABLE A: [ID] [Name]
TABLE B: [ID] [TableA.ID] [intValue] [intTypeID]
There are 3 pieces of information I want:
Total Sum(TableB.intValue)
Sum(TableB.intValue) WHERE intTypeID = 1
Sum(TableB.intValue) WHERE intTypeID = 2
Getting one of those pieces is simple: SELECT a.ID,a.Name,SUM(b.intValue) WHERE a.ID = x GROUP BY a.ID,a.Name
I assume I need some kind of JOIN, but I can't seem to get it..
I've got an ActionMethod that returns a JsonResult object and this isn't returning data to the calling jquery function. I have used wget to send and receive raw data to it, and I've discovered that when the object dataset is empty, it returns a value fine and the wget return is
HTTP request sent, awaiting response... 200 OK
Length: 78 [application/json]
Saving to: `filename'
but when there is data in the object dataset I get an internal server error:-
HTTP request sent, awaiting response... 500 Internal Server Error
2010-03-05 13:21:16 ERROR 500: Internal Server Error.
This is a dataset of objects being returned - are there any common "gotchas" when doing this?
