Stored procedures
creation of sample stored procedure
creation of sample stored procedure with input parameter:
Execution of stored procedure with input parameter:
creation of sample stored procedure with input parameter and default value:
Execution without input value:
Execution with input value:
Stored procedure demonstrating we can modify the input parametet value inside the stored procedure:
Sample stored procedure with output parameter:
Execution of stored procedure with output parameter:
demonstrating Dynamic Query with example:
Execution:
Using ALTER TABLE to Modify the Code of a Stored Procedure:
Using ALTER TABLE to Modify the Options of a Stored Procedure:
To demonstrate encrypted source code:
Stored procedure Demonstrate RETURN Statement:
Case Study 1:Stored Procedures - Output Parameters & Return Values
case study 2:single stored procedure to search on whatever column you want
case study 3:THE DIFFERENCES BETWEEN MS SQL SERVER AND ORACLE
case study 4:the Difference Between EXEC and EXECUTE vs EXEC()–Use EXEC/EXECUTE for SP always
Part 2 Error massages:
Part 3:Questions and Answers:
Question 1:
Answer:
Question 2:(Need to check)
Answer:
Question 3:(Need to check)
Answer:
Stored procedures
create table #num(id int,name varchar(10));
insert into #num
select top 10000 id,name from sysobjects
creation of sample stored procedure
create proc #testproc
AS
select * from #num
go
exec #testproc
creation of sample stored procedure with input parameter:
select * from #num
create procedure #testproc
@id int
as
select name from #num
where id=@id
go
Execution of stored procedure with input parameter:
execute #testproc 1
or
Exec #testproc @id=1
Once created, information about stored procedures' parameters is stored in the syscolumns system table
Sysobjects stores general information,And syscomments stores the code of the stored procedure).
creation of sample stored procedure with input parameter and default value:
create procedure #testproc
@id int=1
as
select name from #num
where id=@id
go
Execution without input value:
exec #testproc
Execution with input value:
exec #testproc 2
if we specify the input parameter value then that value override the default value,
if you didn't specify input parameter value like (exec #testproc) then it will consider the default value as input parameter value.
Stored procedure demonstrating we can modify the input parametet value inside the stored procedure:
create procedure #testproc
@id int
as
set @id=2
select name from #num
where id=@id
go
exec #testproc 1
An input parameter is similar to a variable passed by value.Therefore, the stored procedure gets a
copy of the data and this doesn't affect the data outside the stored procedure.
In other words, if you pass a variable as a parameter of a stored procedure, and the value of this variable is modified inside
the stored procedure, this doesn't change the value of the variable outside the stored procedure.
Even the mode of the parameter is INPUT we are able to modify inside the stored procedure.(we should cross check this behaviour in oracle)
Sample stored procedure with output parameter:
create procedure #testproc
@id int ,
@name varchar(30) OUTPUT
as
select @name=name from #num
where id=@id
go
Execution of stored procedure with output parameter:
declare
@name varchar(30),
@id int
set @id=1
exec #testproc @id,@name output
select @name
go
or
declare
@name varchar(30)
exec #testproc 1,@name output
select @name
go
An output parameter is like a variable passed by reference. Hence, because the stored procedure
gets a pointer to a variable, any changes made to it are reflected outside the scope of the stored
procedure.
Using this type of parameter, a stored procedure can send values back to the calling
application.
To take advantage of output parameters, and to distinguish them from input parameters,
the OUTPUT keyword must be specified when creating the stored procedure, and also when it is
executed.
However, one of the limitations of using parameters is that you can't use a parameter to pass
the name of a database object (table, column, or stored procedure) to the stored procedure.
For this purpose, you must build the query at runtime, generating a dynamic query (using EXEC or
sp_executesql).
Notice that this is not a restriction of parameters; it is a restriction of the Data Definition
Language (DDL).
To illustrate this idea, imagine that you want to create a stored procedure with one parameter, and this
parameter is the table you want to query.
demonstrating Dynamic Query with example:
create procedure #testproc
@tabname varchar(30)
as
declare
@qry varchar(30)
set @qry='select * from '+ @tabname
exec(@qry)
go
Execution:
exec #testproc #num
Using ALTER TABLE to Modify the Code of a Stored Procedure:
In SQL Server 6.5 and earlier, the only way to change a stored procedure's definition was to
drop and re-create it, but this approach has one drawback:
Permissions and properties set on the stored procedure are lost.
Therefore, after re-creating the stored procedure, the database administrator had to set
permissions again.
below SP modifies the definition of the stored procedure created in above SP.
The new stored procedure, in addition to the table's name, receives a column's name as a parameter.
Alter procedure #testproc
@tabname varchar(30),
@colname varchar(20)
as
declare
@qry varchar(100)
set @qry='select '+@colname+' from '+@tabname
exec(@qry)
go
exec #testproc #num,id
When you alter a stored procedure's definition (using the ALTER PROC statement):
SQL Server keeps permissions intact on the stored procedure. As a result, any permissions set on the
stored procedure are kept after changing the stored procedure's code using ALTER TABLE.
This doesn't affect any dependent objects (tables, triggers, or stored procedures). For example, if you
alter a stored procedure's definition and it references a table, the table isn't affected.
This doesn't affect the property to run automatically when SQL Server starts, if this was previously set
using the sp_procoption system stored procedure.
For example, if you alter the code of the stored procedure created in Listing 8.6 (insertsqlstatus, which was set to run automatically whenever
SQL Server is started), SQL Server keeps this property intact.
In other words, if you either want to change the procedure's code without affecting permissions and properties,
or want to change the options of the stored procedure (WITH ENCRYPTION or WITH RECOMPILE), you can
use the ALTER PROCEDURE statement. However, notice that if you just need to change an option, you still
must specify the entire code of the stored procedure. Similarly, if you just have to change the code and
preserve the options,you also must specify the options.
For example, if you want to encrypt the above mentioned code,you would have to add the WITH
ENCRYPTION option to the definition of the stor
creation of sample stored procedure
creation of sample stored procedure with input parameter:
Execution of stored procedure with input parameter:
creation of sample stored procedure with input parameter and default value:
Execution without input value:
Execution with input value:
Stored procedure demonstrating we can modify the input parametet value inside the stored procedure:
Sample stored procedure with output parameter:
Execution of stored procedure with output parameter:
demonstrating Dynamic Query with example:
Execution:
Using ALTER TABLE to Modify the Code of a Stored Procedure:
Using ALTER TABLE to Modify the Options of a Stored Procedure:
To demonstrate encrypted source code:
Stored procedure Demonstrate RETURN Statement:
Case Study 1:Stored Procedures - Output Parameters & Return Values
case study 2:single stored procedure to search on whatever column you want
case study 3:THE DIFFERENCES BETWEEN MS SQL SERVER AND ORACLE
case study 4:the Difference Between EXEC and EXECUTE vs EXEC()–Use EXEC/EXECUTE for SP always
Part 2 Error massages:
Part 3:Questions and Answers:
Question 1:
Answer:
Question 2:(Need to check)
Answer:
Question 3:(Need to check)
Answer:
Stored procedures
create table #num(id int,name varchar(10));
insert into #num
select top 10000 id,name from sysobjects
creation of sample stored procedure
create proc #testproc
AS
select * from #num
go
exec #testproc
creation of sample stored procedure with input parameter:
select * from #num
create procedure #testproc
@id int
as
select name from #num
where id=@id
go
Execution of stored procedure with input parameter:
execute #testproc 1
or
Exec #testproc @id=1
Once created, information about stored procedures' parameters is stored in the syscolumns system table
Sysobjects stores general information,And syscomments stores the code of the stored procedure).
creation of sample stored procedure with input parameter and default value:
create procedure #testproc
@id int=1
as
select name from #num
where id=@id
go
Execution without input value:
exec #testproc
Execution with input value:
exec #testproc 2
if we specify the input parameter value then that value override the default value,
if you didn't specify input parameter value like (exec #testproc) then it will consider the default value as input parameter value.
Stored procedure demonstrating we can modify the input parametet value inside the stored procedure:
create procedure #testproc
@id int
as
set @id=2
select name from #num
where id=@id
go
exec #testproc 1
An input parameter is similar to a variable passed by value.Therefore, the stored procedure gets a
copy of the data and this doesn't affect the data outside the stored procedure.
In other words, if you pass a variable as a parameter of a stored procedure, and the value of this variable is modified inside
the stored procedure, this doesn't change the value of the variable outside the stored procedure.
Even the mode of the parameter is INPUT we are able to modify inside the stored procedure.(we should cross check this behaviour in oracle)
Sample stored procedure with output parameter:
create procedure #testproc
@id int ,
@name varchar(30) OUTPUT
as
select @name=name from #num
where id=@id
go
Execution of stored procedure with output parameter:
declare
@name varchar(30),
@id int
set @id=1
exec #testproc @id,@name output
select @name
go
or
declare
@name varchar(30)
exec #testproc 1,@name output
select @name
go
An output parameter is like a variable passed by reference. Hence, because the stored procedure
gets a pointer to a variable, any changes made to it are reflected outside the scope of the stored
procedure.
Using this type of parameter, a stored procedure can send values back to the calling
application.
To take advantage of output parameters, and to distinguish them from input parameters,
the OUTPUT keyword must be specified when creating the stored procedure, and also when it is
executed.
However, one of the limitations of using parameters is that you can't use a parameter to pass
the name of a database object (table, column, or stored procedure) to the stored procedure.
For this purpose, you must build the query at runtime, generating a dynamic query (using EXEC or
sp_executesql).
Notice that this is not a restriction of parameters; it is a restriction of the Data Definition
Language (DDL).
To illustrate this idea, imagine that you want to create a stored procedure with one parameter, and this
parameter is the table you want to query.
demonstrating Dynamic Query with example:
create procedure #testproc
@tabname varchar(30)
as
declare
@qry varchar(30)
set @qry='select * from '+ @tabname
exec(@qry)
go
Execution:
exec #testproc #num
Using ALTER TABLE to Modify the Code of a Stored Procedure:
In SQL Server 6.5 and earlier, the only way to change a stored procedure's definition was to
drop and re-create it, but this approach has one drawback:
Permissions and properties set on the stored procedure are lost.
Therefore, after re-creating the stored procedure, the database administrator had to set
permissions again.
below SP modifies the definition of the stored procedure created in above SP.
The new stored procedure, in addition to the table's name, receives a column's name as a parameter.
Alter procedure #testproc
@tabname varchar(30),
@colname varchar(20)
as
declare
@qry varchar(100)
set @qry='select '+@colname+' from '+@tabname
exec(@qry)
go
exec #testproc #num,id
When you alter a stored procedure's definition (using the ALTER PROC statement):
SQL Server keeps permissions intact on the stored procedure. As a result, any permissions set on the
stored procedure are kept after changing the stored procedure's code using ALTER TABLE.
This doesn't affect any dependent objects (tables, triggers, or stored procedures). For example, if you
alter a stored procedure's definition and it references a table, the table isn't affected.
This doesn't affect the property to run automatically when SQL Server starts, if this was previously set
using the sp_procoption system stored procedure.
For example, if you alter the code of the stored procedure created in Listing 8.6 (insertsqlstatus, which was set to run automatically whenever
SQL Server is started), SQL Server keeps this property intact.
In other words, if you either want to change the procedure's code without affecting permissions and properties,
or want to change the options of the stored procedure (WITH ENCRYPTION or WITH RECOMPILE), you can
use the ALTER PROCEDURE statement. However, notice that if you just need to change an option, you still
must specify the entire code of the stored procedure. Similarly, if you just have to change the code and
preserve the options,you also must specify the options.
For example, if you want to encrypt the above mentioned code,you would have to add the WITH
ENCRYPTION option to the definition of the stor
No comments:
Post a Comment