Strored PROCEDURE examples ( input and Output paramatere)

Use Pubs

Demo 1

/* Simple Stored PROCEDURE */

CREATE PROCEDURE prcGetAuthors

AS

BEGIN

      PRINT 'List of Authors'

      SELECT * FROM authors

END

-- To EXECute the Stored PROCEDURE

EXEC prcGetAuthors

-- To Know the Contents of the Stored PROCEDURE

Sp_helptext prcGetAuthors

DEMO 2

/* Stored PROCEDURE with INput Parameter */

SELECT * FROM authors

CREATE PROCEDURE prcGetAuthorDetails(@au_id varchar(11))

AS

BEGIN

      PRINT 'Details of Author :    ' + @au_id

      SELECT * FROM authors WHERE au_id = @au_id

END

/* To EXECute the PROCEDURE */

EXEC prcGetAuthorDetails '172-32-1176'

DEMO 3

/* Stored PROCEDURE with input and outPut Parameter */

CREATE PROCEDURE prcGetAuthorNames

(@au_id varchar(11),@au_fname varchar(20) output,@au_lname varchar(40) output)

AS

BEGIN

      PRINT 'Details of Author :    ' + @au_id

      SELECT @au_fname = au_fname , @au_lname = au_lname

      FROM authors

      WHERE au_id = @au_id

END

/* To EXECute the Stored PROCEDURE */

DECLARE @afname varchar(20), @alname varchar(40)

EXEC prcGetAuthorNames '172-32-1176' , @afname output , @alname output

SELECT @afname , @alname

Demo 4

/* To CREATE a PROCEDURE that will call the Demo 3 and display the Details */

CREATE PROCEDURE prcDisplayAuthorDetails(@au_id varchar(11))

AS

BEGIN

      Declare @afname varchar(20)

      Declare @alname varchar(40)

      EXEC prcGetAuthorNames @au_id , @afname OUTput , @alname OUTput

      PRINT 'The Author First Name is     :     ' + @afname

      PRINT 'The Author LASt  Name is     :     ' + @alname

END

/* To EXECute the Stored PROCEDURE */

EXEC prcDisplayAuthorDetails '172-32-1176'

Demo 5

/* NotIFyINg Successful EXECution of a PROCEDURE usINg RETURN Value */

CREATE PROCEDURE prcAuthorStatus(@au_id varchar(11))

AS

BEGIN

      IF Exists (SELECT * FROM authors WHERE au_id = @au_id)

      BEGIN

            RETURN 0

      END

      ELSE

      BEGIN

            RETURN 1

      END

END

/* To EXECute the Stored PROCEDURE */

DECLARE @ret int

EXEC @ret = prcAuthorStatus '172-32-1176'

SELECT @ret

/* Call PROCEDURE WithIN a PROCEDURE */

CREATE PROCEDURE prcCallAuthors(@au_id varchar(11))

AS

BEGIN

      Declare @afname varchar(20)

      Declare @alname varchar(40)

      Declare @ret INt

     

      EXEC @ret = prcAuthorStatus @au_id

      IF @ret = 0

      BEGIN

            EXEC prcGetAuthorNames @au_id , @afname OUTput , @alname OUTput

            PRINT 'The Author First Name is     :     ' + @afname

            PRINT 'The Author LASt  Name is     :     ' + @alname

      END

      ELSE

      BEGIN

            PRINT ' No Records Found '

      END

END

/* To EXECute the Stored PROCEDURE */

EXEC prcCallAuthors '172-32-1176'

/* Stored PROCEDUREs with Default Values */

CREATE PROCEDURE prcDefValues(@p1 INt = null, @p2 INt = 10 , @p3 INt = 20)

AS

BEGIN

      SELECT @p1 , @p2 , @p3

END

/* To EXECute the PROCEDURE */

EXEC prcDefValues

EXEC prcDefValues 1,2,3

EXEC prcDefValues @p2 = 2 , @p1 = 1 , @p3 = 3

EXEC prcDefValues 10,Default,20

EXEC prcDefValues 10,null,default

/* To Drop a Strored PROCEDURE */

Drop PROCEDURE ProcName

Drop proc prcCallAuthors

Handling errors in SQL Server

/* @@ERROR */

-- Displays System defined with Error number severity and Description

SELECT * from master..sysmessages

-- Get the data from authors TABLE

USE pubs

SELECT * from authors

GO

--Reports information about a database object

sp_help authors

/* trapping the error */

-- Violate the format implemented by the check constraint

DECLARE @errint int

UPDATE authors

SET Zip = '123'

WHERE au_id = '213-46-8915'

SELECT @errint = @@ERROR

IF @errint = 547

      PRINT 'This is clear viloation of Check Constraint'

GO

-- CREATE a TABLE TestError

CREATE TABLE TestError

(

      ecode char(4) not null,

      ename varchar(20)

)

--INSERT null VALUES INTO the not null column and check for the @@ERROR value in the PRINT

-- statement

INSERT INTO TestError VALUES(null,'Shyam')

IF @@ERROR <> 0

      PRINT ' Error inserting INTO TABLE TestError ' + CONVERT(char(5),@@ERROR)

