stored procedure and join


,

I am taking a class on sql. We are using sql server 2008 r2. We have a project to create a database and create 3 stored procedures. I have created the database as well as the entries for it. The stored procedures are giving me a little trouble though. I'd love if you could offer some help on how to fix these.
Posted On: Sunday 30th of December 2012 11:17:45 PM Total Views:  284
View Complete with Replies

RELATED TOPICS OF Microsoft SQL Server PROGRAMMING LANGUAGE




Help with inner joins

I need to write the below query using inner joins as the SQL server doesn't support multiple columns in clause.. Select a.Plan_num,b.top_fund_id,(a.fee_basis_pnts * b.actl_pct) ,(a.chrg_amt * b.actl_pct)from rpt408b.dc_fee_disclosure_rptng a, stg5500.paris_fof_info b where a.fund_cusip_id = b.paris_fund_id and (a.plan_num,b.top_fund_id) in (select a.plan_num,b.top_fund_id from rpt408b.dc_fee_disclosure_rptng a, stg5500.paris_fof_info b where a.exp_dim_key = 10005 and a.fund_cusip_id = b.top_fund_id and a.last_acty_oper_id like '%FoF%') Group by plan_num , top_fund_id A quick response will be appreciated!!!
VIEWS ON THIS POST

152

Posted on:

Thursday 25th October 2012
View Replies!

2 tables/2joins

REVISED:I figured out how to get what I want by using an aliased 2nd table, can anyone tell me if its the best way Manager Staff Client Joe Gail XYZ Client Stan Kyle ABC Client Code: SELECT Employee.Emplname AS Manager, Employee_1.Emplname AS Staff, Clients.Cltname FROM Clients INNER JOIN Employee ON Clients.Engmgr = Employee.ID INNER JOIN Employee AS Employee_1 ON Clients.Engstaff = Employee_1.ID WHERE (Employee_1.Empoff = '66') AND (Employee_1.Empstatus = 'A') AND (Employee_1.Empdept = '45012') AND (Clients.Engstatus = '0') OR (Clients.Engstatus = '0') AND (Employee.Empoff = '66') AND (Employee.Empstatus = 'A') AND (Employee.Empdept = '45012') ORDER BY Employee.Emplname, Employee_1.Emplname, Clients.Cltname
VIEWS ON THIS POST

107

Posted on:

Thursday 25th October 2012
View Replies!

Problem with a stored procedure

I am using a stored procedure and executing it from a string, to make it easy to modify. I am passing is a select as varchar(max) as well as a date as datetime, when I rune my execute(sp); I get an error "Conversion failed when converting datetime from character string.". if anyone has any suggestions they will be much appreciated,
VIEWS ON THIS POST

119

Posted on:

Thursday 25th October 2012
View Replies!

Finding if a stored procedure exists

I was wondering if there is an easy way to see if a stored procedure exists before trying to use it. What I am trying to do is give the ability to over write a stored procedure if they need to change it, with out killing the default.
VIEWS ON THIS POST

70

Posted on:

Thursday 25th October 2012
View Replies!

Problems joining 3+ tables

