Friday, June 21, 2013

[SQL Server] Stored procedures

[SQL Server] Stored procedures

Stored procedures

Posted: 21 Jun 2013 01:47 AM PDT

I'm writing a stored procedure to update an employee in the employee table. Each employee in the table has 17 fields. The data is coming from a web form where 3 fields are used to identify the employee. If any other fields contain data, the employee needs to be updated, but only for those fields. In my .net page, I concatenate together the SQL string based upon which form fields contain data. IE:sql = "UPDATE notitiae.dbo.tblpeople SET"If Request.Form("title") <> "" Then sql = sql & " notitiae.dbo.tblPeople.Title = @title"end if...How do I duplicate this in my stored procedure? Do I create a local variable like @SQL and concatenate the string the same way? How then would I tell the procedure to process the string in the variable @SQL?

Changing the Time of a field under a certain condition

Posted: 21 Jun 2013 04:31 AM PDT

This stemmed from my previous post yesterday but is a completely different question What i Need to do: i Need to have the first Enddate Time of the first ID group to be set to 0 and the DateStart Time of the last of the IDs be set to 0 if the dateStart time is greater than dateEnd timeThe end result is so that i can find out how many hours are in the AM and PMThe amount of Ids can vary but will always be different Any help or suggestions would be appreciated [code="sql"]--===== If the test table already exists, drop it IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL DROP TABLE #mytable IF OBJECT_ID('TempDB..#outputtable','U') IS NOT NULL DROP TABLE #outputtable--------Create test table---------- CREATE TABLE #mytable ( DateStart DATETIME, DateEnd DATETIME, ID int )---------Sample Data------- Insert into #mytable (DateStart,DateEnd,ID) Select '2013-05-25 17:00:00.000', '2013-05-25 07:15:00.000', 879 UNION ALL Select '2013-05-26 17:00:00.000', '2013-05-26 07:15:00.000', 879 UNION ALL Select '2013-05-27 17:00:00.000', '2013-05-27 07:15:00.000', 879 Union ALL Select '2013-05-31 17:00:00.000', '2013-05-31 07:15:00.000', 880 union ALL Select '2013-06-01 17:00:00.000', '2013-06-01 07:15:00.000', 880 Union All Select '2013-06-02 17:00:00.000', '2013-06-02 07:15:00.000', 880 union ALL Select '2013-03-15 08:00:00.000', '2013-03-15 17:00:00.000', 1266 Union ALL Select '2013-03-16 08:00:00.000', '2013-03-16 17:00:00.000', 1266 union ALL Select '2013-03-17 08:00:00.000', '2013-03-17 17:00:00.000', 1266 --------Create desired Output table---------- CREATE TABLE #outputtable ( DateStart DATETIME, DateEnd DATETIME, ID int )---------OutPut Data------- Insert into #outPutTable(DateStart,DateEnd,ID) Select '2013-05-25 17:00:00.000', '2013-05-25 00:00:00.000', 879 UNION ALL -- the EndDate Time has changed to 0 Select '2013-05-26 17:00:00.000', '2013-05-26 07:15:00.000', 879 UNION ALL Select '2013-05-27 00:00:00.000', '2013-05-27 07:15:00.000', 879 Union ALL -- the StartDate time has changed to 0 Select '2013-05-31 17:00:00.000', '2013-05-31 00:00:00.000', 880 union ALL -- the EndDate Time has changed to 0 Select '2013-06-01 17:00:00.000', '2013-06-01 07:15:00.000', 880 Union All Select '2013-06-02 00:00:00.000', '2013-06-02 07:15:00.000', 880 union ALL -- the startDate Time has changed to 0 Select '2013-03-15 08:00:00.000', '2013-03-15 17:00:00.000', 1266 Union ALL-- this set is fine because the startdate time is less then enddate time Select '2013-03-16 08:00:00.000', '2013-03-16 17:00:00.000', 1266 union ALL Select '2013-03-17 08:00:00.000', '2013-03-17 17:00:00.000', 1266 select * from #mytable select * from #OutPutTable /** What i Need to do: i Need to have the first Enddate Time of the first ID to be set to 0 and the DateStart Time of the last of the ID group be set to 0 if the dateStart time is greater than dateEnd time **/[/code] The amount of Ids can vary but will always be different

Can some take a look at my code and tell me if it is structured right.

Posted: 21 Jun 2013 03:00 AM PDT

I am having trouble where it starts with:AND jomast.fcompany LIKE 'AT&T*', 'Centerpoint*', 'Cingular*', 'Dynis*', 'Global*', 'Sabre*', 'US*', 'Verizon*'and down.It does not Like the (Like) statement. I have tried everything.The bottom half is to go into the database and find all job boms that match AT&T, Centerpoint, etc and if there is a bom, it is tolook at the fparent name and if it ends with '%-10', AVG the fquantity used and place in a new colunm named 'Casting-10' and so on down the line.SELECT jomast.fpartno AS 'Bom Part No', jomast.fdescript AS 'Bom Desc', jomast.fmeasure AS 'U/M', inmast.fstdcost AS 'Unit Cost', inmast.flocate1 AS 'Bin Loc', inmast.fytdiss AS 'YTD Issued' FROM m2mdata01.dbo.inboms inboms, m2mdata01.dbo.inmast inmast, m2mdata01.dbo.jomast jomast WHERE inboms.identity_column = jomast.identity_column AND inmast.identity_column = inboms.identity_column AND inmast.identity_column = jomast.identity_columnAND jomast.fcompany LIKE 'AT&T*', 'Centerpoint*', 'Cingular*', 'Dynis*', 'Global*', 'Sabre*', 'US*', 'Verizon*'AND inboms.fparent LIKE '%-10' (AVG(jomast.fquantity)) AS 'Casting-10'AND inboms.fparent LIKE '%-20' (AVG(jomast.fquantity)) AS 'Carpentry-20' AND inboms.fparent LIKE '%-30' (AVG(jomast.fquantity)) AS 'Conduit-30' AND inboms.fparent LIKE '%-40' (AVG(jomast.fquantity)) AS 'Electrical-40'AND inboms.fparent LIKE '%-50' (AVG(jomast.fquantity)) AS 'Mechanical-50'AND inboms.fparent LIKE '%-51' (AVG(jomast.fquantity)) AS 'Doors-51'AND inboms.fparent LIKE '%-75' (AVG(jomast.fquantity)) AS 'Generators-75'GROUP BY Bom Part No

SQL Locks

Posted: 20 Jun 2013 10:58 PM PDT

Hello Masters,How can we count the total number of locks in sql server 2005\2008 ?Is there any query\options to check it ?

Reporting Services: Please help

Posted: 20 Jun 2013 06:35 PM PDT

HiI am using ssrs, now i have this report that runs hourly everyday.. I have reduce the hours so it must run from 12 am to 06 pm and must not run on weekends. How do I go about doing that?
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog