In my program i have function that will get one value from Database. Here i want to assign the output of the sql query to a local variable. Its like select emp_id into Num from emp where emp_roll=222; here NUM is local variable which was declared in my program. Is it correct.? can anyone please guide me..?
I have a user login scenario where I would like to make sure that they not only exist in the user table, but also make sure there account is "verified" and "active". I'm trying to return 3 output parameters. UserID, verified, active. Is this possible?
Do I need just a select statement to do this? What is the difference between the output and select statements?
the table has many duplicate entries, where COL2 is the primary key and unique, but its the duplicate COL1 entries that have to be removed.
I was hoping a simple "delete from table where COL1 not in (select COL1, min(COL2) from TABLE group by COL1)"
would do the trick, but obviously in returning two columns from the subselect this won't work. Can I hide the COL2 output from the query that will be put in the subselect?
this is a one-off thing, so i'm not overly concerned about overhead or elegance. just need to make it so.
Is there a way to build a select statement that will output related rows with different column data per row? I want to return something like:
rowtype| ID | value A | 123 | alpha B | 123 | beta C | 123 | delta A | 124 | some val B | 124 | some val 2 C | 124 | some val 3 etc...
where for each ID, I have 3 rows that are associated with it and with different corresponding values.
I'm thinking that I will have to build a temp table/cursor that will get all the ID data and then loop through it to insert each rowtype data into another temp table.
i.e. each ID iteration will do something like: insert into #someTempTable (rowtype, ID, value) values ('A', 123, 'alpha') insert into #someTempTable (rowtype, ID, value) values ('B', 123, 'beta') insert into #someTempTable (rowtype, ID, value) values ('C', 123, 'delta') etc..
After my loop, I will just do a select * from #someTempTable
Is there a better, more elegant way instead of using two temp tables? I am using MSSQL 2005
I have a routine that generates an HTML email and sends it just fine, but one of the columns ends up with 4 decimal places for a column datatype of money. How can I get the script to output only 2 decimal places for the amount column from the select statement?
I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed. If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian
Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".
Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.
hiI need to write a stored procedure that takes input parameters,andaccording to these parameters the retrieved fields in a selectstatement are chosen.what i need to know is how to make the fields of the select statementconditional,taking in consideration that it is more than one fieldaddedfor exampleSQLStmt="select"if param1 thenSQLStmt=SQLStmt+ field1end ifif param2 thenSQLStmt=SQLStmt+ field2end if
Code Block SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02' FROM myTables WHERE Conditions are true ORDER BY Field01
The results are just as I need:
Field01 Field02
------------- ----------------------
192473 8461760
192474 22810
Because other reasons. I need to modify that query to:
Code Block SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02' INTO AuxiliaryTable FROM myTables WHERE Conditions are true ORDER BY Field01 SELECT DISTINCT [Field02] FROM AuxTable The the results are:
Field02
----------------------
22810 8461760
And what I need is (without showing any other field):
Field02
----------------------
8461760 22810
Is there any good suggestion? Thanks in advance for any help, Aldo.
Hello friends, I want to use select statement in a CASE inside procedure. can I do it? of yes then how can i do it ?
following part of the procedure clears my requirement.
SELECT E.EmployeeID, CASE E.EmployeeType WHEN 1 THEN select * from Tbl1 WHEN 2 THEN select * from Tbl2 WHEN 3 THEN select * from Tbl3 END FROM EMPLOYEE E
can any one help me in this? please give me a sample query.
This may be a basic question, but defining anything other than a cursoris preffered.I have, as an example, 2 tables. One with customer data (addresses,phones, etc), the other is a listing of all 50 states (a cross referencefor short state alias to long state name, i.e. FL - Florida, etc...).I want to sort the out put by state long name, and show each customer inthe state ... BUT ...the output needs to be like so:FloridaABC,Inc Address1 City, State Zip, other InfoDummy Corp Address1 City, State Zip, other Info...GeorgiaXYZ, Inc Address1 City, State Zip, other Info...etc ...This is a basic heirarchical listing. Can this be done with a singleT-SQL statement or are cursors needed?Thanks in advance."Excellence is achieved through 1% inspiration and 99% perspiration." A.Einstein*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
I tried wrapping a DELETE FROM ...OUTPUT DELETED.* WHERE... inside of a select, crossed my fingers and got a syntax error. I thought maybe since the OUTPUT statement produces a rowset that it and it's DELETE could be selected from. Is such a thing possible in the same query or should I give up?
I am currently straching my head as to why the following doesn't work as I expect. I have Three tables UsersManagers, Users and Bookings:-
SELECT MAX(UsersManagers.UsernameUser) AS UserID, SUM(Bookings.HoursTotal) AS NumHours FROM UsersManagers LEFT OUTER JOIN Users ON Users.Username = UsersManagers.UsernameUser LEFT OUTER JOIN Bookings ON Bookings.Username = Users.Username WHERE UsersManagers.UsernameManager = 'testPM1' AND (Bookings.DateOfBooking BETWEEN '01 October 2006' AND '31 October 2006') GROUP BY UsersManagers.UsernameUser
This statement returns only one username with the hours they have booked in my Bookings table. However the left outer joins (I would have thought) should return an entry even if there are no hours booked for a user. If I omit the Date Section:
SELECT MAX(UsersManagers.UsernameUser) AS UserID, SUM(Bookings.HoursTotal) AS NumHours FROM UsersManagers LEFT OUTER JOIN Users ON Users.Username = UsersManagers.UsernameUser LEFT OUTER JOIN Bookings ON Bookings.Username = Users.Username WHERE UsersManagers.UsernameManager = 'testPM1' GROUP BY UsersManagers.UsernameUser
I get two records returned testUsr1 37.5 testUsr2 NULL
I have 3 tables, with this relation: tblChats.WebsiteID = tblWebsite.ID tblWebsite.AccountID = tblAccount.ID
I need to delete rows within tblChats where tblChats.StartTime - GETDATE() < 180 and where they are apart of @AccountID. I have this select statement that works fine, but I am having trouble converting it to a delete statement:
SELECT * FROM tblChats c LEFT JOIN tblWebsites sites ON sites.ID = c.WebsiteID LEFT JOIN tblAccounts accounts on accounts.ID = sites.AccountID WHERE accounts.ID = 16 AND GETDATE() - c.StartTime > 180
Hey guys i have a stock table and a stock type table and what i would like to do is say for every different piece of stock find out how many are available The two tables are like thisstockIDconsumableIDstockAvailableconsumableIDconsumableName So i want to,Select every consumableName in my table and then group all the stock by the consumable ID with some form of total where stockavailable = 1I should then end up with a table like thisEpson T001 - Available 6Epson T002 - Available 0Epson T003 - Available 4If anyone can help me i would be very appreciative. If you want excact table names etc then i can put that here but for now i thought i would ask how you would do it and then give it a go myself.ThanksMatt
Another question for all you SQL experts, I have a lot of them. I am trying to select from a table wher some conditions need to be met based on an employee ID. What I am doing is when the rank is a 1,2, or 3 I pick up the text description of that rank. Can I make it so that I get the ID only once and all the text descriptions are on the same line. Here is the sql script along with my current output and my desired output.
--SQL SCRIPT__
select C.empID, B.Rank,B.Text1 as Skills from tbl_survey_rank B , tbl_survey_valueID C where PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and C.empID = '2924'and (B.Rank ='1'or B.Rank ='2' or B.Rank ='3')
--CURRENT OUTPUT--
empID Rank Skills ------- ---- -------------------------------------------------- 2924 1 Create Documents 2924 2 Mail Merge 2924 3 Create Header and footer 2924 3 Create Spreadsheet 2924 3 Joining Tables in a Query
--DESIRED OUTPUT--
empID Rank Skills ------ ---- ------------ 2924 1 Create Documents 2924 2 Mail Merge 2924 3 Create Header and footer, Joining Tables in a Query, Create Spreadsheet
In my previous post I asked how to do the bottom question. I got a response to use a cursor, now I made an attempt to use a cursor but I still get the same response. Any help will be greatly appreciated.
--CURRENT OUTPUT--
empID Rank Skills ------- ---- -------------------------------------------------- 2924 1 Create Documents 2924 2 Mail Merge 2924 3 Create Header and footer 2924 3 Create Spreadsheet 2924 3 Joining Tables in a Query
--DESIRED OUTPUT--
empID Rank Skills ------ ---- ------------ 2924 1 Create Documents 2924 2 Mail Merge 2924 3 Create Header and footer, Joining Tables in a Query, Create Spreadsheet
--Here is the cursor script.--
Declare @skills varchar(255),@skills2 varchar(255),@message varchar(255),@empID varchar(255), @Rank varchar(255) DECLARE emp_skills CURSOR For select C.empID, B.Rank,B.Text1 as Skills from tbl_survey_rank B , tbl_survey_valueID C where PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and C.empID = '2924'and (B.Rank ='1'or B.Rank ='2' or B.Rank ='3') or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and B.Rank ='3'and C.empID = '2924' or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and B.Rank ='3'and C.empID = '2924' or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and B.Rank ='3'and C.empID = '2924' or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and B.Rank ='3'and C.empID = '2924'
DECLARE emp_skills2 CURSOR For select B.Text1 as Skills from tbl_survey_rank B , tbl_survey_valueID C where PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and C.empID = '2924'and (B.Rank ='1'or B.Rank ='2' or B.Rank ='3') or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and B.Rank ='3'and C.empID = '2924' or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and B.Rank ='3'and C.empID = '2924' or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and B.Rank ='3'and C.empID = '2924' or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and B.Rank ='3'and C.empID = '2924' OPEN emp_skills OPEN emp_skills2 FETCH NEXT FROM emp_skills into @empID, @Rank, @skills FETCH NEXT FROM emp_skills2 into @skills2 WHILE @@FETCH_STATUS = 0 BEGIN SELECT @message = @skills2 FETCH NEXT FROM emp_skills2 into @skills2 Print @empID + ' '+ @Rank + ' ' + @message
FETCH NEXT FROM emp_skills into @empID, @Rank, @skills
End CLOSE emp_skills DEALLOCATE emp_skills CLOSE emp_skills2 DEALLOCATE emp_skills2
--Previous Post--
Another question for all you SQL experts, I have a lot of them. I am trying to select from a table wher some conditions need to be met based on an employee ID. What I am doing is when the rank is a 1,2, or 3 I pick up the text description of that rank. Can I make it so that I get the ID only once and all the text descriptions are on the same line. Here is the sql script along with my current output and my desired output.
--SQL SCRIPT__
select C.empID, B.Rank,B.Text1 as Skills from tbl_survey_rank B , tbl_survey_valueID C where PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and C.empID = '2924'and (B.Rank ='1'or B.Rank ='2' or B.Rank ='3')
--CURRENT OUTPUT--
empID Rank Skills ------- ---- -------------------------------------------------- 2924 1 Create Documents 2924 2 Mail Merge 2924 3 Create Header and footer 2924 3 Create Spreadsheet 2924 3 Joining Tables in a Query
--DESIRED OUTPUT--
empID Rank Skills ------ ---- ------------ 2924 1 Create Documents 2924 2 Mail Merge 2924 3 Create Header and footer, Joining Tables in a Query, Create Spreadsheet
Hi friends, I want to select only filename column of sp_helpdb's output.But I don't know how should I do this? Other way is doing a direct select from sysfiles or sysdatabases but as you know microsoft doesn't recommand this way. Also I can select that filed in my application level but it is strange for me: Of course sql server should has a proper way to do a select from output curosr of this procedure,Doesn't it? -Thanks in advance
I'm trying to get the data out of the database in a columed format, I would be quite comfortable doing this if 'in' and 'out' where there own field. The output i'm trying to get is:
name |data |data |
test1 |123 |456 test2 |123 |456 test3 |123 |456
in an ideal world i'd like to be able to have the header the name of the field data - eg
name |in |out |
test1 |123 |456 test2 |123 |456 test3 |123 |456
I can use - select name,field1data from table where field1 in ("in","out")but this returns each piece of data as a new row - eg:
SELECT Top 10 Name, Contact AS DCC, DateAdded AS DateTimeFROM NameTaORDER BY DateAdded DESC I'm trying to right a sql statement for a gridview, I want to see the last ten records added to the to the database. As you know each day someone could add one or two records, how can I write it show the last 10 records entered.
Here is my problem: I have a strored procedure that inserts some records
into one table and then selects some records from another table at the end.
The stored procedure takes several parameters, first one of them is marked as
OUTPUT. I'm using it to return an id of the inserted record. The procedure is called from asp.net code with first parameter set as ParameterDirection.InputOutput (tried with just Output as well). Now for the problem: if the the select statement at the end returns 0 records everything works and i my first parameter contains the @@IDENTITY value from the insert statement like it is supposed to.
If the select statement at the end returns 1 or more records my output parameter is not updated at all and contains the same value as before the procedure was run. All the records are inserted correctly.
if i try to return the @@identity as a plain select statement instead of through the parameter i get System.DBNull.
I hope you can shed some light on this for me. Here is my stored procedure:
INSERT INTO INQUIRY (LibraryName, ContactName, Address, City, State, Zip, Phone, Email, Comment) VALUES(@libraryName, @contactName, @address, @city, @state, @zip, @phone, @email,@comment)
--i tried including this statement at the end as well but that did not do the
--trick either
select @inquiryId=@@IDENTITY FROM INQUIRY
set nocount on declare @separator_position int -- This is used to locate each separator character declare @objectId varchar(200) -- this holds each array value as it is returned
if(@productIds is not null) begin while patindex('%,%' , @productIds) <> 0 begin select @separator_position = patindex('%,%' , @productIds) select @objectId= left(@productIds, @separator_position - 1) INSERT INTO PRODUCT_INQUIRY_LOOKUP (ProductId,InquiryId) VALUES(@objectId, @inquiryId) select @productIds = stuff(@productIds, 1, @separator_position, '') end end set nocount off
Select Distinct Email from vPRODUCT_CONTACT WHERE ProductId in (Select ProductId From Product_Inquiry_Lookup Where InquiryId=@inquiryId)
I have a stored procedure where in a cursor I create a dynamic select statement. If I have a non-zero result set (check @@rowcount) I output a message. The problem is I do not know how to supress the output of the execute of the dynamically created select statement. All I want going to output is the message. I know ISQL has this function, and so does EM. How do I do it totally within a stored procedure.
