SQL Server SCRIPTING USE Accounting DECLARE Ident int

SQL Server SCRIPTING

ﺍﺳکﺮیپﺖ ﺩﺭﺝ ﻣﻘﺪﺍﺭ ﺩﺭ ﺟﺪﻭﻝ USE Accounting; DECLARE @Ident int; INSERT INTO Orders (Customer. No, Order. Date, Employee. ID) VALUES (g. ETDATE, 1); SELECT @Ident = @@IDENTITY; INSERT INTO Order. Details (Order. ID, Part. No, Description, Unit. Price, Qty) VALUES (@Ident, ‘ 2 R 2416’, ‘Cylinder Head’, 1300, 2); SELECT ‘The Order. ID of the INSERTed row is ‘ + CONVERT(varchar(8), @Ident);

SET ﺍﺯ ﺍﺳﺘﻔﺎﺩﻩ � USE Adventure. Works 2008; � DECLARE @Test money; � SET @Test = (SELECT MAX(Unit. Price) FROM Sales. Order. Detail) � SELECT @Test;

ﺍﻧﺘﺨﺎﺏ ﺑﺮﺍی SELECT ﺍﺳﺘﻔﺎﺩﻩ ﺍﺯ � USE Adventure. Works 2008; � DECLARE @Test money; � SELECT @Test = MAX)Unit. Price) FROM Sales. Order. Detail; � SELECT @Test;

SQL Server چﻨﺪ ﻣﺘﻐیﺮ ﺳیﺴﺘﻤی � @@ERROR � @@IDENTITY � @@REMSERVER � @@ROWCOUNT � @@SERVERNAME � @@TRANCOUNT � @@VERSION

USE Adventure. Works 2008; � � GO � DECLARE @RCount int; � SELECT * FROM Person; � � SELECT @RCount = @@ROWCOUNT;

Batch USE Adventure. Works 2008; DECLARE @My. Varchar varchar)50); --This DECLARE only lasts for this batch! SELECT @My. Varchar = ‘Honey, I’’m home; ’. . . PRINT ‘Done with first Batch; ’. . . GO PRINT @My. Varchar; --This generates an error since @My. Varchar -- isn’t declared in this batch PRINT ‘Done with second Batch; ’ GO PRINT ‘Done with third batch’; -- Notice that this still gets executed -- even after the error GO

ﻧﺘیﺠﻪ ﺍﺟﺮﺍی ﺍﺳکﺮیپﺖ ﻗﺒﻞ � Done with first Batch. . . � Msg 137, Level 15, State 2, Line 2 � Must declare the scalar variable “@My. Varchar. ” � Done with third batch

ﺟﺪﺍگﺎﻧﻪ ﻧﻮﺷﺘﻪ ﺷﻮﻧﺪ Batch ﺩﺳﺘﻮﺭﺍﺗی کﻪ ﺑﺎیﺪ ﺩﺭ � CREATE DEFAULT � CREATE PROCEDURE � CREATE RULE � CREATE TRIGGER � CREATE VIEW

� USE Test; � SELECT TABLE_CATALOG � FROM INFORMATION_SCHEMA. TABLES � WHERE TABLE_NAME = ‘Test. Table; ’

Command ﺍﺯ ﻃﺮیﻖ SQL ﺍﺟﺮﺍی ﺩﺳﺘﻮﺭﺍﺕ � C: >sqlcmd -Usa -Pmypass -i testsql. sql [ -d <db name> ] [ -o <output file> ] � � SQLCMD -Usa -Pmypass –Q “SELECT * FROM Adventure. Works 2008. Production. Locati on”

EXEC ﺍﺯ ﺍﺳﺘﻔﺎﺩﻩ ﻧکﺎﺕ runs under a separate scope than code that calls it � By default, it runs under the same security context as the current user � It runs under the same connection and transaction context as the calling object � Concatenation that requires a function call must be performed on the EXEC string prior to actually calling the EXEC statement � EXEC NOT Availabe inside a user-defined function. �

WAITFOR ﺩﺳﺘﻮﺭ � WAITFOR DELAY ‘ 01: 00’; � WAITFOR TIME ‘ 10: 00’;

IF NOT EXISTS ( SELECT s. name AS Schema. Name, t. name AS Table. Name FROM sys. schemas s � JOIN sys. tables t ON s. schema_id = t. schema_id � WHERE s. name = ‘dbo’ AND t. name = ‘Our. IFTest’) � � � CREATE TABLE Our. IFTest( � Col 1 int PRIMARY KEY); � SELECT ‘Found Table ‘ + s. name + ‘. ’ + t. name � FROM sys. schemas s � JOIN sys. tables t ON s. schema_id = t. schema_id � WHERE s. name = ‘dbo’ AND t. name = ‘Our. IFTest’;

� � � Use Adventure. Works 2008; GO SELECT TOP 10 Sales. Order. ID, Sales. Order. ID % 10 AS ‘Last Digit’, Position = CASE Sales. Order. ID % 10 � WHEN 1 THEN ‘First’ � WHEN 2 THEN ‘Second’ � WHEN 3 THEN ‘Third’ � WHEN 4 THEN ‘Fourth’ � ELSE ‘Something Else’ � � END FROM Sales. Order. Header;

� � � � � SELECT TOP 10 Sales. Order. ID % 10 AS ‘Order. Last. Digit’, Product. ID % 10 AS ‘Product. Last. Digit’, “How Close? ” = CASE WHEN (Sales. Order. ID % 10) < 3 THEN ‘Ends With Less Than Three’ WHEN Product. ID = 6 THEN ‘Product. ID is 6’ WHEN ABS(Sales. Order. ID % 10 - Product. ID) <= 1 THEN ‘Within 1’ ELSE ‘More Than One Apart’ END FROM Sales. Order. Detail ORDER BY Sales. Order. ID DESC;

ﺍﺳکﺮیپﺖ ﺩﺭ ﺧﻄﺎﻫﺎ ﻣﻬﺎﺭ � BEGIN TRY � { <sql statement(s)> } � END TRY � BEGIN CATCH � { <sql statement(s)> } � END CATCH [ ; ]

� � � � BEGIN TRY CREATE TABLE Our. IFTest(Col 1 int PRIMARY KEY); END TRY BEGIN CATCH DECLARE @Error. No int, @Severity tinyint, @State smallint, @Line. No int, @Message nvarchar(4000); SELECT @Error. No = ERROR_NUMBER(), @Severity = ERROR_SEVERITY(), @State = ERROR_STATE(), @Line. No = ERROR_LINE (), @Message = ERROR_MESSAGE(); IF @Error. No = 2714 PRINT ‘WARNING: Skipping CREATE as table already exists’; ELSE RAISERROR(@Message, 16, 1 ); END CATCH