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”