I need some help and I can't figure this out. I have a query that works : Code: SELECT table1.DealType, table1.bookings, table2.tours, table1.ResDate1 FROM (SELECT ResDate1, DealType, COUNT(ResID) AS bookings FROM tblReservations res LEFT OUTER JOIN tblDeals del ON res.DealID = del.DealID WHERE VendorType = 'TOUR' AND (del.RoomID = 'OPC') AND (ResDate1 BETWEEN '2008-2-18' AND '2008-2-24') GROUP BY DealType, ResDate1) table1 LEFT OUTER JOIN (SELECT ResDate1, DealType, COUNT(ResID) AS tours FROM tblReservations res LEFT OUTER JOIN tblDeals del ON res.DealID = del.DealID WHERE VendorType = 'TOUR' AND (del.RoomID = 'OPC') AND (ResDate1 BETWEEN '2008-2-18' AND '2008-2-24') AND (ResStatus = 'GOOD TOUR' OR ResStatus = 'OVERGIFTED/GOOD TOUR') GROUP BY DealType, ResDate1) table2 ON table1.ResDate1 = table2.ResDate1 AND table1.DealType = table2.DealType ORDER BY table1.DealType but now I'm trying to add a "table3" which is simply : Code: SELECT ResDate1, DealType, COUNT(ResID) AS nqs FROM tblReservations res LEFT OUTER JOIN tblDeals del ON res.DealID = del.DealID WHERE VendorType = 'TOUR' AND (del.RoomID = 'OPC') AND (ResDate1 BETWEEN '2008-2-18' AND '2008-2-24') AND (ResStatus = 'NON QUALIFIED') GROUP BY DealType, ResDate1 but when I try to combine it its just not working: Code: SELECT table1.DealType, table1.bookings, table2.tours, table1.ResDate1, table3.nqs FROM (SELECT ResDate1, DealType, COUNT(ResID) AS bookings FROM tblReservations res LEFT OUTER JOIN tblDeals del ON res.DealID = del.DealID WHERE VendorType = 'TOUR' AND (del.RoomID = 'OPC') AND (ResDate1 BETWEEN '2008-2-18' AND '2008-2-24') GROUP BY DealType, ResDate1) table1 LEFT OUTER JOIN (SELECT ResDate1, DealType, COUNT(ResID) AS nqs FROM tblReservations res LEFT OUTER JOIN tblDeals del ON res.DealID = del.DealID WHERE VendorType = 'TOUR' AND (del.RoomID = 'OPC') AND (ResDate1 BETWEEN '2008-2-18' AND '2008-2-24') AND ResStatus = 'NON QUALIFIED' GROUP BY DealType, ResDate1) table3 LEFT OUTER JOIN ON table3.ResDate1 = table1.ResDate1 (SELECT ResDate1, DealType, COUNT(ResID) AS tours FROM tblReservations res LEFT OUTER JOIN tblDeals del ON res.DealID = del.DealID WHERE VendorType = 'TOUR' AND (del.RoomID = 'OPC') AND (ResDate1 BETWEEN '2008-2-18' AND '2008-2-24') AND (ResStatus = 'GOOD TOUR' OR ResStatus = 'OVERGIFTED/GOOD TOUR') GROUP BY DealType, ResDate1) table2 ON table1.ResDate1 = table2.ResDate1 AND table1.DealType = table2.DealType ORDER BY table1.DealType any help, pointers, kick in the right direction is appreciated
VIEWS ON THIS POST

100

Posted on:

Thursday 25th October 2012
View Replies!

How to read parameter array in stored procedure

for example i have 2 array parametereg: @event =injury,opd,icu. @month=jan,feb,mar,apr,dec. based on this parameter i wanted to retrive respective patient history. at the first how to split array in stored procdure, and write paricular event. normally in asp.net c# the following code is excuted as follows. string[] event = @event .Split('\'', ','); string[] month = @month.Split('\'', '\"', ','); public ArrayList mai = new ArrayList(); public string[] janhistory ; for (int i = 1; i < event.Length; i++) { if (event[i].Equals("injury")) { foreach (string Items in month) { if (Items.Contains("jan")) { mai.Add(Items.ToString()); } } janhistory = mai.ToArray(Type.GetType("System.String")) as string[]; gh.getmailcomm(janhistory, "Acc398"); getmailcomm is function used as below public void getmailcomm(string[] month,string accno) { k = comm.Length; getmailcomarr = new string[k]; vasi = new int[k]; vasisid = new int[k]; for (int m = 0; m < k; m++) { string l = comm[m]; com = l.Substring(0, 3).ToString(); getma = l.Remove(0, 3).ToString(); com = com + "Comments"; if (getma == "Mail") { SqlConnection con = new SqlConnection(cn); con.Open(); string sql = "Select VAS_Id," + com + ",VAS_Sub_Id from Acc_MVP_Table1 where " + l + "='1' and AccSlno='" + acc + "'"; SqlCommand getmacom = new SqlCommand(sql, con); rd = getmacom.ExecuteReader(); getmailcomarr[m] = "NoComments";// default value if (rd.Read()) { getmailcomarr[m] = rd.GetString(1); vasi[m] = rd.GetInt32(0); vasisid[m] = rd.GetInt32(2); } } } } } and also i first splited both array, but on above query i not able write stored procedure
VIEWS ON THIS POST

96

Posted on:

Thursday 25th October 2012
View Replies!

Help with dynamic where clause in stored procedure

