This is a migrated thread and some comments may be shown as answers.

store procedure with parameters

10 Answers 433 Views

General Discussions

This is a migrated thread and some comments may be shown as answers.

10 Answers, 1 is accepted

answered on 07 Apr 2009, 01:48 PM

Below is a code snipit from T-SQL proc that takes a passed string, converts it to a GUID (uniqueidentifier) and uses in the select statement. On the SQL Server side this took the passed string parameter from the report and returned the record of interest.

CREATE PROCEDURE procRptPrf
   @PId varchar(50)

AS
BEGIN

   DECLARE @ParticipantId as uniqueidentifier
   SELECT @ParticipantId = cast(@Pid as uniqueidentifier)

    SELECT [ProgramName]
      ,[SiteName]
      ...
      ,[StageDrug]
FROM [Sbirt].[dbo].[vwRptPfr]
WHERE @ParticipantId = ParticipantId
END

answered on 09 Apr 2009, 03:16 PM

Hi,
It is very easy when you add DataSource programatycly

//constructor

public void MyReport()

{

InitializeComponents();
SqlConnection sqlConnection1 = new SqlConnection();

SqlCommand sqlSelectCommand1 = new SqlCommand();

SqlDataAdapter sqlDataAdapter1 = new SqlDataAdapter();

// define a connection string

sqlConnection1.ConnectionString = Properties.

Settings.Default.RRLevel1DBCS;

// define the command object properties

sqlSelectCommand1.CommandType =

CommandType.StoredProcedure;

sqlSelectCommand1.CommandText =

"SelectNewExceptionsForAssetRep";

sqlSelectCommand1.Parameters.Add(

new SqlParameter("@AssetGuid", gu));

sqlSelectCommand1.Connection = sqlConnection1;

sqlDataAdapter1.SelectCommand = sqlSelectCommand1;

this.DataSource = sqlDataAdapter1;

}