.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Post New Web Links

instead of a buncha sql select statements

Posted By:      Posted Date: October 18, 2010    Points: 0   Category :Sql Server
I have a query that i need to enter facility code and location code from an excel spreadsheet.... The only problem is that sometimes there is only facility code and other times there is facility code and location code.....  
Select e.FirstName, e.LastName, e.Address1, e.Address2, e.City, e.State, e.ZipCode, f.Code as FacilityCode, f.Name from Employees e 
join Locations l on l.Id= e.LocationId 
join Facilities f on f.Id = l.FacilityId 

where f.Code=
and l.Code=

and e.HewittEmploymentStatus='active' 
  • and e.FullTimeStatus='f' order by f.Code
  • FAC=
  • LOC=
  • 800
  • 544
  • 802
  • 345
  • 0001
  • 334

More Related Resource Links

configure both select and Update statements in ADO.NET

 i would like to retrieve a value with select and perform a calculation and then save using update in the same batch code?  

Error: "Select statements included within a function cannot return data to a client"

hi all, Not sure if there is an easier way to do this but this is my first ever Function I created so far. I am getting the error: "Select statements included within a function cannot return data to a client" All I am trying to do is ...if the date is NOT passed then apply NULL as the default value and run the SELECT statement. If the date IS NOT null then run a different SELECT statement. CREATE FUNCTION [SchemaTest].[fn_Test] ( @instance as uniqueidentifier ,@date as datetime = null ) RETURNS varchar(8000) AS BEGIN If (@date is null) --@date = null ?? Begin Select * From Uds.RealtimeLogging Where instanceID = @instance End Else Begin Select * From Uds.RealtimeLogging Where instanceID = @instance and [timeStamp] > @date End Return @stepIds END Thank you!JCD

Having subqueries within select statements

I want to know whether there are any consequences of having sub queries within a select statement so for example writing queries using this style.     SELECT NAME ,PROFILE CONVERT(VARCHAR(10), (SELECT PROFILEDATE FROM tblTable WHERE ProfileID= tst.[ID] AND [ProfileDate]=(SELECT MAX([ProfileDate]) FROM [EtpForecastDividendsStaging] WHERE [ProfileID]=qry.[ProfileID] AND [ProfileDate]<qry.[ProfileDate])), 120) , CONVERT(VARCHAR(10), (SELECT MAX([ProfileDate]) FROM tbltable WHERE [ProfileID]=qry.[ProfileID] AND [ProfileDate]<qry.[ProfileDate]), 120) FROM tbltable join tblothertable The above example is just a sample and not a query i had expect to compile, i am just using it as an illustration. I believe its bad programming, but i may be wrong. I tend to like normal traditional joins where you select the column and join based on the corresponding keys, but I am trying to grasp some justification for my approach.   Any ideas    

I have the following 4 select statements.. but the 4th one doesnt return what i think i should be ge


I have a report that is presented to the client. It consists of "Scheduled", "Uncompleted", "Completed" and "All". Now what I PERSONALLY think these reports should return for the first 3 should total what is presented in the "All" report.

if i run the report "Scheduled" it returns 0
if i run the report "Uncompleted" it returns 157
if i run the report "Completed" it returns 59
But when i run the "All" report it returns 210

Which leaves a difference of 6. Can anyone tell me what im doing wrong with the following 4 select statements?

Here is the scheduled report:
select p.permit_num,K.task_id,K.task_due_date,K.date_completed,K.complete_ind,K.inspRpt_ind,C.*, R.service_rep_name,L.*,S.sys_name,M.manu_name, SI.sentAgc_date 
from tasks K, service_contracts C,service_reps R,locations L, manufacturers M, sys_inspection SI, systems S left outer join sys_permits sp on sp.sys_id = s.sys_id left outer join permits p on p.permit_id = sp.permit_id and p.active = 1 
where K.service_contract_id=C.service_contract_id and C.sys_id=S.sys_id and C.service_rep_id=R.service_rep_id and M.manu_id=S.manu_id and S.location_id=L.location_id and

INSERT the Results of a Stored Procedure with Multiple SELECT Statements into Multiple Tables?

I have a stored procedure (which is overly complex and written by someone else) that I need to take the results of and put them into tables.  The stored procedure uses Dynamic SQL to build and then run 5 SELECT statements based on literally hundreds of variables.  I need to take the results of these SELECT statements and put them into tables in a different database.  Rewriting or re-creating the logic of the stored procedure is NOT AN OPTION as the logic may change over time and maintaining it in multiple places would be nightmarish.  Can anybody help me with this?  I've googled extensively, but have only been able to find examples of using one result set not multiple ones.

MSSQL 2005 JDBC driver multiple select statements in stored procedure



we have a stored procedure with multiple select in it.



Select a,b,c from table1

Select f,g,h  from table2

Select j,k,l  from table3


Sometimes select no 2 will not return any values.

when this happens  we get only 2 result sets using CallableStatement getMoreResults method. 

it seems that if a select  doesn't return something it will  not have a corresponding resultset. 

On 2000 (driver + server) version we got an empty resultset for this situation. 

Is there  any  way to have the same behaivor with 2005 ?   We tryed with 1.2 and 2.0 jdbc driver.




Two select statements Combined?


OK here is what I am rocking with.....


--select distinct l.Name as LocationName,l.Code as LocationCode, f.Name as FacilityName, f.Code as FacilityCode
--from Locations l 
--inner join Facilities f on f.Id=l.FacilityId


This t-sql statement above selects every Facility/Location combination  695 combos



Select e.FirstName,e.LastName, am.Password, au.UserName,
 l.Code as locationCode, l.Name, f.Code As FacilityCode,f.Name as facilityName

from Employees e
inner join aspnet_Membership am on am.UserId=e.UserId
inner join aspnet_Users au on au.UserId=e.UserId
inner join Locations l on l.Id=e.LocationId

Why SELECT statements obtains Exclusive Locks?


My understanding below report indicates that process52bf948 obtained exclusive lock on PK_tblTrips, while statement which is being executed is SELECT. Why would that happen?

<process id="process52bf948" taskpriority="0" logused="94912" waitresource="KEY: 5:72057594122600448 (bd0015f6d794)" waittime="4496" ownerId="362013974" transactionname="user_transaction" lasttranstarted="2010-10-27T11:06:43.880" XDES="0x13cff2f970" lockMode="S" schedulerid="21" kpid="3260" status="suspended" spid="208" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2010-10-27T11:10:53.367" lastbatchcompleted="2010-10-27T11:10:53.367" lastattention="2010-10-27T11:10:03.003" clientapp="Internet Information Services" hostname="WEBAPP6" hostpid="3036" loginname="masteruser" isolationlevel="read committed (2)" xactid="362013974" currentdb="5" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">

AJAX GridView Checkbox Select All in ASP.Net

In the previous article GridView Select Multiple Rows you learnt the multiple row selection in GridView control. Here you will get the C# code sample to select all the gridview rows using single checkbox server control. You can highlight the selected rows with different color and Font state Bold. If you want to use the selection values on different ASP.Net

ListView DataPager with SQL-Statements that already return only paged data.



As far as I understand the ListView-Control and the DataPager-Control, they work like this: The ListView gets its datarows from its data source, then the DataPager causes the ListView to display only a subset of those rows.

So what, when I want to use custom databinding for the ListView, that *already* gets the certain subset from SQL-Server (for better performance). Can I and how can I use the DataPager then?

Thanx, S.

Select value in gridview


Hi, I am new to gridview. I want to make a gridview. Its row when selected should direct me to a specific .aspx page.Each row should direct to a different page. Should I use querystrings for this? Please suggest. Regards, ap.

The XML Files: XSLT Keys, Select vs. Match, Conflict Resolution, and More


This month Aaron Skonnard covers how keys work in XSLT, XSLT match and select attributes, SelectNodes order, finding