Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to create a procedure which takes multi value parameter -Urgent Help Needed Expand / Collapse
Author
Message
Posted Monday, October 1, 2012 12:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 9, 2012 9:57 PM
Points: 3, Visits: 5
Hi,

I need to create a procedure which takes 2 input parameters and using this stored procedure in crystal reports for creating reports.

Input Params:
p_name1 & p_name2.
p_name1 should take the values from a column in a table so that in crystal report it will show as dropdown where the user can select one value.


anyhelp in this is much appreciated.

Thanks
Post #1366329
Posted Monday, October 1, 2012 3:43 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 3:37 AM
Points: 4,013, Visits: 5,306
pradeep985 (10/1/2012)
Hi,

I need to create a procedure which takes 2 input parameters and using this stored procedure in crystal reports for creating reports.

Input Params:
p_name1 & p_name2.
p_name1 should take the values from a column in a table so that in crystal report it will show as dropdown where the user can select one value.


anyhelp in this is much appreciated.

Thanks

To populate the p_name1 drop list, you will create a data set (e.g. SELECT Code, Description FROM MyTable) and link this as available values for the p_name1 parameter.

The stored procedure could look like:
CREATE PROCEDURE MySchema.MyProcedure
@p_name1 DataType[/], @p_name2 [i]DataType[/]
AS
SELECT [i]ColumnList
FROM Table WHERE ColumnA = @p_name1 AND ColumnB = @p_name2
GO

and will be used in the main data set of the report.


____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1366436
Posted Monday, October 1, 2012 8:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 9, 2012 9:57 PM
Points: 3, Visits: 5
Thanks for the response!!

Yes, I have the query in the same format...But how can I link a dataset to the input parameter @p_name1 in my stored procedure.
Basically I was looking a format like

CREATE PROCEDURE MySchema.MyProcedure
@p_name1 Dataset ='select columnA from table', @p_name2 [i]DataType[/]
AS
SELECT [i]ColumnList FROM Table WHERE ColumnA = @p_name1 AND ColumnB = @p_name2
GO

@p_name1 should hold the result set of columA from a different table.

Thanks
Pradeep GS


Post #1366591
Posted Monday, October 1, 2012 8:27 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 3:37 AM
Points: 4,013, Visits: 5,306
there may be two ways of doing this:

1) using Table Valued Parameters

2) By passing the filter that will be used to determine the result set from TableA and perform an innser join using that, e.g.
CREATE PROCEDURE MyProcedure
@p_name1 DataType, @p_name2 DataType
FROM TableB
INNER JOIN Table A ON TableA.Column = TableB.Column AND TableA.OtherColumn = @p_name1
WHERE TableB.AnotherColumn = @p_name2



____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1366598
Posted Monday, October 1, 2012 10:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 9, 2012 9:57 PM
Points: 3, Visits: 5
Hi SSCRAZY,

I am very new to SQL Stored procedure.I did not understand the second wasy of doing using filters.
I did not understand where we are inserting the dataset to input paramter p_name1.
Can you please provide me a sample procedure for using filters.

Also, regarding the first approach using table-value.I have created a type but do we need to create a temporary table also for that to insert the values in the table? for every execution of stored procedure, this table will created and deleted after executing ?


Post #1366643
Posted Wednesday, October 3, 2012 1:41 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 3:37 AM
Points: 4,013, Visits: 5,306
I am very new to SQL Stored procedure.I did not understand the second wasy of doing using filters.
I did not understand where we are inserting the dataset to input paramter p_name1.
Can you please provide me a sample procedure for using filters.

As an example, let us assume we have two tables, viz. TableA and TableB.
TableA has the more relevant data in that is required in your report.
TableB has the data you wish to place into a parameter to be passed from the report to SQL Server.
Some sample data would be, e.g.
CREATE TABLE TableB (
TableBRefNo INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_TableB PRIMARY KEY NONCLUSTERED,
UserCode CHAR(5) NOT NULL,
Description VARCHAR(36) NOT NULL,
Category CHAR(4) NOT NULL)
GO
INSERT TableB (UserCode, Description, Category)
VALUES ('Code1','First Code','ARAC'),('Code2','Second Code','BDIF'),('Code3','Third Code','ARAC')
GO
CREATE TABLE TAbleA (
TableBRefNo INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_TableA PRIMARY KEY NONCLUSTERED,
UserCode CHAR(5) NOT NULL,
Description VARCHAR(36) NOT NULL,
Spec NVARCHAR(36) NOT NULL,
TableBRefNo INT NOT NULL CONSTRAINT FK_TableA_TableB FOREIGN KEY REFERENCES TableB(TableBRefNo))
GO
INSERT TableA (UserCode, Description, Spec, TableBRefNo)
VALUES ('SPIDS','Special Code 1','AA1.BB2',1),('SCATS','Spacial Category','AB2.BB1',2),('SPIDA','Special Code 2','ABA.BBC',1),('STABA','Spacial Distortion 1','55.2.1',3)

Now, from this, we can create a stored procedure
CREATE PROCEDURE dbo.ReportResults
@Param1 CHAR(4), @Param2 VARCHAR(36)
AS
SELECT TableA.UserRefCode, TableA.Description, TableA.Spec, TableB.Description
FROM TableA
JOIN TableB ON TableB.TableBRefNo = TableA.TableBRefNo AND TableB.Category = @Param1
WHERE TableA.Description LIKE '%'+@Param2+'%'
GO

Using this, the @Param1 would be a single value, e.g. "ARAC", which, when passed to the stored procedure, filters TableB Accordingly and joint to the primary table (TableA) to return the desired result set to the report.


Also, regarding the first approach using table-value.I have created a type but do we need to create a temporary table also for that to insert the values in the table? for every execution of stored procedure, this table will created and deleted after executing ?

When a TVP is used to pass a data set to a stored procedure, this variable can be used as a table in iteslf and joined to the primary table (tableA in my example above) to return the desired results
e.g.
CREATE PROCEDURE dbo.ReportResults
@Param1 AS MyTVP, @Param2 VARCHAR(36)
AS
SELECT TableA.UserRefCode, TableA.Description, TableA.Spec, TableB.Description
FROM TableA
JOIN @Param1 P1 ON P1.TableBRefNo = TableA.TableBRefNo WHERE TableA.Description LIKE '%'+@Param2+'%'
GO

Hope this helps a little


____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1367442
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse