InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 03:13:24 GMT20http://www.sqlservercentral.com/Forums/Topic1366329-391-1.aspx[quote]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.[/quote]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.[code="sql"]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)GOINSERT TableB (UserCode, Description, Category)VALUES ('Code1','First Code','ARAC'),('Code2','Second Code','BDIF'),('Code3','Third Code','ARAC')GOCREATE 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))GOINSERT 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)[/code]Now, from this, we can create a stored procedure[code="sql"]CREATE PROCEDURE dbo.ReportResults@Param1 CHAR(4), @Param2 VARCHAR(36)ASSELECT TableA.UserRefCode, TableA.Description, TableA.Spec, TableB.DescriptionFROM TableAJOIN TableB ON TableB.TableBRefNo = TableA.TableBRefNo AND TableB.Category = @Param1WHERE TableA.Description LIKE '%'+@Param2+'%'GO[/code]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.[quote]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 ?[/quote]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 resultse.g.[code="sql"]CREATE PROCEDURE dbo.ReportResults@Param1 AS MyTVP, @Param2 VARCHAR(36)ASSELECT TableA.UserRefCode, TableA.Description, TableA.Spec, TableB.DescriptionFROM TableAJOIN @Param1 P1 ON P1.TableBRefNo = TableA.TableBRefNo WHERE TableA.Description LIKE '%'+@Param2+'%'GO[/code]Hope this helps a littleWed, 03 Oct 2012 01:41:30 GMTStewart "Arturius" Campbellhttp://www.sqlservercentral.com/Forums/Topic1366329-391-1.aspxHi 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 ?Mon, 01 Oct 2012 10:04:50 GMTpradeep985http://www.sqlservercentral.com/Forums/Topic1366329-391-1.aspxthere may be two ways of doing this:1) using [url=http://msdn.microsoft.com/en-us/library/bb510489(v=sql.105).aspx]Table Valued Parameters[/url]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.[code="sql"]CREATE PROCEDURE MyProcedure@p_name1 [i] DataType[/i], @p_name2 [i]DataType[/i]FROM TableBINNER JOIN Table A ON TableA.Column = TableB.Column AND TableA.OtherColumn = @p_name1WHERE TableB.AnotherColumn = @p_name2[/code]Mon, 01 Oct 2012 08:27:34 GMTStewart "Arturius" Campbellhttp://www.sqlservercentral.com/Forums/Topic1366329-391-1.aspxThanks 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 [b]Dataset[/b] ='select columnA from table', @p_name2 [i]DataType[/]ASSELECT [i]ColumnList FROM Table WHERE ColumnA = @p_name1 AND ColumnB = @p_name2GO@p_name1 should hold the result set of columA from a different table.ThanksPradeep GSMon, 01 Oct 2012 08:15:12 GMTpradeep985http://www.sqlservercentral.com/Forums/Topic1366329-391-1.aspx[quote][b]pradeep985 (10/1/2012)[/b][hr]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[/quote]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:[code="sql"]CREATE PROCEDURE MySchema.MyProcedure@p_name1 [i]DataType[/], @p_name2 [i]DataType[/]ASSELECT [i]ColumnList[/i] FROM Table WHERE ColumnA = @p_name1 AND ColumnB = @p_name2GO[/code]and will be used in the main data set of the report.Mon, 01 Oct 2012 03:43:49 GMTStewart "Arturius" Campbellhttp://www.sqlservercentral.com/Forums/Topic1366329-391-1.aspxHi,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.ThanksMon, 01 Oct 2012 00:42:48 GMTpradeep985