ELSE

      PRINT ' VALUES inserted INTO TABLE TestError ' + CONVERT(char(5),@@ERROR)

SELECT @@ERROR

GO

Notes

-   <> (Not Equal To)

Compares two expressions (a comparison operator). When you compare nonnull expressions, the result is TRUE if the left operand is not equal to the right operand; otherwise, the result is FALSE. If either or both operands are NULL.

-- It is always better to store the error value in a variable and then check it

DECLARE @errint int

INSERT INTO TestError VALUES(null,'Shyam')

SELECT @errint = @@ERROR

IF @errint <> 0

BEGIN

      PRINT '           Error INSERTing INTO TABLE TestError '

      PRINT '           The value of @@ERROR is ' + CONVERT(char(5),@@ERROR)

      PRINT '           The value of @errint is  ' + CONVERT(char(5),@errint)

END

ELSE

BEGIN

      PRINT ' VALUES INSERTed INTO TABLE TestError ' + CONVERT(char(5),@@ERROR)

END

GO

ITShare Professional PHP Track All

حمل من هنا

How to prevent Paste image into Richtextbox

Salam Alikm

Sometimes you need to use Richtextbox to take the benefits of  text formatting which can not in Textbox but user can paste image using Ctrl + V and you want to accept only text So here's the solution to accept text only in Richtextbox

private void richTextBox1_PreviewKeyDown(object sender, PreviewKeyDownEventArgs keyEventArgs)

{

if (Clipboard.ContainsImage() && keyEventArgs.KeyCode == Keys.V&& (Keys.Control) != 0)

Clipboard.Clear();

} 



Flow Control - Part 2 - While in SQL Server

/* While Loop */

-- 1 : Intializer.

DECLARE @index int

SET @index =1

-- 2 : While with Condition.

while @index<=10

Begin

-- Your Command Here.

print 'Printed Number index is :'+ Convert(char(2),@index)

set @index=@index+1 -- Incrementer.

End

/* Using BREAK and CONTINUE */

-- 1 : Intializer.

Declare @index int

set @index =0

-- 2 : While with Condition.

while @index<=10

begin

set @index=@index+1 -- Increment.

IF @index=3

BEGIN

continue

END

ELSE

BEGIN

-- Your Command Here.

print 'Printed Number index is :'+ Convert(char(2),@index)

END

end

Result :

Printed Number index is :1

Printed Number index is :2

Printed Number index is :4

Printed Number index is :5

Printed Number index is :6

Printed Number index is :7

Printed Number index is :8

Printed Number index is :9

Printed Number index is :10

Printed Number index is :11

-- Usage of While stataement with BREAK

-- 1 : Intializer.

Declare @index int

set @index =0

-- 2 : While with Condition.

while @index<=10

begin

set @index=@index+1 -- Increment.

IF @index=3

BEGIN

break

END

ELSE

BEGIN

-- Your Command Here.

print 'Printed Number index is :'+ Convert(char(2),@index)

END

end

GO

/* Using Pubs Database */

Use Pubs

-- SELECT the values FROM titles

SELECT * FROM titles

--SELECT values into the temptitles FROM titles

SELECT * into temptitles

FROM titles

GO

-- Check for the Values in the temptitles

SELECT * FROM temptitles

--Check for average of price FROM the temptitles table

SELECT AVG(price + 2) FROM temptitles

-- Increase the price by 2 till the average of price is less tnan 20

while (SELECT AVG(price + 2) FROM temptitles) < 20

BEGIN

UPDATE temptitles

SET price = price + 2

END

GO

Select* from temptitles

/* Check */

SELECT AVG(price) FROM temptitles

-- To write a query so as to increase the price by 2 till average of price is not more

-- than 20 and at the same time MAXimum price is not more than 40

-- Check for the MAXimum price

SELECT MAX(price) FROM temptitles

-- While statement

WHILE (SELECT AVG(price) FROM temptitles) < 20

BEGIN

UPDATE temptitles

SET price = price + 2

IF (SELECT MAX(price) FROM temptitles) > 40

BREAK

ELSE

IF (SELECT AVG(price) FROM temptitles) < 20

CONTINUE

END

GO

/* Check */

SELECT AVG(price) FROM temptitles

SELECT MAX(price) FROM temptitles

/* Goto */

--goto will branch to a user defined label

DECLARE @err int

SET @err = 0

IF @err = 0

BEGIN

PRINT 'Branching to ......error label'

goto error

END

--Errors handling

error:

BEGIN

PRINT 'In Error label Execution'

END

GO

/* Return */

use pubs

IF not exists (SELECT * FROM titles WHERE title_id = 'B001')

BEGIN

PRINT 'No such title'

Return

END

SELECT * FROM publishers

GO

/* Wait for */

/*

Blocks the execution of a batch, stored procedure,

or transaction until a specified time or time interval is reached,

or a specified statement modifies or returns at least one row.

*/

DECLARE @ctr int

SET @ctr = 1

while @ctr < 10

BEGIN

waitfor delay '000:00:01'

PRINT 'Delayed by 1 Second'

SET @ctr = @ctr + 1

END

SELECT * FROM titles