Posted on Monday, August 31, 2009 at 7:59 AM
The following code is an example of how to find a list of all person records that have the firstname of 'Jim' (probably not the best way, but this is just an example of this method).
SELECT
PersonId
FROM
Person
WHERE
'Jim' IN (SELECT FirstName FROM Person)
Posted on Wednesday, August 26, 2009 at 10:53 AM
First we need to setup a row counter and a row max. In this example I'm iterating through a table of coupons.
DECLARE @RowCounter int
DECLARE @RowMax int
SET @RowCounter = 1
SET @RowMax = (SELECT COUNT(*) FROM Coupon)
And this is how the while loop is created
WHILE (@RowCounter <= @RowMax)
BEGIN
-- Here you could enter real logic to process, I am just printing the RowCounter.
PRINT 'This is row ' + CAST(@RowCounter as nvarchar(10))
-- The RowCounter must be incremented each time to ensure the loop continues on.
SET @RowCounter = @RowCounter + 1
END
Comments:
0
Tags:
loop,
MSSQL,
Count
Posted on Wednesday, August 26, 2009 at 6:58 AM
I use this frequently to make quick-and-dirty data backups when I am experimenting with scripts. THIS SHOULD NOT REPLACE REAL DATA BACKUPS!
SELECT *
INTO
DestinationTable
FROM
OriginTable
Comments:
0
Tags:
Copy,
MSSQL
Posted on Tuesday, August 25, 2009 at 10:24 AM
First we need to create the function...
CREATE FUNCTION dbo.AddTwoNumbers(@Value1 as int, @Value2 as int)
RETURNS int
BEGIN
DECLARE @Result int
SET @Result = @Value1 + @Value2
RETURN @Result
END
GO
Here is how we actually use the function...
SELECT dbo.AddTwoNumbers(5, 8) AS Result
GO
The SELECT statement outputs the integer 13.
Posted on Tuesday, August 25, 2009 at 10:18 AM
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Person]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Person]
(
PersonId int IDENTITY(1,1) NOT NULL,
FirstName nvarchar(50) NOT NULL,
LastName nvarchar(50) NOT NULL,
BirthDate date NOT NULL,
FavoriteColor nvarchar(50) NULL CONSTRAINT [DF_Person__FavoriteColor] DEFAULT(''),
CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED (PersonId)
)
END
GO
Comments:
0
Tags:
MSSQL,
Create Table
