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

stored procedure with optional parameters Expand / Collapse
Author
Message
Posted Monday, February 2, 2009 2:52 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 14, 2012 2:54 AM
Points: 162, Visits: 78
Hi Everybody,

I have a stored procedure with does multiple tasks like displaying the data in multiple grids, updating the records, adding the records etc. All these are done in a single procedure. I want to create a stored procedure with accepts optional parameters. The user may not send all the parameters if he is calling the stored procedure for data display. He will pass all the parameters when ever he wants to add records.

Thanks & Regards,
Naveen kumar


Who is wise? He that learns from everyone. Who is powerful? He that governs his passions. Who is rich? He that is content. Who is that? Nobody.:)
Post #647880
Posted Monday, February 2, 2009 4:58 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:57 AM
Points: 1,093, Visits: 1,222
Parameters in SProcs are not optional. You have to mentioend them while calling the SProc. You can instead mention 'DEFAULT' keyword while passing the parameters. Like:

EXEC dbo.sp_Sample Default, Default

Mahesh


MH-09-AM-8694
Post #647926
Posted Monday, February 2, 2009 5:21 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 4:26 AM
Points: 4,427, Visits: 4,173
Mahesh Bote (2/2/2009)
Parameters in SProcs are not optional. You have to mentioend them while calling the SProc. You can instead mention 'DEFAULT' keyword while passing the parameters. Like:

EXEC dbo.sp_Sample Default, Default

Mahesh

If you use default values for your parameters you don't have to mention them. A good example is sp_send_dbmail which has about 20 parameters, but most of the time I supply only 2 or 3.


Markus Bohse
Post #647934
Posted Monday, February 2, 2009 5:45 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 14, 2012 2:54 AM
Points: 162, Visits: 78
Hi,

I got the solution. create sp like

create PROCEDURE COMPLIANCE_TASK_DETAILS(@USR_ID INT, @OPERATION VARCHAR(10), @GET_ID INT = null,
@GET_TASK_NAME VARCHAR(300) = null, @GET_STATUS VARCHAR(100) = null,
@ASSIGNED_TO INT = null, @GET_COMMENTS VARCHAR(500) = null
)
AS
BEGIN
statements........
end


Who is wise? He that learns from everyone. Who is powerful? He that governs his passions. Who is rich? He that is content. Who is that? Nobody.:)
Post #647944
Posted Friday, April 9, 2010 8:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 5, 2014 8:35 AM
Points: 2, Visits: 116
Dynamic sql will allow you do exactly what you are wanting.
Post #900551
Posted Monday, April 12, 2010 3:40 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, August 29, 2014 11:34 PM
Points: 790, Visits: 646

CREATE PROC usp_procdetails
@Param1 INT = 1
@Param2 VARCHAR(100) = 'Default'--This can work by adding value also.

AS

BEGIN
--Add your code depends upon default.
END

GO

EXEC usp_procdetails

GO



Regards,
Mitesh OSwal
+918698619998
Post #901436
Posted Tuesday, April 13, 2010 6:21 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, September 26, 2014 6:02 AM
Points: 919, Visits: 1,434
mtaylor7210 63514 (4/9/2010)
Dynamic sql will allow you do exactly what you are wanting.


... but also opens you up to SQL Injection attacks.



Post #902328
Posted Tuesday, April 13, 2010 6:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 5, 2014 8:35 AM
Points: 2, Visits: 116
True but anytime you are taking parameters you open yourself up to injection.
Post #902332
Posted Tuesday, April 13, 2010 6:41 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, September 26, 2014 6:02 AM
Points: 919, Visits: 1,434
The security vulnerability footprint is much higher when using dynamic sql over a properly written parametrized stored procedure.

I apologize to the original poster for getting off the "thread" topic.



Post #902339
Posted Wednesday, April 14, 2010 4:19 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 3:34 AM
Points: 1,174, Visits: 2,654
mtaylor7210 63514 (4/13/2010)
True but anytime you are taking parameters you open yourself up to injection.


No, I believe you are mistaken.

Please explain why you think using parameterized stored procedures opens you up to SQL injection?

Unless, of course, you use those parameters to create dynamic SQL.


--
Nigel
Useful Links:
How to post data/code on a forum to get the best help
The "Numbers" or "Tally" Table - Jeff Moden

Post #902978
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse