Stored Procedure Using UNION Joins Is Not Displaying Correctly... Can Someone Help Me With My Logic?
May 16, 2007
I have a stored procedure using UNION joins on three SQL queries.
Sadly, I'm only now learning how to use Stored Procedures, so if this is a really dumb question, please forgive me. I'm not used to big UNION statements like this either... usually I'm just programming websites to serve information out pretty simply :)
I need to return one result set, sorted by date... one complete result per day.
eg:
5/15/2007 | XX | XX | XX | XX | XX | XX |
5/16/2007 | XX | XX | XX | XX | XX | XX |
5/17/2007 | XX | XX | XX | XX | XX | XX |
Currently, when I run the query, I'm getting three separate date values for each date...
eg:
5/15/2007 | XX | XX | 00 | 00 | 00 | 00 |
5/15/2007 | 00 | 00 | XX | XX | 00 | 00 |
5/15/2007 | 00 | 00 | 00 | 00 | XX | XX |
5/16/2007 | XX | XX | 00 | 00 | 00 | 00 |
5/16/2007 | 00 | 00 | XX | XX | 00 | 00 |
5/16/2007 | 00 | 00 | 00 | 00 | XX | XX |
etc
How do I fix this? I've looked through my query ad naseum and don't see anything that sets me off as "wrong".
Here is the stored procedure if you can help. I'd really really love the help!
C R E A T E P R O C E D U R E sp_ApptActivityDate
(@strWHERE as varchar(500),
@strWHERECANCELED as varchar(500)
)
as
exec ('SELECT [date] AS Date, SUM(length) AS TotalSlots, COUNT(cast(substring(appointUniqueKey, 1, 1) AS decimal)) AS TotalAppts, SUM(length * 5) / 60 AS TotalSlotHours, 0 AS TotalActiveSlots, 0 AS TotalActiveAppts, 0 AS TotalActiveSlotHours, 0 AS totalCancelSlots, 0 AS TotalCancelAppts, 0 AS TotalCancelSlotHours
FROM dbo.vw_ALL_ApptActivity ' + @strWHERE + '
UNION
SELECT [date] as DATE, 0 AS TotalSlots, 0 AS TotalAppts, 0 AS TotalSlotHours, SUM(length) AS TotalActiveSlots, COUNT(cast(substring(appointuniquekey, 1, 1) AS decimal)) AS TotalActiveAppts, SUM(length * 5) / 60 AS TotalActiveSlotHours, 0 AS totalCancelSlots, 0 AS TotalCancelAppts, 0 AS TotalCancelSlotHours
FROM dbo.vw_Active_ApptActivity' + @strWHERE + '
UNION
SELECT [date] as DATE, 0 AS TotalSlots, 0 AS TotalAppts, 0 AS TotalSlotHours, 0 AS TotalActiveSlots, 0 AS TotalActiveAppts, 0 AS TotalActiveSlotHours, SUM(length) AS totalCancelSlots, COUNT(cast(substring(AppointUniqueKey, 1, 1) AS decimal)) AS TotalCancelAppts, SUM(length * 5) / 60 AS TotalCancelSlotHours
FROM dbo.vw_CANCELED_ApptActivity ' + @strWHERECANCELED + '
ORDER BY dbo.vw_ALL_ApptActivity.[Date] ' )
GO
View 12 Replies
ADVERTISEMENT
Sep 21, 2006
I used to do this with classic asp but I'm not sure how to do it with .net.Basically I would take a table of Categories, Then I would loop through those. Within each loop I would call another stored procedure to get each item in that Category. I'll try to explain, Lets say category 2 has a player Reggie Bush and a player Drew Brees, and category 5 has Michael Vick, but the other categories have no items.Just for an example.. Category Table: ID Category1 Saints2 Falcons3 Bucaneers4 Chargers5 FalconsPlayer Table:ID CategoryID Player News Player Last Updated1 1 Reggie Bush Poetry in motion 9/21/20062 1 Drew Brees What shoulder injury? 9/18/20063 5 Michael Vick Break a leg, seriously. 9/20/2006 Basically I would need to display on a page:SaintsReggie BushPoetry in MotionFalconsMichael VickBreak a leg, seriously.So that the Drew Brees update doesnt display, only the Reggie Bush one, which is the latest.I have my stored procedures put together to do this. I just don't know how to loop through and display it on a page. Right now I have two datareaders in the code behind but ideally something like this, I would think the code would go on the page itself, around the html.
View 1 Replies
View Related
Jun 13, 2007
Hi. I have a stored procedure that I'm interacting with through vb.net... the stored procedure logic, by itself, runs in query analyzer, but when I run it from the code side using the stored procedure, it dies. Now, that said, I'm not a pro at writing a stored procedure, so you might look at this and gasp in horror.
I'm executing a three-staged query, all of which are using temp tables, the final of which is what I'm using for my datagrid import. The temp table name I'm using doesn't work... it's not accepted in the SP and stops the execution of the query stating "invalid table name."
Not being a guru at this, I dont know why it runs in Query Analyzer but not w/i the SP. Can someone look at this and help?
(If its grossly written and there's a better way, I wouldn't be offended to be told that either. I"m looking for ways to write cleaner, faster code)
Here is the SP, any suggestions are very very appreciated.
Thanks so much!
CREATE PROCEDURE sp_PCPPanel_Summary
(@strWHEREMale as varchar(500), @strWHEREFemale as varchar(500))
AS
EXEC('
SELECT
Gender, RealAge, WeightedAge, FWeight, Mweight, AccountNum, PatLastName, PatFirstName, PatAddress, Patcity, PatState , PatZip, Tertiary, PatientStatusKey, InsClass, InsClassDesc, PCPDr, PCPClass, InsurancePlan, CarrierKey, CarrierName, PlanName, Age
INTO #tblTempPCP
FROM(
SELECT tblPCP.AccountNum, tblPCP.PatLastName, tblPCP.PatFirstName, tblPCP.PatAddress, tblPCP.PatCity, tblPCP.PatState, tblPCP.PatZip, tblPCP.PatPhone, tblPCP.DOB, tblPCP.Age, tblPCP.Gender, tblPCP.InsurancePlan, tblPCP.PCPClass, tblPCP.CarrierName, tblPCP.CarrierKey, tblPCP.PCPDr, tblPCP.PlanName, tblPCP.InsAddress, tblPCP.InsCity, tblPCP.InsState, tblPCP.InsZip, tblPCP.Tertiary, tblPCP.PatientStatusKey, dbo.tblPanelWeights.WeightedAge, dbo.tblPanelWeights.Category, 0 as MWeight, dbo.tblPanelWeights.Female as FWeight, dbo.tblPanelWeights.RealAge, tblPCP.InsClass, tblPCP.InsClassDesc, tblPCP.ApptDate FROM dbo.tblPanelWeights RIGHT OUTER JOIN dbo.[vwPCP+Continuity] tblPCP ON dbo.tblPanelWeights.RealAge = tblPCP.Age ' + @strWHEREFEMALE + '
UNION SELECT tblPCP.AccountNum, tblPCP.PatLastName, tblPCP.PatFirstName, tblPCP.PatAddress, tblPCP.PatCity, tblPCP.PatState, tblPCP.PatZip, tblPCP.PatPhone, tblPCP.DOB, tblPCP.Age, tblPCP.Gender, tblPCP.InsurancePlan, tblPCP.PCPClass, tblPCP.CarrierName, tblPCP.CarrierKey, tblPCP.PCPDr, tblPCP.PlanName, tblPCP.InsAddress, tblPCP.InsCity, tblPCP.InsState, tblPCP.InsZip, tblPCP.Tertiary, tblPCP.PatientStatusKey, dbo.tblPanelWeights.WeightedAge, dbo.tblPanelWeights.Category, dbo.tblPanelWeights.Male as MWeight, 0 as FWeight, dbo.tblPanelWeights.RealAge, tblPCP.InsClass, tblPCP.InsClassDesc, tblPCP.ApptDate FROM dbo.tblPanelWeights RIGHT OUTER JOIN dbo.[vwPCP+Continuity] tblPCP ON dbo.tblPanelWeights.RealAge = tblPCP.Age ' + @strWHEREMALE + '
) unionWEIGHTS
GROUP BY Gender, RealAge, WeightedAge, FWeight, Mweight, AccountNum, PatLastName, PatFirstName, PatAddress, patcity, patState , patZip, Tertiary, PatientStatusKey, InsClass, InsClassDesc, PCPDr, PCPClass, InsurancePlan, CarrierKey, CarrierName, PlanName, Age
ORDER BY realage
')
SELECT isnull(cast(RealAge as varchar), 'Unknown') as AgeRange, RealAge as AgeSort, gender, mweight, fweight, [Male] = sum(case when gender = 'M' then 1 else 0 end), [Female] = sum(case when gender = 'F' then 1 else 0 end), count(*) as Totalinto #tblTempPCP2
FROM #tblTempPCPgroup by realage, gender, mweight, fweight
Select AgeRange, AgeSort, [MALE], [FEMALE], [Male Weighted] = case when gender = 'M' then Sum(MWeight * [MALE]) end, [Female Weighted] = case when gender = 'F' then Sum(FWeight * [FEMALE]) endfrom #tblTempPCP2group by AgeRange, AgeSort, [MALE], [FEMALE], gender, totalorder by AgeSortGO
View 3 Replies
View Related
Jan 27, 2005
I have a table containing a 'queue' of rows waiting to be processed by my application.
Is it possible to call a single stored procedure that selects a row, returns the data and then deletes the row ?
If not, what is the best logic for doing it with two stored procedures ?
The table contains a unique ID, DateTime and nVarChar colums and I could easily add a 'flag' if required.
Any suggestions appreciated.
Steve.
View 3 Replies
View Related
Nov 30, 2000
I am new to trying to do IF/THEN logic in a stored procedure. I want to return one query if @property=1 and another if @property=0. Can anyone give me a hint in how to do this correctly. Here's the basic idea of what I am doing:
Create Procedure "get_orders_by_date_and_card"
@property int =0
as
if @property=0
begin
SELECT stores
FROM shops_master_Table
end
else
begin
SELECT restaurants
FROM restaurant_master_table
end
return
View 4 Replies
View Related
Sep 28, 2006
Hi
i have written a script that collects the applications and the components each application uses.
I wanted to create a stored procedure so that i could get a snapshot of the application and the components that an application has at a given time.
i have the following database structure
CREATE TABLE [Components] (
[CompID] [int] IDENTITY (1, 1) NOT NULL ,
[RT] [nchar] (7) COLLATE Latin1_General_CI_AS NOT NULL ,
[Name] [nchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Version] [smallint] NULL ,
PRIMARY KEY CLUSTERED
(
[CompID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [ApplicationID] (
[ApplicationID] [int] IDENTITY (1, 1) NOT NULL ,
[NodeID] [nchar] (15) COLLATE Latin1_General_CI_AS NULL ,
[WinVer] [nchar] (40) COLLATE Latin1_General_CI_AS NULL ,
[Hw] [nchar] (40) COLLATE Latin1_General_CI_AS NULL ,
[BT] [nchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[MT] [nchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[Cust] [nchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[BVer] [nchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[BRel] [nchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[WStyle] [nchar] (15) COLLATE Latin1_General_CI_AS NULL ,
[DateInst] [datetime] NULL ,
[SnapDate] [datetime] NULL ,
PRIMARY KEY CLUSTERED
(
[ApplicationID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [AppComponents] (
[ApplicationID] [int] NOT NULL ,
[CompID] [int] NOT NULL ,
[InsT] [datetime] NULL ,
[Installed] [bit] NULL ,
[SnapDate] [datetime] NOT NULL ,
CONSTRAINT [FK_AppsComponents_ApplicationID] FOREIGN KEY
(
[ApplicationID]
) REFERENCES [ApplicationID] (
[ApplicationID]
),
CONSTRAINT [FK_AppsComponents_Components] FOREIGN KEY
(
[CompID]
) REFERENCES [Components] (
[CompID]
)
) ON [PRIMARY]
GO
is there a way of checking if the component is already in the data base if it is then just get the CompID and attach that CompId in the AppsComponents link table and put that in the AppComponents table. the snapdate field is to be the current datetime when the data is collected. Is there a current date function in SQL server? Is it possible to do this in a stored procedure?
View 8 Replies
View Related
Dec 16, 2004
Hi guys I cant seem to get my stored procedure to execute properly through Access Xp. Do you think there is something wrong with my stored procedure??
CREATE PROCEDURE [insert_ConditionalLicense_UpdateFromTerms]
(@TM_# [int],
@FirstName [nvarchar](50),
@LastName [nvarchar](50),
@SS# [nvarchar](50),
@Birthdate [nvarchar](50),
@reasonforconditional [ntext],
@Notes [ntext])
AS INSERT INTO [GamingCommissiondb].[dbo].[ConditionalLicense_View]
( [TM #],
[FirstName],
[LastName],
[SS#],
[reasonforconditional],
[ConditionalStart Date])
SELECT
[TM#],
[LASTNAME],
[FIRSTNAME],
[SSN#],
[NOTES],
[DATEOFCONDITIONAL]
FROM EmployeeGamingLicense
WHERE STATUS = 'TERMINATION-COND'
IF @@Error <> '0'
RETURN
when I execute it through a command button this is the message I get "paramater" not quite sure why I am getting this message
View 5 Replies
View Related
Nov 5, 2007
I have a report in SQL Reporting Services that isn't displaying correctly. The report comprises a header and a simble table, typically consisting of 20 to 30 columns and 1-20 rows.
When I run the report IE, the table is truncated and only the first five columns are displayed. I can get the table to display correctly by doing one of the following:
Changing the report zoom - once the zoom has been changed, the report displays correctly even back at the default 100%
Moving to the next report page, then back to the first page
Refreshing the report by right-clicking and selecting 'Refresh' (not using the refresh button in the tool bar)
What seems strange is that if I copy the truncated table from IE to Excel, the full table is copied so it appears that what I'm seeing is some kind of display issue. I've trawled Google and the forums but to no avail - can anyone help?
Thanks!
View 3 Replies
View Related
Jul 20, 2005
Hello.Looking for a smarter way to code the following. I have a storedprocedure I will be passing several variables to. Some times, some ofthe fields used in a WHERE clause will not be passed, and I would liketo avoid having to code a bunch of if statements to set the executingcode. For example, below I would only like to execute the LIKEconditions only when the variable in question is not NULL. I did atest and if the variable is set to null, obviously the select does notreturn what I'm expecting.if @switch = "B"SELECT * from ikb whereikbtitle like @ins1 andikbtitle like @ins2 andikbtitle not like @ins3 andikbbody like @ins1 andikbbody like @ins2 andikbbody not like @ins3endThanks for any help or information with this.
View 2 Replies
View Related
May 21, 2008
I am working with stored procedures to provide conditional business logic for a customer based on triggers. Is it possible for a stored procedure to execute an external program ie opening or distributing a crystal report?
View 9 Replies
View Related
Jun 2, 2008
here is my select command:
SelectCommand="SELECT [JobID], [EmployeeName],
CAST(
STR(YEAR(DATEENTERED)) + '/' +
STR(MONTH(DATEENTERED)) + '/' +
STR(DAY(DATEENTERED)) AS DATETIME
) AS Date,
[From], [To], [Company], [Catagory], [Client], [Description], [TotalHours] FROM [JcpowersJobs]"
It will show correctly in the query builder test but when you load it up in the gridview it will show the date AND 12:00:00AM for every one of the dates
I tried replacing DATETIME with VARCHAR but it would insert spaces into the date where I didn't want them: EX: (12/04/1994 => 12/ 04/ 1994)
any suggestions?
View 1 Replies
View Related
Sep 10, 2007
I have managed to get reporting services running on Vista, but when I open report manager, I don't see the "Contents" or "Properties" tabs. I get the "SQL Server Reporting Services - Home" header and the links on the top right, but no tabs along the yellow or blue lines. I also have created and deployed a report but it does not show up in Report Manager.
What step have I missed?
Thanks,
Dan
View 5 Replies
View Related
Sep 6, 2007
I have managed to get reporting services running on Vista, but when I open report manager, I don't see the "Contents" or "Properties" tabs. I get the "SQL Server Reporting Services - Home" header and the links on the top right, but no tabs along the yellow or blue lines. I also have created and deployed a report but it does not show up in Report Manager.
What step have I missed?
Thanks,
Dan
View 1 Replies
View Related
Sep 6, 2007
I have an Excel spreadsheet with three columns of data, one of which is a "Score". The score will range between 1.0 and 0, going out to two decimal places. I am able to get this data into a global temporary table. I have a script in which a message box pops up, displaying the MIN value of the "Score" field in the temp table. The MIN value is .2. When I try to get the data from the temp table to a staging table, the Scores are all rounded to the nearest whole number. I think I've tried using every numeric data type for the staging table, and I always get the same results. In the temp table, Score is defined as such:
[Score] [decimal](18, 0) NULL
Does anybody know what I need to do to get the score to display acccurately?
Lindsay
View 5 Replies
View Related
Aug 9, 2015
I have a data model with 7 tables and I'm trying to write a stored procedure for each table that allows four actions. Each stored procedure should have 4 parameters to allow a user to insert, select, update and delete a record from the table.
I want to have a stored procedure that can accept those 4 parameters so I only need to have one stored procedure per table instead of having 28 stored procedures for those 4 actions for 7 tables. I haven't found a good example online yet of conditional logic used in a stored procedure.
Is there a way to add a conditional logic IF statement to a stored procedure so if the parameter was INSERT, go run this statement, if it was UPDATE, go run this statement, etc?
I have attached my data model for reference.
View 9 Replies
View Related
Feb 20, 2008
Iam working with a query where iam joining different fields from two tables and this is working well
SELECT Ind.Industry_Name,Com.Company_Name,Com.Company_Address FROM Industry AS Ind INNER JOIN Company AS Com INNER JOIN ON Ind.Ind_ID_PK = Com.Ind_ID_FK
Here iam getting the values depending on the Ids of both the tables.
But now i want to join three different tables..can i use this Query
SELECT Ind.Industry_Name,Com.Company_Name,Com.Company_Address,Pla.Plant_Name,Pla.Created_On FROM Industry AS Ind INNER JOIN Company AS Com INNER JOIN Plant AS Pla ON Ind.Ind_ID_PK = Com.Ind_ID_FK and Ind.Ind_ID_PK = Pla.Ind_ID_FKIam getting an error like :
Incorrect syntax near 'Ind_ID_FK'. i.e here Com.Ind_ID_FK .
I checked by removing and Ind.Ind_ID_PK = Pla.Ind_ID_FK but iam getting same error.
Please help me... Its Urgent..
View 2 Replies
View Related
May 18, 2007
This example is working:
Declare @startRowIndex INT; set @startRowIndex = (@PagerIndex * @ShowMembers); With BattleMembers as ( SELECT TOP 20 ROW_NUMBER() OVER (ORDER BY LastActivityDate DESC) AS Row, UserId, UserName FROM aspnet_Users) SELECT UserId, UserName FROM BattleMembers WHERE Row between @startRowIndex and @startRowIndex+@ShowMembersEND
and this one doesn't work:USE [xx]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[battle_Paging]@PagerIndex INT,@ShowMembers INT ASBEGINDeclare @startRowIndex INT; set @startRowIndex = (@PagerIndex * @ShowMembers); With BattleMembers as ( SELECT TOP 20 ROW_NUMBER() OVER (ORDER BY aspnet_Users.LastActivityDate DESC) AS Row, aspnet_Users.UserId, aspnet_Users.UserName, mb_personal.Avatar FROM aspnet_Users LEFT JOIN mb_personal ON (mb_personal.UserID=aspnet_Users.UserId) SELECT UserId, UserName, Avatar FROM BattleMembers WHERE Row between @startRowIndex and @startRowIndex+@ShowMembersEND
Error:Msg 156, Level 15, State 1, Procedure battle_Paging, Line 18Incorrect syntax near the keyword 'SELECT'.
I try to join in the table mb_personal here. Some help would be very appreciated! Thanks.
View 2 Replies
View Related
Feb 9, 2005
I have a database of news articles and i have a stored procedure that basically pulls one from the database based on an ID number. The author (Press Contact) and publication are stored as just ID numbers and pulled in via JOINs.
SELECT Articles.date_published, Articles.headline, Publication.press_contact,
Publication.pub_name, Articles.body
FROM Articles
LEFT OUTER JOIN
PressContact ON PressContact.press_id = Articles.press_id
LEFT OUTER JOIN
Publication ON Publication.publication_id = Articles.publication_id
WHERE (Articles.id = @ID)
Everything works great in this setup. However, we've recently added a press_id2 field to the articles table to be able to store a 2nd press contact. So now I need my stored procedure to pull out both press contact names and I'm not sure the best way to do that.
I tried to JOIN the PressContact table a 2nd time on PressContact.press_id = Articles.press_id2 but that didn't seem to work.
Can anyone give me any suggestions?
Thanks in advance.
View 2 Replies
View Related
Oct 30, 2015
How 'NULL' value is displaying after encrypting a stored procedure? links to know the procedure behind this how the encrypted procedure is storing and updating with NULL value under the same column after the encryption.
View 7 Replies
View Related
Feb 26, 2015
I am having problems displaying time values in my SSRS report. below is info. Tried expressions still does not work. I want the values to show what in the SQL Server table 00:00:00.82. I tried stored proc still does not work.
SQL Server table time value shown in milliseconds:
00:00:00.82
Reporting Services report value shown:
00:00:00
View 3 Replies
View Related
Oct 23, 2007
I am having problems producing the following report.
The function of this report is to list people who have not paid their account in 30,60, and 90 days.
In the BillingRun table, there is a dateprocessed field.
this is the date to check against the paydate field in the payment table.
i.e. if their account is in debit, check to see when a payment was last made.
Using the following columns:
Sitename , Child Name, Debt Age, Amount Outstanding,
Debt Age would be 30 days, 60 days or 90 days+
I am using a datediff(day,billingrun.dateprocessed,payment.paydate) to get the age of the debt.
Rpt_ageDebtorsForSite (@cmb1 as varchar(100)) with encryption as
Begin
Would suggest a union. i.e. create a select statement for the 30 days to 59 days UNION select statement for 60 to 89 days UNION select statement for 90 + days
i.e. SELECT S.SITENAME,C.FORENAME+€™ €˜+C.SURNAME,€™30 DAYS€™,TOTAL
WHERE DEBT BETWEEN 30 AND 59 DAYS OLD
UNION
SELECT S.SITENAME,C.FORENAME+€™ €˜+C.SURNAME,€™30 DAYS€™,TOTAL
WHERE DEBT BETWEEN 60 AND 89 DAYS OLD
UNION
SELECT S.SITENAME,C.FORENAME+€™ €˜+C.SURNAME,€™30 DAYS€™,TOTAL
WHERE DEBT 90 DAYS OR GREATER OLD
End
This is what i have so far,
CREATE procedure rpt_ageDebtorsForSite
(@cmb1 as varchar(100)) WITH ENCRYPTION
AS
BEGIN
SELECT DISTINCT
Site.siteName,
Child.surname + ' ' + Child.forename AS Child_Name,
datediff(day,billingrun.dateprocessed,payment.paydate) AS DebtAge
--
FROM Site
INNER JOIN
BillingRun
ON Site.siteID = BillingRun.siteID
INNER JOIN
payment
ON Site.siteID = payment.siteID
INNER JOIN
Child
ON Site.siteID = Child.siteID
select
amount
from
payment AS Amount_Outstanding
--WHERE
--site.sitename=@cmb1
END
The output should look like
Sitename: childname: debtage: amount_outstanding
HappySite mary 10 100
View 5 Replies
View Related
Feb 29, 2008
I have four tables which I want to return results for an advanced search function, the tables contain different data, but can be quite easily joined,
Table A Contains a Main Image, this image is displayed in the results
Table B Contains an Icon, this image is displayed in the results
Table C doesn't have an image in it but has a child table with a number of images associated to the table, in the UNION ALL statement I would Like to do a Join to get say the top Image from this child and print it for the row associated with table C.
Select title, description, image from tableA
UNION ALL
Select title, description, icon as image from tableB
UNION ALL
title, description, ( inner Join SELECT top(1)
from imageTableC where imagetableC.FK = tableC.PK)
as image from tableC
Could someone show me the syntax to do this, I have all the information printing to the screen, bar this table C image.
View 14 Replies
View Related
Feb 22, 2008
Hi
i have a search page having four text boxes like name,accountnumber,ssn..etc we have to search the database by these values. but even if we give value in one text box keeping the others null the stored procedure have to serach and get the values. and we display it using gridview control.
here is the stored procedure i wrote.but its not working.its not giving any erros...but its not showing any values.
ALTER Procedure [dbo].[usp_CheckUser]
@name nvarchar(50),
@ssn nvarchar(50),
@accountnumber nvarchar(50)
AS
BEGIN
if(@name!=null AND @ssn!=null AND @accountnumber!=null)
select * from Userinfo where name=@name AND ssn=@ssn AND accountnumber=@accountnumber
else if(@name=null AND @ssn!=null AND @accountnumber!=null)
select * from Userinfo where ssn=@ssn AND accountnumber=@accountnumber
else if(@name=null AND @ssn=null AND @accountnumber!=null)
select * from Userinfo where accountnumber=@accountnumber
else if(@name!=null AND @ssn=null AND @accountnumber!=null)
select * from Userinfo where accountnumber=@accountnumber AND name=@name
else if(@name!=null AND @ssn=null AND @accountnumber=null)
select * from Userinfo where name=@name
else if(@name!=null AND @ssn!=null AND @accountnumber=null)
select * from Userinfo where name=@name AND ssn=@ssn
else if(@name=null AND @ssn!=null AND @accountnumber=null)
select * from Userinfo where ssn=@ssn
end
table name is userinfo
please help me with this. its very urgent.
thanx for your help in advance.
ramya
View 7 Replies
View Related
Jul 23, 2005
I have the following stored procedure in SQL 2000 and would like todiplay the database name where data is drawn from. I'm using 4databases db1, db2, db3, db4 which all have the same table (Table1)with identical column names (Surname, GivenNames).CREATE PROCEDURE [dbo].[x_searchwildcard] @varSurname VARChar(25)ASSelect a.Surname, a.GivenNamesFrom [db1]..Table1 As aWhere a.Surname LIKE @varSurname + '%'UNIONSelect a.Surname, a.GivenNamesFrom [db2]..Table1 As aWhere a.Surname LIKE @varSurname + '%'UNIONSelect a.Surname, a.GivenNamesFrom [db3]..Table1 As aWhere a.Surname LIKE @varSurname + '%'UNIONSelect a.Surname, a.GivenNamesFrom [db4]..Table1 As aWhere a.Surname LIKE @varSurname + '%'Order By a.Surname,a.GivenNamesGOI tried the followingSelect a.Surname, a.GivenNames, db_name()However it only gave me the name of the database where the storedprocedure is kept (in my case 'Common')I was hoping it would display results something like the followingSurname GivenNames Database------- ---------- --------Fred Smith db1Freddy Smith db2Fred Smith db3Fred Smithe db3Fred Smith db4Fred Smithye db4Instead I receiveSurname GivenNames Database------- ---------- --------Fred Smith commonFreddy Smith commonFred Smith commonFred Smithe commonFred Smith commonFred Smithye commonAny ideas?ThanksRick
View 1 Replies
View Related
Sep 14, 2015
Any better way to query SQL 2012 to display the code of a stored proc to a single line. I'm trying to write a script to insert the contents of the procs between my devestprod environments. So people can query a single table for any proc that is different between environments. At the moment I am using the syscomments view and the text column but the problem here is if you get a lengthy proc it cuts it up into multiple rows.
I can get around it by converting the text to a varchar(max) and outer joining the query, but as you can see by my code below I have to try and guess what the maximum number of rows I'm going to get back for my largest proc. If someone adds a new one that returns 8 rows I'm going to miss it with this query.
Select col1.[type],col1.[name],convert(varchar(max),col1.text) + isnull(convert(varchar(max),col2.Text),'')
+ isnull(convert(varchar(max),col3.Text),'')
+ isnull(convert(varchar(max),col4.Text),'')
+ isnull(convert(varchar(max),col5.Text),'')
+ isnull(convert(varchar(max),col6.Text),'')
+ isnull(convert(varchar(max),col7.Text),'')
[Code] .....
View 3 Replies
View Related
Sep 15, 2006
How to find maximum value from two tables have the same field name?
For example:
Table -1 has field calcuated_price and its max value is 3500 and then Table -2 has
same field name calcuated_price has max value is 3000.
Nishith
View 9 Replies
View Related
Aug 31, 2000
I've got a union query (below)and it returns rows that have duplivate itemno's, descrip's, imsrp3's, and imsrp4's, while the remaining columns are not duplicate for the same row. An Excel report uses this query to populate itself and for a more visually appealing look, I'd like to skip the duplicated columns in the display. I'm not sure how to use the Distinct or Group by in this case, since technically I'm dealing with two separate queries, neither one separately returning any duplicate rows.
thanks for any suggestions...
~
select itemno,descrip,imsrp3,imsrp4,qoh,border,wadcto,wad oco,
watrdj,wapddj,wauorg,wauser
from nowo
where nowo.wasrst <='40'
union
select itemno,descrip,imsrp3,imsrp4,qoh,border,wadcto,wad oco,
watrdj,wapddj,wauorg,wauser
from nopo
where nopo.wasrst <='499'
View 1 Replies
View Related
Jul 15, 2014
I am writing a stored procedure that takes in a customer number, a current (most recent) sales order quote, a prior (to most current) sales order quote, a current item 1, and a prior item 1, all of these parameters are required.Then I have current item 2, prior item 2, current item 3, prior item 3, which are optional.
I added an IF to check for the value of current item 2, prior item 2, current item 3, prior item 3, if there are values, then variable tables are created and filled with data, then are retrieved. As it is, my stored procedure returns 3 sets of data when current item 1, prior item 1, current item 2, prior item 2, current item 3, prior item 3 are passed to it, and only one if 2, and 3 are omitted.I would like to learn how can I return this as a one data set, either using a full outer join, or a union all?I am including a copy of my stored procedure as it is.
View 6 Replies
View Related
Jun 12, 2014
SQL Server 2008 r2...
I have a query which does 3 selects and Union ALLs each to get a final result set. The performance is unacceptable - takes around a minute to run. If I remove the Union All so that the result sets are returned individually it returns all 3 from the query in around 6 seconds (acceptable performance).
Any way to join the result sets together without using Union All.
Each result set has exactly the same structure returned...
Query below [for reference]...
WITH cte AS (
SELECT A.[PoleID], ISNULL(B.[IsSpanClear], 0) AS [IsSpanClear], B.[SurveyDate], ROW_NUMBER() OVER (PARTITION BY A.[PoleID] ORDER BY B.[SurveyDate] DESC) rownum
FROM[UT_Pole] A
LEFT OUTER JOIN [UT_Surveyed_Pole] B ON A.[PoleID] = B.[PoleID]
[Code] .....
View 4 Replies
View Related
Oct 22, 2007
I have the following query in an ExecuteSQL Task:
Insert Into Table2
Select * From Table1 Where Column1Val = '4'
As you can see, I don't need any parameters so I havent configured any. Also, there should not be any result set so I shouldnt need to configure a resultset parameter.
Why is the above query failing with
Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly
View 4 Replies
View Related
Jan 15, 2008
Hello all ..
I have a form that views the date automatically in a textbox... the date of this text box must be stored in the database after the user press the submit button ... However, I am getting a wrong date which is 01/01/1900 .. although I see it in the form as 15/01/2008 ..
this is the code ... please give me a solution ASAP ..
Protected Sub SubmitButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles SubmitButton.Click
'Declaring the variablesDim objConnection As SqlConnection
Dim objDataCommand As SqlCommand
Dim courseId, traineeName, traineeId, department, comment1, comment2 As String
Dim formDate As Date
Dim RB1, RB2, RB3, RB4, RB5, RB6, RB7, RB8, RB9, RB10, RB11, RB12, RB13, RB14 As String
Dim ConnectionString, evaluationSQL, TraSQL, iTotal As String
'Save form values in variables
formDate = Convert.ToDateTime(DateTxt.Text)
courseId = CourseIDTxt.Text
traineeId = EmailTxt.Text
traineeName = TraineeNTxt.Text
department = DeptDropDownList.SelectedValue
RB1 = RBL1.SelectedValue
RB2 = RBL2.SelectedValue
RB3 = RBL3.SelectedValue
RB4 = RBL4.SelectedValue
RB5 = RBL5.SelectedValue
RB6 = RBL6.SelectedValue
RB7 = RBL7.SelectedValue
RB8 = RBL8.SelectedValue
RB9 = RBL9.SelectedValue
RB10 = RBL10.SelectedValue
RB11 = RBL11.SelectedValue
RB12 = RBL12.SelectedValue
RB13 = RBL13.SelectedValue
RB14 = RBL14.SelectedValue
comment1 = CommentTxt1.Text
comment2 = CommentTxt2.TextConnectionString = WebConfigurationManager.ConnectionStrings("GRPConnectionString").ConnectionString
'Create and open the connection objConnection = New SqlConnection(ConnectionString)
objConnection.Open()evaluationSQL = "Insert into Evaluation (Eva_Date, CourseD_Id, Tra_Id , Q1, Q2, Q3, Q4, Q5, Q6, Q7, Q8, Q9, Q10, Q11, Q12, Q13, Q14, Comment1, Comment2 ) " & _
"values(" & formDate & ",'" & courseId & "','" & traineeId & "','" & RB1 & "','" & RB2 & "','" & RB3 & "','" & RB4 & "','" & RB5 & "','" & RB6 & "','" & RB7 & "','" & RB8 & "','" & RB9 & "','" & RB10 & "','" & RB11 & "','" & RB12 & "','" & RB13 & "','" & RB14 & "','" & comment1 & "','" & comment2 & "')"objDataCommand = New SqlCommand(evaluationSQL, objConnection)
objDataCommand.ExecuteNonQuery()
View 3 Replies
View Related
Apr 5, 2006
Hey guys,I have to import a csv file to my database and then add
some values to other fields based on these values. At present I use a
store procedure that does a bulk insert and then I use an update and
fetch inside the sql to perform these updates. I am not sure if this is
the best way. Is this bad, since some amount of busniess logic is in
the store procedure?If it is, how can I do this better?Thanks for the answer.
View 1 Replies
View Related
Jul 20, 2005
Hello,I stuck in a delimma.Where to put the business logic that involves only one updatebut N number of selects from N tables........with N where conditions
View 5 Replies
View Related