I have a stored procedure being called based on user search criteria. Some, the colour and vendor fields are optional in the search so i do not want that portion of the procedure to run. at this point i keep getting errors in the section bolded below it never seems to recognize anything after the if @myColours 'Select' CREATE Procedure PG_getAdvWheelSearchResults3 ( @SearchDiameter NVarchar( 20 ), @SearchWidth NVarchar( 20 ), @minOffset int , @maxOffset int , @boltpattern1 NVarchar( 20 ), @VendorName NVarchar( 40 ), @myColours NVarchar( 40 ) ) As BEGIN TRANSACTION SELECT *, dbo.VENDORS.*, dbo.WHEEL_IMAGES.Wheel_Thumbnail AS Wheel_Thumbnail, dbo.WHEEL_IMAGES.Wheel_Image AS Wheel_Image, dbo.WHEELS.*, dbo.VENDOR_IMAGES.Vendor_Thumbnail AS Expr1, dbo.VENDOR_IMAGES.Vendor_AltTags AS Expr2 FROM WHEEL_CHARACTERISTICS INNER JOIN dbo.VENDORS ON WHEEL_CHARACTERISTICS.Vendor_ID = dbo.VENDORS.Vendor_ID INNER JOIN dbo.WHEEL_IMAGES ON WHEEL_CHARACTERISTICS.Wheel_ID = dbo.WHEEL_IMAGES.Wheel_ID INNER JOIN FILTER_CLIENT_WHEELS5 ON WHEEL_CHARACTERISTICS.Wheel_ID = FILTER_CLIENT_WHEELS5.Wheel_ID INNER JOIN dbo.WHEELS ON WHEEL_CHARACTERISTICS.Wheel_ID = dbo.WHEELS.Wheel_ID INNER JOIN CLIENT_WHEEL_PRICES5 ON FILTER_CLIENT_WHEELS5.Client_ID = CLIENT_WHEEL_PRICES5.ClientId AND WHEEL_CHARACTERISTICS.Wheel_Char_ID = CLIENT_WHEEL_PRICES5.Wheel_Char_ID INNER JOIN dbo.VENDOR_IMAGES ON dbo.VENDORS.Vendor_ID = dbo.VENDOR_IMAGES.Vendor_ID WHERE (dbo.VENDORS.Vendor_Active = 'y') AND (FILTER_CLIENT_WHEELS5.FCW_Active = 'y') AND (FILTER_CLIENT_WHEELS5.Client_ID = '1039') AND (WHEEL_CHARACTERISTICS.Wheel_Diameter =@SearchDiameter) AND (WHEEL_CHARACTERISTICS.Wheel_Width =@Searchwidth) AND (WHEEL_CHARACTERISTICS.Wheel_Bolt_Pattern_1 = @boltpattern1) if @myColours 'Select' and WHEEL_CHARACTERISTICS.Wheel_Search_Colour = @myColours end if AND (cast(WHEEL_CHARACTERISTICS.wheel_Offset as int(4)) BETWEEN @minOffset AND @maxOffset) ORDER BY CLIENT_WHEEL_PRICES5.Price asc COMMIT TRANSACTION GO Anyone know how i should word the if...statements I have not found anything that works yet.
VIEWS ON THIS POST

178

Posted on:

Thursday 25th October 2012
View Replies!

Import From Excel in stored procedure

I'm trying to automate a data entry process. We get annual or semi-anual fee schedules from our clients. Sometimes it's an excel file, sometimes it's csv or other text, sometimes it's from the web. We clean up these files a bit and then import them to a new table in a sql server database. Then someone writes a custom insert to take specific columns from that temp table, do some transformations, and put them in the correct place in the normal database. Then the imported table is deleted or archived. We'll still have to do the clean up phase, but I should be able to automate most everything after the import. What I want to know is, can I pass a filename to a stored procedure, along with perhaps a few other parameters, and have the procedure import data from that file If so, how would I go about it
VIEWS ON THIS POST

95

Posted on:

Thursday 25th October 2012
View Replies!

Does MSSQL return unused parameters from stored procedures?

