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

Variable Parameter list for for procedure Expand / Collapse
Author
Message
Posted Tuesday, November 6, 2012 4:46 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, April 27, 2014 7:55 PM
Points: 218, Visits: 556
Dear All

I need to create procedure with variable parmaeter list. I am trying to achive it through table value parameter.

Is it the correct approch?

Also I need this parameter to be optional. But when i give folloing syntax i get error
CREATE PROCEDURE [P1]
@param1 VARCHAR(40),
@ParameterTable ParameterTable READONLY = NULL
as
.....

Post #1381492
Posted Tuesday, November 6, 2012 5:01 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 1, 2013 10:17 AM
Points: 323, Visits: 984
You can use default Stored Procedure Template from Template Explorer.
it will help you



SSMS > View > Template Explorer > Stored Procedure


-----------------------------------------------------------------------------
संकेत कोकणे
Post #1381502
Posted Tuesday, November 6, 2012 6:03 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:50 AM
Points: 2,856, Visits: 5,124
shilpaprele (11/6/2012)
Dear All

I need to create procedure with variable parmaeter list. I am trying to achive it through table value parameter.

Is it the correct approch?

Also I need this parameter to be optional. But when i give folloing syntax i get error
CREATE PROCEDURE [P1]
@param1 VARCHAR(40),
@ParameterTable ParameterTable READONLY = NULL
as
.....




Table-valued parameter is the best for passing table-like data, but not variable parameter list.
Let say you want to pass multiple Customer Id's together with relevant Customer Names. Table-valued parameter is upto this task.
But if you want to pass list of different parameters eg. not-related to each other and having different datatype, I would recommend using XML.


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1381527
Posted Tuesday, November 6, 2012 6:52 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, April 27, 2014 7:55 PM
Points: 218, Visits: 556
I checked default template does not cater to my changing parameter reuirement. Also it is not showing me how to make table type paramter as optional
Post #1381548
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse