Input Parameters Scenario in SQL Stored Procedure (SQL Server 2005/2008)

To increase the flexibility of stored procedures and perform more complex processing, we can pass parameters to procedures. The parameters can be used anywhere that local variables can be used within the procedure code.

For all the examples shared below I have used Pubs database. You can download its msi file from here and then attach .mdf file in your SQL Sever 2008.
http://www.microsoft.com/downloads/en/details.aspx?FamilyId=06616212-0356-46A0-8DA2-EEBC53A68034&displaylang=en

Below is a basic example of a stored procedure excepting inout parameters:

USE [pubs]
GO

/****** Object: StoredProcedure [dbo].[reptq3] Script Date: 04/29/2011 01:20:52 ******/
SET ANSI_NULLS ON
GO

if exists (select * from sys.procedures where schema_id = schema_id('dbo')
and name=N'reptq3')
DROP procedure reptq3
SET QUOTED_IDENTIFIER ON
GO

create PROCEDURE reptq3
@lolimit money,
@hilimit money,
@type char(12)
AS
select
case when grouping(pub_id) = 1 then 'ALL' else pub_id end as pub_id,
case when grouping(type) = 1 then 'ALL' else type end as type,
count(title_id) as cnt
from titles
where price >@lolimit AND price <@hilimit AND type = @type OR type LIKE '%cook%'
group by pub_id, type with rollup
GO

Execute it:

USE [pubs]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[reptq3]
@lolimit = 2,
@hilimit = 9,
@type = N'business'
SELECT 'Return Value' = @return_value
GO

Below is result

Pub_id type cnt
-----------------------------
0736 business 1
0736 ALL 1
0877 mod_cook 2
0877 trad_cook 3
0877 ALL 5
ALL ALL 6

Return Value
------------------------------
0

A. How to set default values for parameters:

We can assign a default value to a parameter by specifying a value in the definition of the parameter. So let us define default values for the above stored procedure "Author_Titles". For that we need to modify it.

USE [pubs]
GO
/****** Object: StoredProcedure [dbo].[Author_Titles] Script Date: 04/29/2011 22:11:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Author_Titles] @state char(5)='%'
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN
SELECT a.au_lname, a.au_fname, t.title
FROM titles t
INNER JOIN titleauthor ta ON t.title_id = ta.title_id
RIGHT OUTER JOIN authors a ON ta.au_id = a.au_id
WHERE a.state like @state
RETURN 0
END
END

We can also make use of sp_help to know what are all the parameters that are requried for a Stored procedure.



B. How to pass Object names as parameter:

In SQL Server 2008, if you pass an object name as a parameter to a stored procedure, SQL Server attempts to treat it as a table-valued parameter unless the object name is used either as an argument in a WHERE clause or in a dynamic SQL query.

See the stored procedure shown below; it causes an error:

CREATE proc find_data @table varchar(128)
as
select * from @table
GO

Msg 1087, Level 16, State 1, Procedure find_data, Line 3
Must declare the table variable "@table".

As you can see, when the parameter is used in the FROM clause, SQL Server expects it to be defined as a table variable. To use the value in the parameter as a table name, you can build a dynamic SQL query like below;

USE [pubs]
GO
/****** Object: StoredProcedure [dbo].[find_data] Script Date: 04/30/2011 19:34:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create proc [dbo].[find_data] @table varchar(128)
as
exec ('select * from ' + @table)



C. How to use wild card in Stored procedure.

Wildcards can be included in varchar-based input parameters and used in a LIKE clause in a query to perform pattern matching. However, you should not use the char data type for parameters that will contain wildcard characters because SQL Server pads spaces onto the value passed in to the parameter to expand it to the specified size of the char data type. For example, if you declared an @lastname parameter as char (40) and passed in 'S%', SQL Server would search not for a string starting with 'S' but for a string starting with 'S', any characters, and ending with up to 38 spaces. This would likely not match any actual data values.

Let us create a stored procedure that accepts wild cards; say %:

if exists (select * from sys.procedures where schema_id= schema_id('dbo')
and name =N'sp_wildcard')
drop procedure sp_wildcard
go
create proc sp_widlcard @lastname varchar(40)='%'
as
select au_id, au_lname, au_fname
from authors
where au_lname like @lastname
order by au_lname, au_fname

Point to remember here:

1. If passed no parameter, returns data for all authors in the authors table

2. If passed a string containing wildcard characters, this procedure returns data for all authors matching the search pattern specified.

3. If a string containing no wildcards is passed, the query performs a search for exact matches against the string value.
   
   Take a look below
   
   exec sp_widlcard @lastname = 'S%'
   
   au_id au_lname au_fname
   ---------------------------------
   341-22-1782 Smith Meander
   274-80-9391 Straight Dean
   724-08-9931 Stringer Dirk

D. How to use table valued parameter (SQL Seever 2008).

In previous versions of SQL Server, it was not possible to share the contents of table variables between stored procedures. SQL Server 2008 changes that with the introduction of table-valued parameters, which allow you to pass table variables to stored procedures.

Points to remember:

1. Table-valued parameters are not permitted as the target of a SELECT INTO or INSERT EXEC statement.

2. table-valued parameters can be passed only as READONLY input parameters to stored procedures

3. DML operations, such as UPDATE, INSERT, and DELETE, cannot be performed on table-valued parameters within the body of a stored procedure.

4. To create and use table-valued parameters, you must first create a user-defined table type and define the table structure. You do so using the CREATE TYPE command.

Let us create the first table structure using a create type command.

if exists (select * from sys.systypes t where t.name='ytdsales_tabletype')
drop type ytdsales_tabletype

create type ytdsales_tabletype as table
(
title_id char(6),
title varchar(50),
pubdate date,
ytd_sales int)
go

After the table data type is created, we can use it for declaring local table variables and for Stored procedure parameters. To use the table-valued parameter in a procedure, we create a procedure to receive and access data through a table-valued parameter:

if OBJECT_ID('ab_parm_test') is not null
drop proc ab_parm_test
go

create proc ab_parm_test
@sales_minimum int =0,
@pubdate datetime=null,
@ytd_sales_tab ytdsales_tabletype READONLY
as
set nocount on
if @pubdate is null
set @pubdate = dateadd(month,-12,GETDATE())

select * from @ytd_sales_tab
where pubdate > @pubdate
and ytd_sales >=@sales_minimum
return
go

Then, when calling that stored procedure, we declare a local table variable using the table data type defined previously, populate the table variable with data, and then pass the table Variable to the stored procedure:

Now that we we have some data in our table type, we will execute our stored procedure.

declare @ytd_sales_tab ytdsales_tabletype
insert @ytd_sales_tab
select title_id, convert(varchar(50), title), pubdate, ytd_sales
from titles

exec ab_parm_test '6/1/2001', 10000, @ytd_sales_tab

Thanks for reading.

Cheers.