SQL Server Stored Proc – Optional Parameter values

CREATE PROCEDURE pr_Orders_SelectMultiWCustomerEmployeeShipper
	@sCustomerID nchar(5),
	@iEmployeeID int,
	@iShipperID int
AS
SELECT 	*
FROM	Orders
WHERE	CustomerID = COALESCE(@sCustomerID, CustomerID)
	AND
	EmployeeID = COALESCE(@iEmployeeID, EmployeeID)
	AND
	ShipVia = COALESCE(@iShipperID, ShipVia)

Share data between stored procedures

There are many ways to share data between stored procs in sql server 2008. I would like to give simple example using temp tables –

When you want to share data between a single pair of a calling stored procedure (caller) and a called stored procedure (callee), using a local temp table is probably the most appealing choice, as you do not have to create a permanent table. But the more procedures you throw into the mix, the more likely that you will face a maintenance problem that makes this method less preferable.

for more information click here

CREATE TABLE #tmp (id int,formid uniqueidentifier, formname nvarchar(1000))

INSERT #tmp (id,formid,formname)
   EXEC [dbo].[Forms_Select_By_Age]
		@p_ProjectID = @ProjectID,
		@p_Age = @Age,
		@p_ParentOrChild =@ParentOrChild,
		@p_Phase =@Phase

select @TotalForms = count(id) from #tmp

SQL Server – Get Table or Column Info

select * from sys.tables;
select * from sys.columns;

select name from sys.columns where object_id = (select object_id from sys.tables where name = 'Customers'')</div>
and name not in ('customerid')