If for example I had the following: Code: CREATE PROCEDURE dbo.ExampleSP ( @ID int, @SmallData int OUTPUT, @HugeData text OUTPUT ) AS -- Select a little data into @SmallData -- Select lots of data into @HugeData RETURN and I called by the following: (C#) Code: SqlCommand ExampleQuery = new SqlCommand("ExampleSP", Database); ExampleQuery.CommandType = System.Data.CommandType.StoredProcedure; ExampleQuery.Parameters.Add("@ID", System.Data.SqlDbType.Int); ExampleQuery.Parameters["@ID"].Direction = System.Data.ParameterDirection.Input; ExampleQuery.Parameters.Add("@SmallData", System.Data.SqlDbType.Int); ExampleQuery.Parameters["@SmallData"].Direction = System.Data.ParameterDirection.Output; ExampleQuery.Parameters["@ID"].Value = 4; SqlDataReader ExampleReader = ExampleQuery.ExecuteReader(); Because I haven't added HugeData as a parameter, although I know it will still be selected in the SP, will SQL waste bandwidth returning the data to my application I suppose what I'm asking is does the SQL engine look at what parameters are sent, or is it the SqlClient objects that handle that, in which case the data would be returned and just never used.
VIEWS ON THIS POST

167

Posted on:

Thursday 25th October 2012
View Replies!

Viewing created procedures and functions

I'am using SqlServer 2005.. I'am able to view created tables via: SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES; How do I do the same with created procedures and functions i.e how do I view all procedures and functions using SELECT query
VIEWS ON THIS POST

118

Posted on:

Friday 26th October 2012
View Replies!

Passing dates to a procedure

im throwing up some errors Syntax error converting datetime from character string. im sending 2 asp:Text fields to the procedure in the format dd/mm/yyyy i.e. 06/03/2006 the table im inserting it into is set to datetime within the procedure i have tried both @Date1 DateTime, @Date2 DateTime @Date1 Nvarchar(50), @Date2 Nvarchar(50) and it still throws up Syntax error converting datetime from character string. now im not sure if i should pass it to the Procedure as Nvarchar then Convert it within the procedure .. or change the table type to nvarchar and just insert into a non date format table type.
VIEWS ON THIS POST

91

Posted on:

Friday 26th October 2012
View Replies!

Difference among store procedure, Functions and views

What is the basic difference between store procedure, Functions and views
VIEWS ON THIS POST

199

Posted on:

Friday 26th October 2012
View Replies!

Using Like in a stored proc with variables

this is the search string in the stored proc for sql server Code: SELECT ContactID FROM tblContact WHERE SocialSecurityNumber like @ID what i want to be able to do is ad a % after id however when i do it throws an error and if i put it like this '@ID' then it wont find anything. how do i fix this
VIEWS ON THIS POST

175

Posted on:

Friday 26th October 2012
View Replies!

table Self-joins with updates

table = PEOPLE Name Money Type ----- ----- ---- Steve 400 R Steve 100 R Paul 500 R Paul 100 R Matt 500 R Matt 200 R Matt 0 T Steve 0 T Paul 0 T I'm trying to add-up all of the Money values for each Name and store them into their names, but under Type 'T'. after the update command it should look like this Name Money Type ----- ----- ---- Steve 400 R Steve 100 R Paul 500 R Paul 100 R Matt 500 R Matt 200 R Matt 700 T Steve 500 T Paul 600 T
VIEWS ON THIS POST

356

Posted on:

Friday 26th October 2012
View Replies!

Any benefit from filtering in join vs. the where clause?

Just curious. The exec plan is the same for both qry's, and they both show the same estimated row counts @ the point of question in the exec plan. The exec times are roughly the same, any variances I'm attributing to db load from other things going on, since any benefits of one over the other are not consistent from execution to execution. So is there any benefit to filtering in the join conditions vs. the where clause My thinking was that by filtering earlier in the qry (when joining) as opposed to "waiting" to do it in the where clause, the rest of the qry after the join would inherently be dealing w/a smaller result set for the rest of it's execution, thus improving performance. After the exec plan checking I did, I guess I was wrong. Seems that Sql Server is intelligent about such filtering when analyzing the entire qry, and building its execution accordingly. The execution plan for both qry's showed the same where clause argument for the tables being joined. Filtering in where clause.... Code: select ... From tProject p with (noLock) join tProjectCall pc with (noLock) on P.ID = pc.project_id join tStore S with (noLock) on pc.store_id = s.id join tZip Z with (noLock) on Z.zip5 = s.zip5 join tManager M on M.ID = case ... end left join ( select projectCall_RecNum as RecNum, sum(answer) as HoursUsed from tCall C where Answer > 0 and question_id in (1, 2) group by projectCall_Recnum ) as C on pc.recnum = c.recnum where pc.removed = 0 and p.cancelled = 0 and p.deleted = 0 and s.closed = 0 and s.deleted = 0 and year(getDate()) between year(P.startDate) and year(P.expDate) Filtering in joins... Code: select ... From tProject p with (noLock) join tProjectCall pc with (noLock) on P.ID = pc.project_id and pc.removed = 0 and p.cancelled = 0 and p.deleted = 0 and year(getDate()) between year(P.startDate) and year(P.expDate) join tStore S with (noLock) on pc.store_id = s.id join tZip Z with (noLock) on Z.zip5 = s.zip5 and s.closed = 0 and s.deleted = 0 join tManager M on M.ID = case ... end left join ( select projectCall_RecNum as RecNum, sum(answer) as HoursUsed from tCall C where Answer > 0 and question_id in (1, 2) group by projectCall_Recnum ) as C on pc.recnum = c.recnum
VIEWS ON THIS POST

133

Posted on:

Friday 26th October 2012
View Replies!

removing duplicates from inner joins

Hi There, I was wondering if someone could help me with the results on this query, at the moment I am getting values repeated and I was wondering if it was possible to have some of the columns grouped, I have tried to have grouping at the end of the query but this still did not group the rows.
VIEWS ON THIS POST

132

Posted on:

Friday 26th October 2012
View Replies!

need to change stored proc. a bit

ok here's the thing : CREATE PROCEDURE Split ( @OrderList varchar(500) ) AS BEGIN DECLARE @OrderID varchar(10),@Pos int SET @OrderList = LTRIM(RTRIM(@OrderList))+ ',' SET @Pos = CHARINDEX(',', @OrderList, 1) IF REPLACE(@OrderList, ',', '') '' BEGIN WHILE @Pos > 0 BEGIN SET @OrderID = LTRIM(RTRIM(LEFT(@OrderList, @Pos - 1))) IF @OrderID '' BEGIN INSERT INTO Testowa(ide) VALUES ( CAST (@OrderID AS int ) ) --Use Appropriate conversion END SET @OrderList = RIGHT(@OrderList, LEN(@OrderList) - @Pos) SET @Pos = CHARINDEX(',', @OrderList, 1) END END RETURN END GO what it basically does is - it takes a string like '34,2,3,5' and takes ',' as a separator to retrieve values that it should insert into a table 'Testowa' what i'd like to have is : take string like '1,32,4,5' and recognize that it should insert values like this : table Testowa ------------- id | num ------------ 1 32 4 5 can someone rearrange this procedure for me so it'd do this
VIEWS ON THIS POST

127

Posted on:

Friday 26th October 2012
View Replies!

changing one field updates 3 other fields, use a join?

i currently have a table like this.. Code: user_id username app_id app2_id app3_id app4_id 1 john 3 4 5 6 2 mike 4 5 6 6 3 manager 4 5 6 6 4 vicepres 5 6 6 6 5 ceo 6 6 6 6 6 board 6 6 6 6 the basic pattern is... a user has approvers, and those approvers have approvers as well... i have 4 columns of approvers.. and if my first approver is the manager, then my second approver will be the managers approver and my third approver will be the managers approver's approver and so on.. on my actual page, i have select fields for the app, app2, app3, app4 and i need it so that when i change the very first app, it'll automatically update app2, app3, and app4 any ideas how i would do this im pretty new to sql but im thinking i would use some type of join
VIEWS ON THIS POST

131

Posted on:

Friday 26th October 2012
View Replies!

joining a view to match main data in search query

I am currently building a telephone directory, i'm at the stage where I have the search functions runing smoothly. Except the way I have the database set up is that the main table holds the business type as a numeral which was entered from another table with coresponding names. I have built views to tie these two tables together. Now once a person searchs the listings they currently recieve a page of results, it is here that the problem occurs. I need to to in my sql statment tie the main table of data to the view to pull the business type name out instead of the business type numeral which is contained within the main data table. Here is my current Sql Statement: SELECT * FROM dbo.MAIN, dbo.VIEW_BUSINESS_NAME WHERE ENTRY_TYPE LIKE 'varSearchType' AND LAST_NAME LIKE '%varName%' AND INITIAL LIKE '%varInitial%' AND STREET_NAME LIKE 'varStreet' AND STATE LIKE 'varState' AND REGION LIKE 'varRegion' AND BUSINESS_TYPE LIKE 'varBusinessType' AND dbo.VIEW_BUSINESS_NAME.Expr1 = dbo.MAIN.BUSINESS_TYPE ORDER BY LAST_NAME It is being put together in dreamweaver mx. This is returning a multiple list of the one business repeated for as many listings as there are for that business type! Hope this makes sense it's very early in the morning.
VIEWS ON THIS POST

228

Posted on:

Friday 26th October 2012
View Replies!