Temporary Stored Procedures in SQL Server 2008

Temporary Stored Procedures are same as the normal Stored Procedures. 
There are two kinds of temporary Stored Procedures, 
local (#)
Global.(##)
Temporary Stored Procedures are created just like any other SP but the name must be prefixed with a hash (#) for a local temporary SP and two hashes (##) for a global temporary Stored Procedure.


A local temporary Stored Procedure is only with in  the current session and is dropped when the session is closed 

CREATE PROCEDURE [dbo].[#TempProcedure] 
 @x INTEGER,
 @y INTEGER,
 @Z INTEGER OUTPUT
AS
 SET @Z=@x+@y
 SELECT @Z


To execute
declare @aaa integer
exec #TempProcedure 2,2,@aaa output

When a execute the same stored procedure in different session
then i get an error as follows(i,e in new query window)

Msg 2812, Level 16, State 62, Line 2
Could not find stored procedure '#TempProcedure'.


Global temporary Stored Procedure
A global temporary Stored Procedure is visible to all sessions and is dropped
when the session of the user that created it is closed.


CREATE PROCEDURE [dbo].[##TempProcedure]
 @x INTEGER,
 @y INTEGER,
 @Z INTEGER OUTPUT
AS
 SET @Z=@x+@y
 SELECT @Z


To execute
declare @aaa integer

exec ##TempProcedure 2,2,@aaa output

Where can we find this temporary stored procedures

COALESCE Function in Sql Server

COALESCE Function

COALESCE function returns the first Non NULL value

select * from employee

If the Employee has got the First Name, return first name

If the Employee doesn’t have a First Name and he has a Middle and a Last Name, then return Middle Name.

If the Employee doesn’t have First Name and Middle Name and he has only the Last Name, then return LastName

But if an Employee has both First Name and Middle Name, then return First Name.

Similarly, if an Employee has all of the Names –

 First, Middle and Last Names, then return FirstName

So the priority should first go to First Name, then to Middle Name and finally to Last Name.

Now, i want the output to be as

SELECT EmpID ,COALESCE(FirstName,MiddleName,LastName) AS Name,Designation,Salary

FROM employee

Custom Columns select using Crystal Report (Selected Columns to Be DISPLAYED IN CRYSTALREPORT AT RUN TIME)

First create a Data Set

Click on Add Button

It prompts for a message saying as

Click on Yes Button

ADD  YOUR COLUMNS  AS COLUMN1..........  COLUMNN

Add New Item

Select crystal report.rpt file and the below screen is visible

And Click on Ok button

And then press ctrl +alt +T

And click on  Ok BUTTON

In the Report Viewer Screen 

Press Ctrl+Alt +T 

In the Report 
Parameter Fields (Right Click and Select New)

Give the Name as col1

And Similarly Add 

Col2,col3,col4,

Similarly Add the Formula Fields as Below

And finally your database fields looks like 

Drag the controls 

.cs page

Add the following Namespaces

using CrystalDecisions.CrystalReports.Engine;

using CrystalDecisions.ReportSource;

using CrystalDecisions.Shared;

write the following code in button click

------------------------------------------------------------------------------------

private string GetData()

    {

        ReportDocument reportDocument;

        ParameterFields paramFields;

        ParameterField paramField;

        ParameterDiscreteValue paramDiscreteValue;

        reportDocument = new ReportDocument();

        paramFields = new ParameterFields();

        string query = "SELECT ";

        int columnNo = 0;

        if (ChkEmpID.Checked)

        {

            columnNo++;

            query = query.Insert(query.Length, "EmpID as Column" + columnNo.ToString());

            paramField = new ParameterField();

            paramField.Name = "col" + columnNo.ToString();

            paramDiscreteValue = new ParameterDiscreteValue();

            paramDiscreteValue.Value = "EmpID";

            paramField.CurrentValues.Add(paramDiscreteValue);

            //Add the paramField to paramFields

            paramFields.Add(paramField);

        }

        if (ChkEmpName.Checked)

        {

            columnNo++;

            if (query.Contains("Column"))

            {

                query = query.Insert(query.Length, ", ");

            }

            query = query.Insert(query.Length, "Ename as Column" + columnNo.ToString());

            paramField = new ParameterField();

            paramField.Name = "col" + columnNo.ToString();

            paramDiscreteValue = new ParameterDiscreteValue();

            paramDiscreteValue.Value = "Ename";

            paramField.CurrentValues.Add(paramDiscreteValue);

            //Add the paramField to paramFields

            paramFields.Add(paramField);

        }

        if (ChkDesignation.Checked)

        {

            columnNo++;

            if (query.Contains("Column"))

            {

                query = query.Insert(query.Length, ", ");

            }

            query = query.Insert(query.Length, "Designation as Column" + columnNo.ToString());

            paramField = new ParameterField();

            paramField.Name = "col" + columnNo.ToString();

            paramDiscreteValue = new ParameterDiscreteValue();

            paramDiscreteValue.Value = "Designation";

            paramField.CurrentValues.Add(paramDiscreteValue);

            //Add the paramField to paramFields

            paramFields.Add(paramField);

        }

        if (chksalary.Checked)

        {

            columnNo++;

            if (query.Contains("Column"))

            {

                query = query.Insert(query.Length, ", ");

            }

            query = query.Insert(query.Length, "Salary as Column" + columnNo.ToString());

            paramField = new ParameterField();

            paramField.Name = "col" + columnNo.ToString();

            paramDiscreteValue = new ParameterDiscreteValue();

            paramDiscreteValue.Value = "Salary";

            paramField.CurrentValues.Add(paramDiscreteValue);

            //Add the paramField to paramFields

            paramFields.Add(paramField);

        }

        CrystalReportViewer1.ParameterFieldInfo = paramFields;

        query += " FROM Employee";

        return query;

    }

 OUTPUT