Jump to content

Search the Community

Showing results for tags 'MSSQL'.



More search options

  • Search By Tags

    Type tags separated by commas.
  • Search By Author

Content Type


Forums

  • Welcome
    • Announcements & News
    • New Arrivals
  • Web Development
    • Programming
    • Database Development
    • Server Administration
    • Hosting & Domains
    • Frameworks
  • Web Design
    • HTML & CSS
    • Graphics & Multimedia
  • Desktop Discussion
    • Linux Development
    • Windows Development
    • Mac/Apple Development
    • Hardware Discussion
  • Marketing & Business
    • Advertising, Marketing, Monetization & Social Media
    • Search Engine Optimization & Traffic Building
    • Buy, Sell or Trade
  • Prodjex Web Development Applications, Tools and Plugins
    • IP.Board Applications and Plugins
    • Web Tools
  • The Developer Dump
    • General Chat

Calendars

  • Community Calendar

Found 10 results

  1. MSSQL query of SQl

    Can any one tell me how to add(sum) two columns from two different tables but from same database. please send me exact query if you know..
  2. So who's tried it? I know Marc just got a book on it and started studying for certifications. I have yet to take a look.
  3. Have had people ask me in the past what is the best way to go about optional parameters when using a stored procedure in sql server, so thought I would put up the way in which I do this Example CREATE PROC blahblah @startdate DATETIME, @enddate DATETIME, @customerid INT AS SELECT * FROM myOrders WHERE CustomerID = @customerid AND orderdate BETWEEN @startdate AND @enddate Now lets say in the above we want to have customerid as optionally set. To do this we would need to give @customerid a default value. So we do this by adding "=NULL" to the end which will give it a default of null. However since it would not return any records this way due to the customer always having a value, we can achieve this by saying we want the record returned either if the customerid is same as we set or if NULL is the same as @customerID. Therefore setting null would actually return a record regardless of its value, as if we dont set it NULL will always equal @customerid (which would be NULL). Example CREATE PROC blahblah @startdate DATETIME, @enddate DATETIME, @customerid INT = NULL AS SELECT * FROM myOrders WHERE (CustomerID = @customerid OR @customerid = NULL) AND orderdate BETWEEN @startdate AND @enddate
  4. Thought I would post up a bit of an example on row numbers within MSSQL. This is something that can come in handy in a multitude of senarios and there are a couple of handy things you can do with in sql server. Lets say I have a dataset consisting of just name, value and I want the top 10 with the highest value first along with their rank. This is somewhere that adding a row number would be quite handy. So do this you would use the following: SELECT TOP 10 ROW_NUMBER() OVER(ORDER BY value DESC) as RANKING, [name],[value] This will give me a row number next to each result line when the dataset is ordered by value descending. Top 10 added to give me the top 10 results Now lets go further than this and say we have another column known as dept. This time I want the order of value, but I want it for each department. So sales for example would have 1-x, as would accounts. SELECT TOP 10 ROW_NUMBER() OVER(PARTITION BY dept ORDER BY value DESC) as RANKING, dept, [name],[value] This will order by dept then by value. The row number will be reset for each dept and therefore giving me a ranking for each department
  5. Sometimes if the database is not designed correctly you will find dates stored as text values. When trying to compare dates to text you will have problems and it's not efficient. So that's where the cast function comes in. Table: Invoice Field: InvoiceDate Data Set: 20080515 20081025 20070113 20091007 Expected Data Set: 05/15/2008 10/25/2008 01/13/2007 10/07/2009 To do this simply cast the field as a date: select cast(InvoiceDate as datetime) from Invoice
  6. Some time ago I was reading data from a flat .txt file and inserting it into my database. My table in the database was only setup to handle 40 characters for the company or person's last name. If it was a company some would be longer than 40 and my job would fail. I chose to fix it with the substring function in MSSQL. Table: Debtor Field: RespLName My original code: select case when ltrim(rtrim(RespLName)) is null then P.LastName else ltrim(rtrim(RespLName)) end as LastName from Debtor After adding the substring function: select case when ltrim(rtrim(substring(RespLName,0,40))) is null then P.LastName else ltrim(rtrim(substring(RespLName,0,40))) end as LastName from Debtor
  7. MSSQL dateadd function

    With this example I was trying to subtract 30 days from the current date. You can do this with the dateadd() function in MSSQL. select getdate(day,-30,getdate())
  8. MSSQL Rounding Numbers

    A while back I was trying to round some numbers to only 2 digits after the decimal in MSSQL. I figured out how to do and wanted to share. Name of Table: Invoice Field Name: BalanceDue Data Set: 100.0001 34.532 49.4732 234.567000 Expected Data Set: 100.00 34.53 49.47 234.57 To do this you can use the ROUND function within MSSQL. select round(BalanceDue,2) from Invoice
  9. This is a quick and easy way to change the owner of an object if needed with sp_changedobjectowner sp_changeobjectowner 'oldownername.nameofobject', 'newowner' example: sp_changeobjectowner 'nathan.p_Customer1', 'nriley' The following changes the owner from "nathan" to "nriley"
  10. The following script will allow you to search all the data in all the tables within a certain database. This is useful for poorly labeled databases or when you have no data diagram to work off. If you know a unique piece of data that you see within the application itself you can then query the database for it. However, I wouldn't recommend running it on a production server during business hours. All you need to do is replace "DatabaseName" with you database name. Then execute it and it will be written into your database. Then just call it with the string of text you are looking for example: SearchAllTables '%Important Data%' USE [DATABASENAME] GO /****** Object: StoredProcedure [dbo].[searchAllTables] Script Date: 07/30/2010 10:37:52 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROC [dbo].[searchAllTables] ( @SearchStr nvarchar(100) ) AS BEGIN CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630)) SET NOCOUNT ON DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110) SET @TableName = '' SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''') WHILE @TableName IS NOT NULL BEGIN SET @ColumnName = '' SET @TableName = ( SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName AND OBJECTPROPERTY( OBJECT_ID( QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) ), 'IsMSShipped' ) = 0 ) WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL) BEGIN SET @ColumnName = ( SELECT MIN(QUOTENAME(COLUMN_NAME)) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2) AND TABLE_NAME = PARSENAME(@TableName, 1) AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar') AND QUOTENAME(COLUMN_NAME) > @ColumnName ) IF @ColumnName IS NOT NULL BEGIN INSERT INTO #Results EXEC ( 'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' + ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2 ) END END END SELECT ColumnName, ColumnValue FROM #Results END
×