Feeds:
Posts
Comments

Posts Tagged ‘Name’

declare @columnList varchar(5000) = '' 
-- The will give you a actual name of all the Fields in a table select @columnList = @columnList + case when column_id = 1 then '' else ',' end + '[' + name + ']'from sys.columns cwhere object_id = object_id('schema.aTable')
print 'Field Captions: ' + @columnList

set @columnList = null -- reinitialising. -- The below will give you the a list of all values of a field as a CSV set @columnList = null -- reinitialise SELECT @columnList= isnull(@columnList + ',[' + FieldName + ']' ,'[' + FieldName + ']')FROM aTable
print 'Values of a field: ' + @columnList

Read Full Post »

-- Returns the Week Day Names and its correponding Week Number (taking Sunday as 1)
--==================================================
--alter proc dbo.usp_getWeekDayNames
--as
SET DATEFIRST 1 -- Monday is set to be the first day of the week

DECLARE @c INT

SET @c = 1

DECLARE @WeekDays TABLE (
	WeekDayNumber INT
	,WeekDayName VARCHAR(12)
	)

WHILE (@c & lt;= 7)
BEGIN
	INSERT @WeekDays
	SELECT datepart(weekday, dateadd(day, @c, getdate())) WeekDayNumber
		,datename(weekday, dateadd(day, @c, getdate())) WeekDayName

	SET @c = @c + 1
END

SELECT *
FROM @WeekDays
ORDER BY 1

Read Full Post »

--WARNING! ERRORS ENCOUNTERED DURING SQL PARSING! -- There are two SPs here
--First option:    Returns all month name and number
--==================================================
--create proc dbo.usp_getMonthNameAndNumber
--as
DECLARE @allMonths TABLE (
	MonthNumber INT identity
	,MonthName VARCHAR(10)
	)
DECLARE @count INT

SET @count = 0

WHILE (@count & lt;12)
BEGIN
	INSERT @allMonths
	SELECT dateName(month, dateadd(month, @count, '1 Jan 2010'))

	SET @count = @count + 1
END

SELECT *
FROM @allMonths
ORDER BY 1

--Second option:    Returns Month name and number upto the passed month number
--    (rows are restricted to the passed month)
--==================================================
--create proc dbo.usp_getMonth (@RestrictToMonthNumber int = null)
--as
---------====== Test Values ======
DECLARE @RestrictToMonthNumber INT

SET @RestrictToMonthNumber = 6

-------=============================
-- Note: If null is sent then all months are returned
DECLARE @allMonths TABLE (
	MonthNumber INT identity
	,MonthName VARCHAR(10)
	)

IF @RestrictToMonthNumber IS NULL
	SET @RestrictToMonthNumber = 12

DECLARE @count INT

SET @count = 0

WHILE (@count & lt;@RestrictToMonthNumber)
BEGIN
	INSERT @allMonths
	SELECT dateName(month, dateadd(month, @count, '1 Jan 2010'))

	SET @count = @count + 1
END

SELECT *
FROM @allMonths
ORDER BY 1 

Related post ( used IN SSRS ) :
http: / / wp.me / pAchH – 4 A

Read Full Post »

--- Just a note ---
SELECT @@Datefirst -- By Default this is 7 which denotes Sunday and the values for Monday is 1 whihc cat be set if needed like below:
 -- set Datefirst 1
 ---------------

-- With Datepart we can retrieve the integer value of any part of a datetime value:
--    year, quarter, month, day of the year, day, week number, weekday number, hour, minute, second, or millisecond.
-- with datename we can retrieve its name / string value
-- Example:
SELECT datepart(week, getdate()) WeekNumber
 ,datepart(weekday, getdate()) WeekDayNumber
 ,datepart(month, getdate()) MonthDayNumber

--Week does not have a name so its sting value is returned
SELECT datename(week, getdate()) WeekName
 ,datename(weekday, getdate()) WeekDayName
 ,datename(Month, getdate()) MonthDayName

Result:

Read Full Post »

--Check if a table exists (and drop it if does) before creating it again
IF EXISTS (
  SELECT NAME
  FROM anydbnameOptional.dbo.sysobjects -- use TYPE to specify tables, Sps etc
  WHERE type = 'u' -- <a href="http://msdn.microsoft.com/en-us/library/ms177596.aspx">http://msdn.microsoft.com/en-us/library/ms177596.aspx</a>
   AND NAME = 'whatever'
  )
 DROP TABLE whatever

--Check if a database exists
IF EXISTS (
  SELECT NAME
  FROM master..sysdatabases
  WHERE NAME = 'DatabaseName'
  )
 SELECT 'ok'

DECLARE @anotherWay VARCHAR(100)

-- if select is used then the outer bracket must be used as well
--set @anotherWay = (select DB_NAME())
SET @anotherWay = DB_NAME()

PRINT @anotherWay

--=====================
-- To check and delete an object
--if object_id ('dbo.udf_CapitaAnsweredCallsKPI8') is not null
--    drop function dbo.udf_CapitaAnsweredCallsKPI8
--go
----=========================
-------- =========== Check if a column exists  ================================ --------------------------
IF EXISTS (
  SELECT *
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = 'myTableName'
   AND COLUMN_NAME = 'myColumnName'
  )
 ---- The below way (sys.columns) is depricated
 IF EXISTS (
   SELECT *
   FROM sys.columns
   WHERE NAME = N'columnName'
    AND Object_ID = Object_ID(N'tableName')
   )
 BEGIN
  -- Column Exists
 END
   ----------------------====================================================----------------------------------

Read Full Post »

  1. Create a group in Windows say ‘CubeGroup’
  2. Create as many Windows users and passwords as needed and add them to the group above
  3. Create a role in SSAS cube then add ‘CubeGroup there

Read Full Post »