SQL Server programmable objects: stored procedures Tips, Tricks and Traps

1.       How to view dependency info on a stored procedure

sp_depends sp_creatediagram

 

2.       How to modify a stored procedure

Alter Procedure <<stored procedure name>>

 

3.       How to find out info about a stored procedure

Sp_help <<stored procedure name>>

 

4.       How to execute a stored procedure

EXEC <<stored procedure name>>

 

5.       How to declare a variable

a.       Regular variable: DECLARE @name  varchar(120)

b.      OUTPUT variable:  see line 3 in Listing 0003

Listing 0001

1.         create proc usp_wdTestWithVariableNOutputVar

2.         (

3.         @stateCode varchar(2),

4.         @Name varchar(50) OUTPUT

5.         )

6.         as

7.         BEGIN

8.         SET NOCOUNT ON

9.         SELECT * FROM States WHERE StateCode =@STATECODE

10.        SELECT @Name='This is explicily set'

11.        END

12.                     RETURN 0

c.       T-SQL variable;

Listing 0002

1.  create proc usp_wdTestWithTSQLvariables

2.  (

3.  @stateCode varchar(2)

4.  )

5.  as

6.  --SET NOCOUNT ON

7.  BEGIN

 

8.  DECLARE @UserMsg VARCHAR(1000)

 

9.  SET @UserMsg='This is relatively pain free query'

 

10.PRINT @UserMsg

11.SELECT *

12.FROM States

WHERE StateCode=@stateCode

 

END

RETURN 0

6.       How to assign a value to a variable

a.       Use the SELECT statement: SELECT @myvariable=’hello’

b.      USE the SET statement:  SET @name=’hello’

 

7.       How to execute a stored procedure?

a.       With no variables:  EXECUTE <<stored procedure name>>

 

b.      With input variables ONLY: Using the stored procedure in Listing 0002, the following command will pass NY to the stored procedure: EXEC usp_wdTestWithTSQLvariables 'NY'

 

 

c.       With an OUTPUT variable (see Listing 0003)

Listing 0003

1.  CREATE PROCEDURE usp_wdTestWIThOutputvariables

2.  (

3.  @FirstName VARCHAR(48) = 'Anonymous',

4.  @TableName varchar(256) OUTPUT

5.  )

6.  AS

7.   

8.  SET NOCOUNT ON

9.  --Step 1 Create local temp table

10. 

11.create table #User

12.(

13.      rOWid INT NOT NULL IDENTITY ,

14.      FirstName Varchar(48),

15.      LastName Varchar(96) NOT NULL

16.)

17. 

18. 

19.-- Step 2- load sample data

20. 

21.DECLARE @NewID INTEGER

22.INSERT INTO #User(FirstName,LastName)

23.VALUES (@FirstName,'Doe')

24.SELECT @Newid=@@IDENTITY

25. 

26. 

27.RETURN @NewId

28. 

d.      Running a query In the immediate window for the query in Listing 0003

Listing 0004

Declare @rv integer

Declare @tbl varchar(255)

Execute @rv=usp_wdTestWIThOutputvariables 'John',@tbl output

print 'The tble name is ' + @tbl

 

e.      Saving the Return value

Listing 0005

1.  Declare @rv integer

2.  Declare @tbl varchar(255)

3.  Execute @rv=usp_wdTestWIThOutputvariables 'John',@tbl output

4.  -print 'The tble name is ' + @tbl

5.  PRINT 'The new id is ' + CAST(@rv as varchar)

 

8.       How to suppress sending info messages back to the client.

SET NOCOUNT ON

 

9.       How to write a single comment

USE the 2 dashes: --. For example,  in Listing 0002, There is a comment  in line 6

 

10.   How to write a multi-line comment?

USE /* to begin a comment block and */to end the comment black

 

11.   After finishing write a stored procedure that complies correctly, what Is the recommended next step?

Assign permissions to the new created objected by using the command:

GRANT EXECUTE ON <<stored procedure name>> TO public