جستجو یا پیدا کردن یک پارامتر
کد:
CREATE PROCEDURE usp_FindAuthor
/* ALL parameters are optional */
@LastName varchar(200) = NULL,
@FirstName varchar(200) = NULL,
@ZipCode varchar(200) = NULL,
@Contract varchar(200) = NULL
AS
IF @LastName IS NULL
BEGIN
/* IF the parameter IS null, THEN this statement will
cause the SELECT TO NOT filter BY last name. */
SET @LastName = '%'
END
ELSE
BEGIN
/* Since we want TO search FOR this last name, ADD the
wildcard TO require the SELECT to RETURN ALL
last names that BEGIN WITH this value. */
SET @LastName = @LastName + '%'
END
IF @FirstName IS NULL
BEGIN
/* IF the parameter IS null, THEN this statement will
cause the SELECT TO NOT filter BY first name. */
SET @FirstName = '%'
END
ELSE
BEGIN
/* Since we want TO search FOR this last name, ADD the
wildcard TO require the SELECT to RETURN ALL
first names that BEGIN WITH this value. */
SET @FirstName = @FirstName + '%'
END
IF @ZipCode IS NULL
BEGIN
/* IF the parameter IS null, THEN this statement will
cause the SELECT TO NOT filter BY zip code. */
SET @ZipCode = '%'
END
ELSE
BEGIN
/* Since we want TO search FOR this zip code, ADD the
wildcard TO require the SELECT to RETURN ALL
zip codes that BEGIN WITH this value. */
SET @ZipCode = @ZipCode + '%'
END
IF @Contract IS NULL
BEGIN
/* IF the parameter IS null, THEN this statement will
cause the SELECT TO NOT filter BY contract. */
SET @Contract = '%'
END
/* Since we want TO search FOR contracted authors AND
this is a bit field, do NOT ADD the wildcard.
The same IS true FOR numeric fields AND dates. */
SELECT
au_id,
au_lname,
au_fname,
phone,
address,
city,
state,
zip,
contract
FROM
pubs..authors WITH (NOLOCK)
WHERE
au_lname LIKE @LastName
AND
au_fname LIKE @FirstName
AND
zip LIKE @ZipCode
AND
contract LIKE @Contract
ساخت یک برنامه بک آپ گیری
کد:
USE master
go
DECLARE @ServerName varchar(400)
DECLARE @DatabaseName varchar(400)
DECLARE @BackupPath varchar(400)
DECLARE @TSQLCommand varchar(2000)
DECLARE @FullBackupJob varchar(2000)
DECLARE @DiffBackupJob varchar(2000)
DECLARE @BackupDevice varchar(2000)
DECLARE @FullStepName varchar(2000)
DECLARE @DiffStepName varchar(2000)
DECLARE @FullSchName varchar(2000)
DECLARE @DiffSchName varchar(2000)
SET @ServerName = HOST_NAME()
SET @DatabaseName = 'Database name'
SET @BackupPath = 'Unit:\CompletePath\' + @DatabaseName + '.bk!'
SET @FullBackupJob = @DatabaseName + '_Weekly_Full_Backup'
SET @DiffBackupJob = @DatabaseName + '_Daily_Diff_Backup'
SET @BackupDevice = @DatabaseName + '_Backup'
SET @FullStepName = @DatabaseName + '_Full_Backup'
SET @FullSchName = 'Schedule_' + @DatabaseName + '_Weekly_Full_Backup'
SET @DiffSchName = 'Schedule_' + @DatabaseName + '_Daily_Diff_Backup'
SET @DiffStepName = @DatabaseName + '_Diff_Backup'
-- Delete the device if exists
IF EXISTS (SELECT name FROM master.dbo.sysdevices WHERE name = @BackupDevice)
BEGIN
EXEC sp_dropdevice @BackupDevice
END
IF EXISTS (SELECT name FROM msdb.dbo.sysjobs WHERE name = @FullBackupJob)
BEGIN
EXEC msdb.dbo.sp_delete_job @job_name = @FullBackupJob
END
IF EXISTS (SELECT name FROM msdb.dbo.sysjobs WHERE name = @DiffBackupJob)
BEGIN
EXEC msdb.dbo.sp_delete_job @job_name = @DiffBackupJob
END
EXEC sp_addumpdevice 'disk', @BackupDevice, @BackupPath
EXEC msdb.dbo.sp_add_job @job_name = @FullBackupJob,
@owner_login_name = 'sa' -- Creates the job OF FULL BACKUP
EXEC msdb.dbo.sp_add_jobserver @job_name = @FullBackupJob,
@server_name = @ServerName
SET @TSQLCommand = 'BACKUP DATABASE [' + @DatabaseName + '] TO [' + @DatabaseName + '_Backup] WITH INIT , NOUNLOAD , NAME = N''' + @DatabaseName + '_Weekly_Full_Backup'', NOSKIP , STATS = 10, NOFORMAT'
EXEC msdb.dbo.sp_add_jobstep @job_name = @FullBackupJob,
@step_name = @FullStepName,
@subsystem = 'TSQL',
@command = @TSQLCommand,
@retry_attempts = 5,
@retry_interval = 5
EXEC msdb.dbo.sp_add_jobschedule @job_name = @FullBackupJob,
@name = @FullSchName, -- Creates the Job Schedule
@freq_type = 8, -- Weekly
@freq_interval = 2, -- Monday
@freq_recurrence_factor = 1, -- Every week
@active_start_time = 083000 -- HHMMSS (08:30:00 AM)
EXEC msdb.dbo.sp_add_job @job_name = @DiffBackupJob,
@owner_login_name = 'sa' -- Creates the job OF Differencial BACKUP
EXEC msdb.dbo.sp_add_jobserver @job_name = @DiffBackupJob,
@server_name = @ServerName
EXEC msdb.dbo.sp_add_jobschedule @job_name = @DiffBackupJob,
@name = @DiffSchName, -- Creates the Job Schedule
@freq_type = 8, -- Weekly
@freq_interval = 60, -- Tuesday, Wednesday, Thursday AND Friday
@freq_recurrence_factor = 1, -- Every week
@active_start_time = 083000 -- HHMMSS (08:30:00 AM)
SET @TSQLCommand = 'BACKUP DATABASE [' + @DatabaseName + '] TO [' + @DatabaseName + '_Backup] WITH NOINIT , NOUNLOAD , DIFFERENTIAL , NAME = N''' + @DatabaseName + '_Daily_Diff_Backup'', NOSKIP , STATS = 10, NOFORMAT '
EXEC msdb.dbo.sp_add_jobstep @job_name = @DiffBackupJob,
@step_name = @DiffStepName,
@subsystem = 'TSQL',
@command = @TSQLCommand,
@retry_attempts = 5,
@retry_interval = 5
تبدیل عدد به حرف
کد:
CREATE function dbo.fNumberToText (@num AS numeric(18,2), @isMoney as bit)
returns varchar(255)
AS
BEGIN
--converts numeric value to string
--handles up to 2 decimal places, with r
-- ounding
--if @isMoney = true then it will output
-- dollars and cents
--created by: brendan kerry
--created on: 14/12/2006
--declare @num numeric(18,2)
--declare @isMoney bit
--set @num = 8000000000.17
--set @isMoney = 1
DECLARE @Split tinyint
DECLARE @iSplit tinyint
DECLARE @sNum varchar(20)
DECLARE @NumSet varchar(3)
DECLARE @Char char(1)
DECLARE @NumText varchar(255)
DECLARE @SetText varchar(100)
DECLARE @HunText varchar(100)
DECLARE @CharText varchar(50)
DECLARE @Match bit
DECLARE @Point tinyint
DECLARE @Cents varchar(2)
DECLARE @CentText varchar(100)
DECLARE @NumStrings TABLE (Num int, NumStr varchar(20))
INSERT INTO @NumStrings
SELECT 1, ' One'
UNION SELECT 2, ' Two'
UNION SELECT 3, ' Three'
UNION SELECT 4, ' Four'
UNION SELECT 5, ' Five'
UNION SELECT 6, ' Six'
UNION SELECT 7, ' Seven'
UNION SELECT 8, ' Eight'
UNION SELECT 9, ' Nine'
UNION SELECT 10, ' Ten'
UNION SELECT 11, ' Eleven'
UNION SELECT 12, ' Twelve'
UNION SELECT 13, ' Thirteen'
UNION SELECT 14, ' Fourteen'
UNION SELECT 15, ' Fifteen'
UNION SELECT 16, ' Sixteen'
UNION SELECT 17, ' Seventeen'
UNION SELECT 18, ' Eighteen'
UNION SELECT 19, ' Nineteen'
UNION SELECT 20, ' Twenty'
UNION SELECT 30, ' Thirty'
UNION SELECT 40, ' Fourty'
UNION SELECT 50, ' Fifty'
UNION SELECT 60, ' Sixty'
UNION SELECT 70, ' Seventy'
UNION SELECT 80, ' Eighty'
UNION SELECT 90, ' Ninety'
SET @sNum = cast(@num as varchar(20))
--convert any cent text first, then the
-- whole number
SET @Point = charindex('.', @sNum)
IF @Point > 0
BEGIN
SET @Cents = substring(@sNum, @Point + 1, 2)
SET @sNum = left(@sNum, @Point-1)
--if isMoney THEN combine the two digits (eg 11 = eleven)
IF @isMoney = 1
BEGIN --look FOR matches WITH the RIGHT two characters
SET @Match = (select count(*) FROM @NumStrings WHERE Num = @Cents)
IF @Match <> 0
BEGIN
SET @CentText = (select NumStr FROM @NumStrings WHERE Num = @Cents)
END
ELSE
BEGIN
SET @CentText = isnull((select NumStr FROM @NumStrings WHERE Num = left(@Cents, 1) + '0'),'') + (select NumStr from @NumStrings where Num = right(@Cents, 1))
END
END
ELSE --if NOT isMoney THEN treat each digit seperately (eg 11 = one one)
BEGIN
SET @CentText = isnull((select NumStr FROM @NumStrings WHERE Num = left(@Cents, 1)),'') + isnull((select NumStr from @NumStrings where Num = right(@Cents, 1)),'')
END
END
IF @CentText IS NULL
SET @CentText = ''
--break the number into blocks of 3 char
-- acters
SET @Split = ((len(@sNum)-1) / 3) + 1
SET @iSplit = 0
SET @NumText = ''
WHILE @iSplit < @Split
BEGIN
SET @CharText = ''
SET @HunText = ''
SET @SetText = ''
SET @NumSet = right(left(@sNum, len(@sNum) - @iSplit * 3), 3)
IF len(@Numset) = 3 --Calculate ANY hundreds
BEGIN
SET @Char = left(@NumSet, 1)
SET @HunText = isnull((select NumStr FROM @NumStrings WHERE Num = @Char) + ' Hundred', '')
SET @SetText = @HunText
END
--look FOR matches WITH the RIGHT two characters
SET @Match = (select count(*) FROM @NumStrings WHERE Num = right(@NumSet, 2))
IF @Match <> 0
BEGIN
SET @CharText = (select NumStr FROM @NumStrings WHERE Num = right(@NumSet, 2))
END
ELSE
BEGIN
SET @CharText = isnull((select NumStr FROM @NumStrings WHERE Num = left(right(@NumSet, 2), 1) + '0'),'') + (select NumStr from @NumStrings where Num = right(@NumSet, 1))
END
--make sure there IS something in @CharText AND @SetText (ie IF @NumSet = x00, x000)
IF @CharText IS NULL SET @CharText = ''
IF @SetText IS NULL set @SetText = ''
--seperate ANY hundreds FROM tens/units WITH an 'and'
IF @HunText <> '' AND @CharText <> ''
SET @SetText = @SetText + ' and'
--if there are no hundreds, it's the smallest SET AND there are other sets, ADD an 'and' before
IF @HunText = '' AND @CharText <> '' and @iSplit = 0 and @Split > 1 and @CentText = ''
SET @SetText = ' and' + @SetText
SET @SetText = @SetText + @CharText
--append the SET suffix text. ADD a ',' AS long as there IS something in the lowest set
IF @SetText <> ''
SET @SetText = @SetText + CASE @iSplit
WHEN 0 THEN ''
WHEN 1 THEN ' Thousand'
WHEN 2 THEN ' Million'
WHEN 3 THEN ' Billion'
WHEN 4 THEN ' Trillion'
WHEN 5 THEN ' Quadrillion'
END + CASE WHEN len(@NumText) > 0 THEN ',' ELSE '' end
SET @NumText = @SetText + @NumText
SET @iSplit = @iSplit + 1
END
--add any dollars and cent text
IF @isMoney = 1 AND @NumText <> '' and rtrim(ltrim(@NumText)) <> 'One'
SET @NumText = @NumText + ' Dollars'
IF @isMoney = 1 AND rtrim(ltrim(@NumText)) = 'One'
SET @NumText = @NumText + ' Dollar'
IF @isMoney = 1 AND @CentText <> '' and rtrim(ltrim(@CentText)) <> 'One'
SET @CentText = @CentText + ' Cents'
IF @isMoney = 1 AND rtrim(ltrim(@CentText)) = 'One'
SET @CentText = @CentText + ' Cent'
IF @isMoney = 0 AND @CentText <> ''
SET @CentText = ' Point' + @CentText
IF @isMoney = 1 AND @NumText <> '' and @CentText <> ''
SET @CentText = ' and' + @CentText
--combine dollars and cents
SET @NumText = @NumText + @CentText
--add 'Minus' for negative numbers
IF left(@sNum, 1) = '-'
SET @NumText = 'Minus' + @NumText
RETURN @NumText
END
تولید n رشته های تصادفی
کد:
SET nocount ON
DECLARE @i int
DECLARE @Length int
DECLARE @NumberOfChars int
/* Change the next two lines to vary the length */
DECLARE @Output varchar(10)
SET @NumberOfChars = 10
SET @Length = 0
SET @Output = ''
WHILE (@Length < @NumberOfChars)
BEGIN
SET @i = cast(rand(convert(int, convert(varbinary, newid()))) * 100 as int)
IF (
(@i >= 48 AND @i <= 57)
OR
(@i >= 65 AND @i <= 90))
BEGIN
set @Output = @Output + char(@i)
set @Length = @Length + 1
END
END
PRINT @Output
برنامه n وزیر با اس کیو ال (SQL N Queens) و دو برنامه تقویم با اس کیو ال
این برنامه ها را رو از لینک زیر دریافت کنید!
درخواست هر گونه مقاله و مطلب و... و پاسخ سوالات گوناگون شما (در رابطه با تالار رشته ي كامپيوتر و فناوري اطلاعات)
نوشته اصلی توسط asp
پروژه های اس کیو ال (sql)
تاپيك در خواست مقالات مختلف كامپيوتري
پاسخ با نقل قول
